一、前言

Next-key锁是记录锁和间隙锁的组合，可以理解为一种特殊的间隙锁。通过临建锁可以解决幻读的问题。

每个数据行上的非唯一索引列上都会存在一把临键锁，当某个事务持有该数据行的临键锁时，会锁住一段左开右闭区间的数据。

当InnoDB 搜索或扫描索引时，InnoDB在它遇到的索引记录上所设置的锁就是next-key lock，它会锁定索引记录本身以及该索引记录前面的gap。

二、加锁原则

1. 加锁的基本单位是next-key lock。next-key lock是左开右闭区间。
2. 查找过程中访问到的对象才会加锁。
3. 索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为行锁。
4. 索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁。
5. 唯一索引上的范围查询会访问到不满足条件的第一个值为止。

三、slock in share mode 与 for update 的区别

• lock in share mode 加的是读锁(共享锁) , lock in share mode 只锁覆盖索引,也就说：lock in share mode 只锁非主键索引对应的B+树中的索引内容。
• for update 加的是写锁(排它锁) , for update 的加锁内容是非主键索引树上符合条件的索引项,以及这些索引项对应的主键索引树上相应的索引项。在两个索引上都加了锁。
• 共同点: 两者都属于当前读范围。

四、Next-Key Lock案例演示

1、数据准备

create table test_NK(
id int primary key,
num1 int,
num2 int,
key idx_num1(num1)
);

insert into test_NK values(5,5,5),(10,10,10),(20,20,20),(25,25,25);

2、在更新时,不仅对下面的五条数据加行锁, 还会对中间的取值范围增加6个临键锁, 该索引可能被锁住的范围如下

(-∞,5],(5,10],(10,15],(15,20],(20,25],(25,+∞)

测试1: 等值查询普通索引

-- 事务1,开启事务,查询 num1 = 5的记录, 并添加共享锁
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select id from test_NK where num1 = 5 lock in share mode;
+----+
| id |
+----+
| 5 |
+----+
1 row in set (0.01 sec)

-- 事务2,执行插入操作
Database changed
mysql> update test_NK set num2 = num2+1 where id = 5; -- 执行成功
Query OK, 1 row affected (0.01 sec)

mysql> insert into test_NK values(7,7,7); --阻塞
mysql> insert into test_NK values(4,4,4); --阻塞
mysql> insert into test_NK values(10,10,10); -- 主键已存在
ERROR 1062 (23000): Duplicate entry '10' for key 'PRIMARY'

分析上面部分SQL语句发生阻塞的原因

1. 初步加锁，加锁的范围是（0,5]，（5,10]的临键锁。
2. 根据原则4,由于查询是等值查询,并且最后一个值不满足查询要求,所next-key退化 为间隙锁,最终锁定的范围是(0,10)。
3. 因为加锁范围是(0,10) , 所以 insert id = 4和 id=7都阻塞了。
4. 因为num1是普通索引列, lock in share mode 因为覆盖索引,没有对主键索引加 锁,所以update操作正常执行。

测试2: 范围查询 唯一索引

-- 事务1 开启事务,执行一个条件是id的范围查询
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test_NK where id > 10 and id < 15 for update;
Empty set (0.00 sec)

-- 窗口2, 每一条SQL都是一个独立事务
-- 间隙范围: (-∞,5],(5,10],(10,15],(15,20],(20,25],(25,+∞)
mysql> insert into test_NK values(9,9,9); -- 正常
mysql> insert into test_NK values(12,12,12); -- 阻塞
mysql> insert into test_NK values(14,14,14); -- 阻塞
mysql> insert into test_NK values(16,16,16); -- 阻塞
mysql> insert into test_NK values(17,17,17); -- 阻塞
mysql> insert into test_NK values(18,18,18); -- 阻塞
mysql> insert into test_NK values(19,19,19); -- 阻塞
mysql> insert into test_NK values(20,20,20); --阻塞
mysql> insert into test_NK values(21,21,21); --成功
Query OK, 1 row affected (0.00 sec)

分析上面部分SQL语句发生阻塞的原因:

根据原则5 唯一索引上的范围查询,会访问到不满足条件的第一个值为止。所以锁定范围是(10,15],(15,20] ,也就是(10,20)。

测试3: 范围查询 普通索引

-- 事务1 开启事务,通过索引字段num1 进行范围查询,并加排它锁
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test_NK where num1 >= 10 and num1 < 15 for update;
+----+------+------+
| id | num1 | num2 |
+----+------+------+
| 10 | 10 | 10 |
+----+------+------+
  
-- 窗口2, 每一条SQL都是一个独立事务
-- 间隙范围: (-∞,5],(5,10],(10,15],(15,20],(20,25],(25,+∞)
mysql> insert into test_NK values(9,9,9);-- 阻塞
mysql> insert into test_NK values(13,13,13);-- 阻塞
mysql> insert into test_NK values(16,16,16);-- 阻塞
mysql> insert into test_NK values(19,19,19);-- 阻塞
mysql> insert into test_NK values(21,21,21); -- 正常
Query OK, 1 row affected (0.00 sec)

分析上面部分SQL语句发生阻塞的原因

InnoDB存储引擎会对普通索引的下一个键值加上gap lock.所以原本临键锁锁定是(5,10],(10,15]。而15下一个键值是(15,20],所以插入5~20之间的值的时候都会被锁定，要求等待。

下文将分析幻读是如何产生的，怎么根据临键锁进行处理的；