Next-key lock explication - Primary key for range

Question

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

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.

Answer 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