Can two transactions lock rows in the same table in parallel?

Question

I am trying to grasp the concept of MySQL locks and came across this section in the docs: https://dev.mysql.com/doc/refman/8.0/en/innodb-locks-set.html:

A locking read, an UPDATE, or a DELETE generally set record locks on every index record that is scanned in the processing of the SQL statement. It does not matter whether there are WHERE conditions in the statement that would exclude the row. InnoDB does not remember the exact WHERE condition, but only knows which index ranges were scanned.

That leads me to the conclusion that two transactions can never lock rows in the same table in parallel. Let me explain it on the following example:

Assume I have the following table:

CREATE TABLE t1 (id INT, PRIMARY KEY (id)) ENGINE = InnoDB;

Further, lets assume I have 7 rows inserted. This is the balanced tree of the primary key:

Now assume we lock row with id 3:

SELECT * FROM t1 WHERE id=3 FOR UPDATE

From the above paragrph

A locking read [..] set record locks on every index record that is scanned in the processing of the SQL statement.

I would guess that there is an exclusive lock on row 3,5,10. Is that right?

If that would be the case, then

SELECT * FROM t1 WHERE id=100 FOR UPDATE

would try to set an exclusive lock on row 10, because it scanned in the process to find 100. But that means, it has to wait for the other transaction to be finished.

This implies, any transaction that locks a row in table t1 will always first lock row with id 10. Thus, its not possible that two transactions lock two different rows in parallel.

My questions are:

• Is that actually true?
• How can I actually see the balanced index tree, so I can do some local testing?

Answer 1

Your example is forgetting one important piece of information, which is the same row of data can be indexed more than once, by different criteria. If a second index exists with a B-Tree, and say for example the index was sorted on id descending, then you'd be able to concurrently access the row with value 100 (which would be your root node of this second index).

Now assuming by row you didn't mean table row, rather you meant a specific row location within a single index. Then yes your example above is true, when only talking about within the same index.

Note this answer is a bit high level just to explain the concepts in the context of your question, and I can't say exactly what else MySQL specifically could be doing under the hood.

Answer 2

Essentially, because of the default transaction isolation level of the InnodDB engine, no.

InnoDB offers all four transaction isolation levels described by the SQL:1992 standard: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE. The default isolation level for InnoDB is REPEATABLE READ.

^{Reference: 15.7.2.1 Transaction Isolation Levels (Dev MySQL 8.0)}

I set up the following environment:

Create Database, Add Table, Insert Data

use Q286159;
CREATE TABLE t1 (id INT, PRIMARY KEY (id)) ENGINE = InnoDB;
insert into t1 (id) values (3),(5),(7),(10),(100),(50),(110);

Reproducing

I then went ahead and selected the data in one tab and updated the exact same row with the same id in another tab.

Ensure you have Auto-Commit Transactions turned off

First Tab Code

set autocommit = 0;
use Q286159;
start transaction;
BEGIN;
select * from t1 where id = 10 for update;

Second Tab Code

update t1 set id = 9 where id = 10;

Result

09:32:16  update t1ni set id = 9 where id = 10    1 row(s) affected Rows matched: 1  Changed: 1  Warnings: 0  0.000 sec

Ok, that isn't what you were expecting, but because of the default transaciton isoltion level to be expected.

Let's have a look at the Q & A which explains a bit what is going on under the hood.

How can I make a select statement get blocked?

Comment to accepted answer:

I tried to get a blocking read by using BEGIN; SELECT ... FOR UPDATE in Process 1 and a SELECT * for the same table in Process 2. It doesn't seem to block, and instead returns immediately with the data that existed before the transaction began. Is that correct?

Response to that comment:

@you786 Yes, that would be correct because the default transaction isolation level for InnoDB is REPEATABLE READ, no matter how many SELECT * queries run with a live SELECT ...FOR UPDATE

Answering your question(s)

I would guess that there is an exclusive lock on row 3,5,10. Is that right? (Is that actually true?)

No, because of the above explanation and the information supplied in the MySQL documentation:

For a unique index with a unique search condition, InnoDB locks only the index record found, not the gap before it.

Even when I selected the id that would be locked for a direct update of the same id (10), the record was only locked for a fraction of a second. The update was successful. Further selects would be possible.

You would actually have to switch the isolation level to read-committed and then try to update the rows to produce some form of locking.

Session 1

set autocommit = off;
set session transaction isolation level read committed;
start transaction;
select * from t1 where id = 10 for update;

Session 2

set autocommit = off;
set session transaction isolation level read committed;
start transaction;
update t1 set id = 9 where id = 10;

Back to Session 1

update t1 set id = 10 where id = 9;

Doesn't update because the data (record) is locked by the update in Session 2 which hasn't committed. The select ... for update doesn't have any effect on session 2's update....

Only after an explicit commit; in Session 2 will the update in Session 1 be successful.

How can I actually see the balanced index tree, so I can do some local testing?

If you have a look at this answer, then there is no (reasonable) way of viewing the actual b-tree index:

How to view the B-tree index structure generated by InnoDB of MySQL? (Stack Overflow)

There are no official tools to view the internals of InnoDB B-tree index structures.

There's an experimental set of tools to examine the internals of InnoDB pages, but it won't display the B-tree in a human-readable way. https://github.com/jeremycole/innodb_ruby