Next-Key Locks

Next-Key Locks

一個next-key lock 是 一個record lock 在index record 和 一個區間鎖 在一個區間在index record之前

InnoDB 執行 row-level locking  以這樣一種方式當它搜尋或者掃描 一個表的索引,


它設定共享或者排它鎖在index records.

是以, row-level locks 實際上是  index-record locks. 

一個next-key lock 在一個index record 也影響區間在那個index record 之前。


也就是說,一個next-key lock  是一個Index-record 加上一個區間鎖在index record 之前的區間。


如果一個會話有一個共享或者排它鎖在記錄R上在一個索引上,


另外的會話不能插入一個新的index record 在這個區間 

假設一個Index 包含值10,11,13,20.

可能的next-key locks 對于這個index包含了下面的時間間隔,

一個圓括号表示排除間隔端點

一個方括号表示包含間隔端點

(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)


在最後的區間, next-key lock locks 的區間在最大值的上界在index和上界的僞記錄

有一個值高于任何值在index裡。


上界限不是一個真正的index record.是以,實際上,


next-key lock  locks隻有區間在最大索引值後面的區間

預設情況下, InnoDB 工作在REPEATABLE READ 事務隔離級别下 

 innodb_locks_unsafe_for_binlog 系統變量被禁用,


在那種情況下,可以使用next-key locks來搜尋和索引掃描,來防止幻讀行










Session 1:

mysql> explain select * from t1 where id BETWEEN 5 and 7 for update;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------+
|  1 | SIMPLE      | t1    | range | t1_idx1       | t1_idx1 | 5       | NULL |    3 | Using index condition |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

mysql> select * from t1 where id BETWEEN 5 and 7 for update;
+-----+------+------+
| sn  | id   | info |
+-----+------+------+
| 239 |    5 | a5   |
| 240 |    6 | a6   |
| 241 |    7 | a7   |
+-----+------+------+
3 rows in set (0.00 sec)



Session 2:
mysql>  update t1 set id=300 where id=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> rollback;
Query OK, 0 rows affected (0.01 sec)

mysql>  update t1 set id=500 where id=5;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>  update t1 set id=600 where id=6;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>  update t1 set id=600 where id=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql>  update t1 set id=600 where id=7;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>  update t1 set id=800 where id=8;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>  update t1 set id=800 where id=9;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> rollback;
Query OK, 0 rows affected (0.01 sec)


會鎖住 5,6,7,8 4條記錄




繼續測試:

Session 1:
mysql> show index from t1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t1    |          0 | PRIMARY  |            1 | sn          | A         |          11 |     NULL | NULL   |      | BTREE      |         |               |
| t1    |          0 | t1_idx1  |            1 | id          | A         |          11 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

mysql> select * from t1 where id=7 for update;
+-----+------+------+
| sn  | id   | info |
+-----+------+------+
| 241 |    7 | a7   |
+-----+------+------+
1 row in set (0.00 sec)


Session 2:
mysql> update t1 set id=800 where id=8;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> rollback;
Query OK, 0 rows affected (0.01 sec)



去掉Id列的索引繼續測試:


Session 1:

mysql> show index from t1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t1    |          0 | PRIMARY  |            1 | sn          | A         |          11 |     NULL | NULL   |      | BTREE      |         |               |
| t1    |          0 | t1_idx1  |            1 | id          | A         |          11 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

mysql> alter table t1 drop index t1_idx1;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from t1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t1    |          0 | PRIMARY  |            1 | sn          | A         |          11 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

mysql> select * from t1 where id=7 for update;
+-----+------+------+
| sn  | id   | info |
+-----+------+------+
| 241 |    7 | a7   |
+-----+------+------+
1 row in set (0.00 sec)


Session 2:


Database changed
mysql> update t1 set id=800 where id=8; --HANG




/************************************************
mysql> explain select * from t1 where id=7 for update;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |   11 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)



mysql> show index from t1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t1    |          0 | PRIMARY  |            1 | sn          | A         |          11 |     NULL | NULL   |      | BTREE      |         |               |
| t1    |          1 | t1_idx1  |            1 | id          | A         |          11 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

mysql> explain select * from t1 where id=7 for update;
+----+-------------+-------+------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | t1    | ref  | t1_idx1       | t1_idx1 | 5       | const |    1 | NULL  |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)




下面來舉個手冊上的例子看什麼是next-key lock。假如一個索引的行有10,11,13,20 
那麼可能的next-key lock的包括： 
(無窮小, 10] 
(10,11] 
(11,13] 
(13,20] 
(20, 無窮大) (這裡無窮大為什麼不是閉合？你數學不到家~~) 



舉例測試:

mysql> desc t100;
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| sn    | int(11) | NO   | PRI | NULL    | auto_increment |
| id    | int(11) | YES  |     | NULL    |                |
+-------+---------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> show create table t100\G;
*************************** 1. row ***************************
       Table: t100
Create Table: CREATE TABLE `t100` (
  `sn` int(11) NOT NULL AUTO_INCREMENT,
  `id` int(11) DEFAULT NULL,
  PRIMARY KEY (`sn`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR: 
No query specified



Session 1:
mysql> mysql> show index from t100;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t100  |          0 | PRIMARY  |            1 | sn          | A         |          11 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

mysql> select * from t100;
+----+------+
| sn | id   |
+----+------+
|  1 |    7 |
|  2 |    9 |
|  3 |   10 |
|  4 |   12 |
|  5 |   13 |
|  6 |   14 |
|  7 |   15 |
|  8 |   22 |
|  9 |   23 |
| 10 |   24 |
| 11 |   25 |
+----+------+
11 rows in set (0.00 sec)

mysql> update t100 set id=100 where id=1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0



id列上沒有索引,導緻:

Session 2:


mysql> insert into t100(id) values(100);


t100表所有記錄鎖住




/***************

mysql> show index from t100;
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t100  |          0 | PRIMARY   |            1 | sn          | A         |          11 |     NULL | NULL   |      | BTREE      |         |               |
| t100  |          1 | t1oo_idx1 |            1 | id          | A         |          11 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

mysql> select * from t100;
+----+------+
| sn | id   |
+----+------+
|  1 |    7 |
|  2 |    9 |
|  3 |   10 |
|  4 |   12 |
|  5 |   13 |
|  6 |   14 |
|  7 |   15 |
|  8 |   22 |
|  9 |   23 |
| 10 |   24 |
| 11 |   25 |
+----+------+
11 rows in set (0.00 sec)

mysql> delete from t100 where id=21;
Query OK, 0 rows affected (0.00 sec)




Session 2:

mysql>  insert into t100(id) values (15);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>  insert into t100(id) values (16);
 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>  insert into t100(id) values (17);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> mysql>  insert into t100(id) values (18);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>  insert into t100(id) values (19);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>  insert into t100(id) values (20);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>  insert into t100(id) values (21);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction


鎖15-21 之間

行鎖加鎖對象永遠是索引記錄，因為innodb中表即索引
在（三）種，a=21也是不存在，但是在表裡面21前後都有記錄，是以這裡next-key lock的區間也就是(15,21]，是以不在這個區間内的都可以插入。 


記錄鎖---鎖單條記錄；區間鎖---鎖一個開區間；next-key 鎖---前面兩者的結合