【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 这一行。