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: 0mysql> 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 transactionmysql>  update t1 set id=600 where id=6;ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionmysql>  update t1 set id=600 where id=4;Query OK, 1 row affected (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 0mysql> 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 transactionmysql>  update t1 set id=800 where id=8;ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionmysql>  update t1 set id=800 where id=9;Query OK, 1 row affected (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 0mysql> 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: 0mysql> 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: 0mysql> 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 changedmysql> 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: t100Create Table: CREATE TABLE `t100` (  `sn` int(11) NOT NULL AUTO_INCREMENT,  `id` int(11) DEFAULT NULL,  PRIMARY KEY (`sn`)) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)ERROR: No query specifiedSession 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: 0id列上没有索引,导致: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 transactionmysql>  insert into t100(id) values (16); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionmysql>  insert into t100(id) values (17);ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionmysql> mysql>  insert into t100(id) values (18);ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionmysql>  insert into t100(id) values (19);ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionmysql>  insert into t100(id) values (20);ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionmysql>  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 锁---前面两者的结合