Row Lock of mysql innodb (5)-- next-Key Lock

The gap lock next-key is locked for records where the range condition does not exist to avoid unrepeatable reading and phantom reading. If it is the isolation level of read committed, this problem does not exist.

So: in our development, if we use the isolation level of repeatable read, we should minimize updates to a certain range of records, otherwise it will seriously affect concurrent insertion.

Special case: applying for a lock of a record that does not exist through equal conditions will also use the next-key lock and affect the insertion of the record.

Root@sakila 10:46:49 > show variables like'% tx_isolation%'

+-+ +

| | Variable_name | Value |

+-+ +

| | tx_isolation | REPEATABLE-READ |

+-+ +

1 row in set (0.00 sec)

Session 1:

Root@sakila 10:49:04 > select * from tab_no_index where id=111 for update

Empty set (0.00 sec)

Session 2: because the first session lock record does not exist, the next-key is locked. There is no scope, which is equal to all, so the new record cannot be inserted.

Root@sakila 10:47:43 > insert into tab_no_index values (200Japanese 200')

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Root@sakila 10:49:17 > rollback

Query OK, 0 rows affected (0.01 sec)