2

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:

enter image description here

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?
3
  • You might be able to test parts of MySQL's transaction behavior by running explicit transactions in two different MySQL command-line clients. – Mike Sherrill 'Cat Recall' Feb 28 at 14:34
  • @MikeSherrill'CatRecall' yes I already use two MySQL cli's. But what I currently can't see is the balanced index tree. Without knowing its structure, I cant test my assuptions made in this post. – Adam Feb 28 at 16:40
  • I'm pretty sure they mean that only the leaf nodes are locked. That is only "3". – Rick James Mar 1 at 21:36
0

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.

5
  • Thank you for your answer. But even if you use a secondary key, I belive the primary index get looked as well, see 15.7.3: If a secondary index is used in a search and index record locks to be set are exclusive, InnoDB also retrieves the corresponding clustered index records and sets locks on them. the clustered index is the primary key – Adam Feb 28 at 16:37
  • @Adam It depends, even what you quoted is only in the case an exclusive lock is taken out. My answer was meant to be high level because it's going to be situational, but I also wanted to communicate it is possible to not lock the entire table at the row level, just because one index is locked, especially when another index exists on that table. – J.D. Feb 28 at 16:40
  • The converse is also possible as you read though too. So both outcomes are possible and valid, situation depending. – J.D. Feb 28 at 16:49
  • okay I got it. It is in general possible. But if you have only a primary key and you do 10.000 updates, then they all have to be done in sequence, you cant do it parallel, right? Looks crazy to me. – Adam Feb 28 at 16:57
  • 1
    @Adam I honestly don't know 100% but my understanding is with a scan operation, that's true. With a seek operation (even though you traverse the root node of the B-Tree) you don't necessarily exclusively lock every node that is traversed, and parallel UPDATES are possible. Whether that's true in MySQL, or always the case I can't say for sure under the hood, but in theory that's true. If I find a good resource on concurrent updates in MySQL I'll update my answer. – J.D. Feb 28 at 17:59
0

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

MySQL Workbench - Query Options - Auto-Commit Transaction 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

4
  • Okay that answers it. A locking read, an UPDATE, or a DELETE generally set record locks on every index record that is scanned in the processing this means in general they dont assume a primary key. And later what you quoted For a unique index with a unique search condition, InnoDB locks only the index record found, not the gap before it.. – Adam Mar 2 at 21:25
  • I read dba.stackexchange.com/q/41865 and it makes much sense to me, because its only a select statment. And SELECT ... FOR UPDATE prevents other transactions from locking it. However SELECT * FROM WHERE x=3 is not using any locks, so it works even if the row is locked. – Adam Mar 2 at 21:32
  • But I can't see that your example could possibly work. Because if transaction a is locking the row, you cannot lock it again. See dev.mysql.com/doc/refman/8.0/en/innodb-locking.html : A record lock is a lock on an index record. For example, SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE; prevents any other transaction from inserting, updating, or deleting rows where the value of t.c1 is 10. – Adam Mar 2 at 21:33
  • It's a timing issue. Once the SELECT has run, and the results have been returned to the client, why should the lock persist? The example does work. I ran it against a MySQL instance running on Ubuntu Server via a MySQL Workbench client. – John K. N. Mar 3 at 7:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.