【Mysql】next-key 锁范围

背景

Mysql RR场景下通过next-key 锁解决了幻读的问题，而幻读通常是由 insert 新增的数据导致。所以next-key锁最终通过锁机制防止了一定条件下的新增数据从而解决了幻读问题。

规律

next-key锁可以由以下几条规律总结出锁范围

• next-key会对查询过程中访问到的对象进行加锁
• next-key锁通常是左开右闭的
• 在唯一索引上做等值查询时，next-key锁会退化成行锁
• 在做等值查询时，查询最后访问到的一个对象所加的next-key上不包含右节点，也就是变成了两边都是开区间
• 在唯一索引上做范围查询时，会访问到下一个不满足条件的对象上（Mysql 5.7版本）

实例

表数据结构
id 列是主键，c列上有普通索引，表内数据如下

id	c	d
0	0	0
5	5	5
10	10	10
15	15	15
20	20	20
25	25	25

等值查询间隙锁

第一个 update 语句会先找 id = 7 的数据，所以在 主键索引上的 B+树上做查询时，会先扫描 id = 7 的这一行，但是表内没有这一条数据，所以找到了比他大的第一条数据，也就是 id = 10 这一行。
再根据next-key加锁规律进行加锁，先加一个左开右闭的锁，再因为做等值查询时，最后不满足条件的第一条数据变成左开右开区间，从而给 (5, 10) 这一个范围加上了锁。
最终导致 insert 8 插入失败，update 10 更新成功。

非唯一索引等值锁

第一个 select … … lock in share mode 会先在索引树上找到 c = 5, id = 5 这一行，因为不知道有其他的 c = 5，会继续向下找，找到 c = 10, id = 10 这一行并终止查询。
所以根据规律，会现在索引 c 上加 (0, 5] 和 (5, 10] 上加锁，并因为做等值查询，将范围改为 (0, 5] 和 (5, 10)。
在做 update id = 5 这一行数据时，因为 修改对象是 id = 5 不在 索引 c 上，所以修改成功。
而做 insert c = 7 时，因为在锁区间内，所以 插入失败。
在这里不对 主键 索引 id 上加锁，是因为 select id … lock in share mode 的特殊性，首先因为覆盖索引的特殊性， id 列本身就在 索引 c 的B+树叶子节点上，所以不涉及到回表，访问到的对象也只有 索引 c 上的树节点。

如果将 lock in share mode 换成 for update。此时，update 就会被 block 住，因为 Mysql 会认为这将会造成一条更新，所以会将锁向 主键索引传递，为对应的主键索引数据行加锁。

主键索引范围锁

第一个 select 语言会扫描所有 id >= 10 并且 id < 11 的 主键索引数据行，所以会先找到 id = 10 这一行，并且接着向下扫描扫描到 id = 15 这一行时，不满足条件，结束查询。
所以根据规律会先对(5, 10] 和 (10,15] 上加锁，因为唯一索引加等值查询的原因，第一个next-key锁退化为行锁，最终变成[10,15] 这个范围。
从而导致 insert 8 成功，insert 13 失败，update 15 失败。

非唯一索引范围锁

第一个 select 语言会扫描所有 c >= 10 并且 c < 11 的 主键索引数据行，所以会先找到 c = 10 这一行，并且接着向下扫描扫描到 c = 15 这一行时，不满足条件，结束查询。
所以根据规律会先对(5, 10] 和 (10,15] 上加锁。

这里和上面不同，首先是因为 做 c>= 10 时，索引 c 不是唯一索引，所以不会退化为行锁
其次在做 c < 11 时，不是 等值查询，所以也不用做 左开右开的退化。

唯一索引范围锁 bug

这个查询会扫描所有 id > 10 并且 id <= 15 的 主键索引数据行，所以会先找到比10大的第一行 也就是 id = 15 这一行，并且接着向下扫描扫描到 id = 15 这一行时，正常会因为 唯一索引的原因，不会存在 第二个 id = 15 了，但是因为规律第五条的原因会继续向下扫描到 id = 20 这一行，结束查询。
所以最终加锁范围是 (10,15] 和 (15, 20] 这两个区间。
导致 update 20 修改失败， insert 16 插入失败。

非唯一索引上存在"等值"的例子

此时，数据表中多一条 (30, 10, 30)的数据，此时数据表中数据为

id	c	d
0	0	0
5	5	5
10	10	10
15	15	15
20	20	20
25	25	25
30	10	30

因为第一条 delete c = 10 ，会先扫描 索引 c 的 B+树上 c = 10 的数据行，接着因为 c 不是唯一索引，不知道 有没有其他的 c = 10 会继续向下扫描，知道扫描到 c = 15 时，不满足条件，结束查询。
此时，根据next - key 规律，会先加 (5, 10] 和 (10, 15] 锁，并因为等值查询的原因，第二段锁退化成左开右开，变成 (5, 10] 和 (10, 15) 锁区间。
最终导致 insert 12 插入失败，update 15 更新成功。

limit 语句加锁

此时，数据表中多一条 (30, 10, 30)的数据，此时数据表中数据为

id	c	d
0	0	0
5	5	5
10	10	10
15	15	15
20	20	20
25	25	25
30	10	30

因为第一条 delete c = 10 ，会先扫描 索引 c 的 B+树上 c = 10 的数据行，也就是 c = 10, id = 10 和 c = 10, id = 30 这两条，此时因为 limit 2 的原因，扫描到这两条之后满足条件就退出查询了，不会继续向下扫描 c = 15 这一行。

所以加锁范围就仅仅是 (5, 10] 这一个区间了。
最终导致 insert 12 插入成功。

一个死锁的例子

next-key 锁由间隙锁（左开右开） 和 行锁组成，所以在某些情况下会出现 间隙锁加锁成功，行锁加锁失败的场景。
在这段语句中，Session A 做 select lock in share mode时，会对(5,10] 加锁，并且因为 share mode的原因是共享锁。

在做update c = 10 最终结果失败的，但是由于间隙锁和行锁的原因，Session B 的间隙锁加锁成功，c = 10 的行锁加锁失败。

Session A 此时又要插入 8，这个 8 在 Session B 的间隙锁区间内，所以也加锁失败，开始等待。

这个时候Session A 在等待 Session B 释放锁，Session B 又在等待 Session A 释放锁，构成死锁条件。

参考

Mysql 实战45讲 20节
Mysql 实战45讲 21节