Next-Key Locks
A next-key lock is a combination of a record lock on the index record and a gap lock on the gap before the index
record.
是一个记录锁在索引记录上和一个区间锁在区间的组合 在index record之前
InnoDB 执行行级锁以这种方式 当它搜索或者扫描一个表的索引,它设置共享或者排它锁在Index records。
因此, row-level locks 实际上是 index-record locks.
一个 next-key lock 在一个index record 也会影响 那个index record 记录前的gap
也就是说 一个next-key lock 是一个 index-record lock plus a gap lock
If one session has a shared or exclusive lock on record R in an index, another session cannot insert a new index
record in the gap immediately before R in the index order.
如果一个session 有一个共享或者排它锁在记录R上,另外的session 不能插入新的index record 在before R这个区间
Session 1:
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> update SmsTest set phoneNo=111 where phoneNo <10;
Query OK, 20 rows affected (0.01 sec)
Rows matched: 20 Changed: 20 Warnings: 0
mysql> explain update SmsTest set phoneNo=111 where phoneNo <10;
+----+-------------+---------+-------+---------------+--------------+---------+-------+------
+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
|
+----+-------------+---------+-------+---------------+--------------+---------+-------+------
+------------------------------+
| 1 | SIMPLE | SmsTest | range | SmsTest_idx1 | SmsTest_idx1 | 4 | const | 20 | Using where; Using
temporary |
+----+-------------+---------+-------+---------------+--------------+---------+-------+------
+------------------------------+
1 row in set (0.00 sec)
锁住1-9的记录
mysql> select * from SmsTest where phoneNo <10;
+-------+---------+-------------+--------+
| sn | phoneNo | channelType | status |
+-------+---------+-------------+--------+
| 1 | 1 | 2 | 1 |
| 45210 | 1 | 1 | 1 |
| 45211 | 1 | 1 | 1 |
| 2 | 2 | 2 | 1 |
| 201 | 2 | 1 | 1 |
| 45212 | 2 | 1 | 1 |
| 3 | 3 | 2 | 1 |
| 45209 | 3 | 1 | 1 |
| 45213 | 3 | 1 | 1 |
| 4 | 4 | 2 | 1 |
| 45214 | 4 | 1 | 1 |
| 5 | 5 | 2 | 1 |
| 45215 | 5 | 1 | 1 |
| 6 | 6 | 2 | 1 |
| 45216 | 6 | 1 | 1 |
| 7 | 7 | 2 | 1 |
| 45217 | 7 | 1 | 1 |
| 8 | 8 | 2 | 1 |
| 45218 | 8 | 1 | 1 |
| 9 | 9 | 2 | 1 |
+-------+---------+-------------+--------+
20 rows in set (0.00 sec)
Session 2:
mysql> insert into zjzc.SmsTest(PhoneNo,channelType,status) values(1,1,1); --hang
mysql> insert into zjzc.SmsTest(PhoneNo,channelType,status) values(2,1,1); --hang
mysql> insert into zjzc.SmsTest(PhoneNo,channelType,status) values(3,1,1); --hang
mysql> insert into zjzc.SmsTest(PhoneNo,channelType,status) values(4,1,1); --hang
mysql> insert into zjzc.SmsTest(PhoneNo,channelType,status) values(5,1,1); --hang
mysql> insert into zjzc.SmsTest(PhoneNo,channelType,status) values(6,1,1); --hang
mysql> insert into zjzc.SmsTest(PhoneNo,channelType,status) values(7,1,1); --hang
mysql> insert into zjzc.SmsTest(PhoneNo,channelType,status) values(8,1,1); --hang
mysql> insert into zjzc.SmsTest(PhoneNo,channelType,status) values(9,1,1); --hang
mysql> insert into zjzc.SmsTest(PhoneNo,channelType,status) values(10,1,1);
Query OK, 1 row affected (0.01 sec)
mysql>
mysql> insert into zjzc.SmsTest(PhoneNo,channelType,status) values(11,1,1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into zjzc.SmsTest(PhoneNo,channelType,status) values(12,1,1);
Query OK, 1 row affected (0.01 sec)
假设一个Index 包含值10，11,13和20，可能的next-key locks 对于这个索引覆盖下面的区间,
一个圆括号表示排除两端,一个方块号报表包含
(negative infinity, 10] 负无穷大
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)正无穷大
测试:
mysql> select * from SmsTest where phoneNo in (10,11,13,20);
+-------+---------+-------------+--------+
| sn | phoneNo | channelType | status |
+-------+---------+-------------+--------+
| 10 | 10 | 2 | 1 |
| 45239 | 10 | 1 | 1 |
| 45252 | 10 | 1 | 1 |
| 11 | 11 | 2 | 1 |
| 45253 | 11 | 1 | 1 |
| 13 | 13 | 2 | 1 |
| 20 | 20 | 2 | 1 |
+-------+---------+-------------+--------+
7 rows in set (0.00 sec)
mysql> update SmsTest set phoneNo=999 where phoneNo in (10,11,13,20);
Query OK, 7 rows affected (0.00 sec)
Rows matched: 7 Changed: 7 Warnings: 0
mysql> explain update SmsTest set phoneNo=999 where phoneNo in (10,11,13,20);
+----+-------------+---------+-------+---------------+--------------+---------+-------+------
+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
|
+----+-------------+---------+-------+---------------+--------------+---------+-------+------
+------------------------------+
| 1 | SIMPLE | SmsTest | range | SmsTest_idx1 | SmsTest_idx1 | 4 | const | 7 | Using where; Using
temporary |
+----+-------------+---------+-------+---------------+--------------+---------+-------+------
+------------------------------+
1 row in set (0.00 sec)
Session 2:
mysql> insert into zjzc.SmsTest(PhoneNo,channelType,status) values(1,1,1);
Query OK, 1 row affected (0.01 sec)
mysql> insert into zjzc.SmsTest(PhoneNo,channelType,status) values(2,1,1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into zjzc.SmsTest(PhoneNo,channelType,status) values(3,1,1);
Query OK, 1 row affected (0.05 sec)
mysql> insert into zjzc.SmsTest(PhoneNo,channelType,status) values(4,1,1);
Query OK, 1 row affected (0.01 sec)
mysql> insert into zjzc.SmsTest(PhoneNo,channelType,status) values(5,1,1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into zjzc.SmsTest(PhoneNo,channelType,status) values(6,1,1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into zjzc.SmsTest(PhoneNo,channelType,status) values(7,1,1);
Query OK, 1 row affected (0.01 sec)
mysql> insert into zjzc.SmsTest(PhoneNo,channelType,status) values(8,1,1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into zjzc.SmsTest(PhoneNo,channelType,status) values(9,1,1); --hang
mysql> insert into zjzc.SmsTest(PhoneNo,channelType,status) values(10,1,1);--hang
mysql> insert into zjzc.SmsTest(PhoneNo,channelType,status) values(11,1,1); --hang
mysql> insert into zjzc.SmsTest(PhoneNo,channelType,status) values(12,1,1); --hang
mysql> insert into zjzc.SmsTest(PhoneNo,channelType,status) values(13,1,1);--hang
mysql> insert into zjzc.SmsTest(PhoneNo,channelType,status) values(14,1,1);
Query OK, 1 row affected (0.01 sec)
mysql> insert into zjzc.SmsTest(PhoneNo,channelType,status) values(15,1,1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into zjzc.SmsTest(PhoneNo,channelType,status) values(16,1,1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into zjzc.SmsTest(PhoneNo,channelType,status) values(17,1,1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into zjzc.SmsTest(PhoneNo,channelType,status) values(18,1,1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into zjzc.SmsTest(PhoneNo,channelType,status) values(19,1,1);
mysql> insert into zjzc.SmsTest(PhoneNo,channelType,status) values(20,1,1); --hang
mysql> insert into zjzc.SmsTest(PhoneNo,channelType,status) values(21,1,1);
Query OK, 1 row affected (0.01 sec)
mysql> insert into zjzc.SmsTest(PhoneNo,channelType,status) values(22,1,1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into zjzc.SmsTest(PhoneNo,channelType,status) values(23,1,1);
Query1 row affected (0.01 sec)
默认情况下,InnoDB 操作在 REPEATABLE READ transaction isolation level 禁用innodb_locks_unsafe_for_binlog system
variable
在这种情况下,使用next-key locks 用于搜索和索引扫描,可以防止幻读
