Next-Key Lock浅析

这是我参与8月更文挑战的第4天，活动详情查看：8月更文挑战

本文是看到 ON DUPLICATE KEY UPDATE 引起的死锁问题 中的 log，看到 Next-key lock 的存在，从而引发了对 Next-key lock 的思考。

幻读

同一事务下，连续执行两次同样的SQL可能得到不同的结果，第二次的SQL可能返回了不存在的行

当前存在 id=99, id=102 ，执行以下 SQL：

select * from user where id > 100 for update;

此时插入 id=101 的记录，再次查询 → 出现 id=101 的记录。这就是 幻读

InnoDB采用 Next-Key Lock解决幻读问题

🌰

CREATE TABLE `test` ( 
	`id` int(11) primary key auto_increment, 
	`xid` int, 
	KEY `xid` (`xid`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

insert into test(xid) values (1), (3), (5), (8), (11);

开启第一个 session：

begin;

select * from test where id = 8 for update;

然后开启第二个：

begin;

insert into test(xid) values (1);
insert into test(xid) values (4);
insert into test(xid) values (5);
insert into test(xid) values (9);

背景介绍完了。开始看看执行流程：

Session 1

1. RR 隔离级别 → 解决 幻读 ⇒ gap lock + record lock
2. 查询的是全值匹配
3. 匹配 主键 Id

在一行行扫描的过程中，不仅将给行加上了行锁，还给行两边的空隙也加上了锁 ⇒ 这个也就是：Next-key Lock。

跟间隙锁存在冲突关系的，是“往这个间隙中插入一个记录”这个操作。间隙锁之间都不存在冲突关系。

Session 2

如果在 xid 的索引改为唯一索引：

// sessionA
begin;
insert into test(xid) values (20);

// sessionB
insert into test(xid) values (12);

在 sessionA 中：

1. insert 语句首先会对记录加插入意向锁
2. 插入意向锁和 之前的GAP 锁冲突
3. 锁会等待

如果没有之前 select for update:

1. 加插入意向锁
2. 插入数据
3. 给记录加 X record lock
4. 后面的 insert 是可以插入的

如果此时：insert into … on duplicate key update

insert into … on duplicate key update 这个语义的逻辑是，插入一行数据，如果碰到唯一键约束，就执行后面的更新语句。

他会在插入的记录这加 next-key lock，所以回会导致后续的插入出现死锁。