About Next-Key Locks
Posted by:
five small
Date: April 14, 2021 06:30AM
I have learned something about Next-Key Locks in Official definition:
------------------------------------------------------------------------------
Suppose that an index contains the values 10, 11, 13, and 20. The possible next-key locks for this index cover the following intervals, where a round bracket denotes exclusion of the interval endpoint and a square bracket denotes inclusion of the endpoint:
(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)
------------------------------------------------------------------------------
So I did a test in my own database,my table test is following:
mysql> show create table test\G
*************************** 1. row ***************************
Table: test
Create Table: CREATE TABLE `test` (
`id` int NOT NULL,
`a` int DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `single_index` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
mysql> select * from test;
+----+------+
| id | a |
+----+------+
| 1 | 10 |
| 2 | 11 |
| 3 | 13 |
| 4 | 20 |
+----+------+
4 rows in set (0.00 sec)
mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ |
+-------------------------+
1 row in set (0.01 sec)
------------------------------------------------------------------------------
And my test steps following:
client A:
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test where a=17 for update;
Empty set (0.00 sec)
------------------------------------------------------------------------------
client B:
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test values(5,13);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into test values(5,14);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into test values(5,15);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into test values(5,16);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into test values(5,17);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into test values(5,18);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into test values(5,19);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into test values(5,20);
Query OK, 1 row affected (0.00 sec)
------------------------------------------------------------------------------
I find that I can't insert between 13 and 19,
but I can insert the 20 to "a" column,
That means Next-Key Locks should be [13,20),not (13,20],
So I want to know if I'm wrong about Next-Key Locks