1

I have the following query, and I wanted to use the diagram to CONFIRM IF I UNDERSTAND IT RIGHT:

SELECT * FROM table WHERE pk > 99;

"pk" is the primary key

enter image description here

I am having trouble understanding the next key lock, I found this diagram to know which gap lock will be applied and which "next key locks".

If the diagram is WRONG, let me know.

1 Answers1

1

The diagram look right.

I'll assume pk in your example is the primary key of an InnoDB table, and is therefore the clustered index.

The gap is locked starting one value greater than 97, and extending to infinity.

It seems strange, because the values 98 and 99 may seem like they should be free of locks, because the condition is on WHERE pk > 99, and therefore does not match the values 98 or 99. But the next-key lock locks the whole gap before the index record, down to but not including the preceding index record.

https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html#innodb-next-key-locks says:

A next-key lock on an index record also affects the “gap” before that index record. That is, a next-key lock is an index-record lock plus a gap lock on the gap preceding the index record.

Demo: In a first window, I start a transaction that acquires a next-key lock:

mysql> select * from mytable;
+-----+-------+
| pk  | name  |
+-----+-------+
|   3 | hello |
|  97 | hi    |
| 101 | hola  |
| 103 | yo    |
| 107 | hey   |
+-----+-------+

mysql> start transaction;

mysql> select * from mytable where pk > 99 for update;
+-----+------+
| pk  | name |
+-----+------+
| 101 | hola |
| 103 | yo   |
| 107 | hey  |
+-----+------+

Now the index record for pk 101 is locked, as well as the gap following pk 97.

In a second window I test this:

mysql> insert into mytable values (99, 'test');
(hangs)

mysql> insert into mytable values (98, 'test');
(hangs)

mysql> update mytable set name='test' where pk=97;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • hi @Bill Karwin "The gap is locked starting one value greater than 97, and extending to infinity." - So (as a summary), the "total of locked gaps " starts where the FIRST gap lock belonging to the first record lock (next-key lock) that takes the condition UNTIL INFINITY begins, ¿right? –  Dec 30 '22 at 23:05
  • `But the next-key lock locks the whole gap before the index record, down to but not including the preceding index record` - IT DOES NOT INCLUDE IT because the index record WILL HAVE a record lock applied instead of a gap lock, right? @Bill Karwin –  Dec 30 '22 at 23:12
  • 2
    It does not include the preceding index record (97 in this case) because your condition in your WHERE clause does not include that record. – Bill Karwin Dec 30 '22 at 23:49
  • 2
    I don't think I can explain any further. Please see my example above. – Bill Karwin Dec 30 '22 at 23:50
  • thanks @Bill Karwin one last question, in this case, is row the same as index record, I have asked a question right here, I would like to know your opinion https://stackoverflow.com/questions/74955129/innodb-locking-does-record-lock-use-indexes –  Dec 31 '22 at 00:49
  • 1
    No, an index record may be linked to multiple rows. Think of any index that is non-unique. The specific value may occur on multiple rows. Each index record has a list of rows where that value occurs. – Bill Karwin Dec 31 '22 at 03:11
  • Locks are tied to individual rows -- the row itself and/or the gap next to it. This simplifies the implementation by not having to invent fictitious rows (eg, 99). – Rick James Dec 31 '22 at 07:08