使用next-key locks 用于搜尋和索引掃描,可以防止幻讀

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 用于搜尋和索引掃描,可以防止幻讀