MySQL Forums
Forum List  »  InnoDB

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

Options: ReplyQuote


Subject
Views
Written By
Posted
About Next-Key Locks
316
April 14, 2021 06:30AM
127
April 14, 2021 06:33AM
134
April 14, 2021 01:42PM
104
April 15, 2021 07:49AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.