关于 MySQL Gap Lock 和 Next-Key Lock 的一个问题

[jiangcheng97]

MySql 的隔离级别是可重复读

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=InnoDB;

insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);

sessionA	sessionB
begin;
select * from t where c >=15 and c<=20 order by c desc for update;
	insert into t (6,6,6);

为什么 sessionB 会阻塞? 为什么在索引 c 上向左遍历，要扫描到 c=10 才停下来，next-key lock 会加到(5,10]？ 求解答

[PythonYXY]

c 是非唯一索引，MySQL 会向左遍历到第一个不满足条件的值，然后在这个值上加 next-key lock

[jiangcheng97]

@PythonYXY 如果按照这个逻辑，向右遍历，到 25 停下来，应该加(20,25]和(25,+00)的间隙锁吧，但是并没有加(25,+00)的间隙锁。还是我理解上有问题呢...

[RedisMasterNode]

复现成功插个眼蹲一手答案，另外几个测试 case：
1. insert into t VALUES (6,5,6); -- 阻塞
2. insert into t VALUES (6,4,6); -- 执行

不靠谱猜测：
1. 阻塞肯定是因为锁定区域有重叠；
2. 既然重叠那肯定是猜测 session A 锁定了 [10, 15] 这部分，session B 锁定了 [5, 10] 的这部分（边界是开区间闭区间暂且不进行确认，但是必然是有重叠区域的，例如这里的猜测 [10]）。

其他的提示信息：
1. Explain 结果显示 session A 的查询使用了 Backward index scan ，提示这里对 idx_c 的使用是反向的，因此 15 的 Next-Key 是 10 （可能）没错。

蹲一手答案。

[PythonYXY]

@jiangcheng97 25 对应的间隙锁就是(20,25)，(25,+00)这个间隙锁对应的是 supremum 。加间隙锁要看对应的哪个 key 。

[mercurius]

https://s2.loli.net/2023/02/20/nj27OqbSkJmsVAi.png
应该就跟 3 楼说的一样，因为是 Backward index scan 所以找到第一个不满足条件的不是 25 ，而是 10 （个人猜测因为是倒序的，所以这里的间隙锁应该为 (10,5] ，前开后闭区间），把排序去掉后间隙锁就是 (20,25] 了
Backward index scan 是 MySQL8.0 后才出现的，可以用 5.7 版本试试会不会一样的结果

[NeroKamin]

8.0 版本的 MySQL 有 Backward index scan ，所以 c 上加锁的情况应该是(20,25)、(15,20]、(10,15]、(5,10]，主键锁住记录 15 和 20

[jiangcheng97]

锁住(10.15],(15,20],(20.25]我可以理解，我其实不太理解的是为什么 desc 排序会锁住(5,10]这个区间；
而在正常的 asc 排序中，也只比 desc 少了(5,10]这个区间

[wueryi]

chatgpt 说：

[wueryi]

chatgpt 说：Session B 阻塞是因为 Session A 在执行 select 时设置了 FOR UPDATE ，这意味着它会在表中加一个 next-key lock ，否则会导致读取到已经被修改的数据。

向左遍历时，这个 next-key lock 会加到 (5,10] 上，是因为在主键 id 上，next-key lock 是一个包含前一行和本行的范围，而且根据索引 c ，可以看出 c=10 是最后一行满足条件的数据，因此会在 c=10 的位置停止遍历。

[lazyfighter]

我认为 sessionB 不会阻塞

[initObject]

在索引遍历的过程上进行加锁
索引搜索指的是就是:
在索引树上利用树搜索快速定位找到第一个值
然后向左或向右遍历
order by desc 就是用最大的值来找第一个
order by asc 就是用最小的值来找第一个

因为 order by id desc 所以首先在普通索引找到 c=20 的第一条记录
在 c=20 加上 next-key 锁 (15,20]
因为是普通索引 引擎认为可能存在不止一条的 c=20 的记录 因此向右遍历找到第一条不符合条件的记录 c=25 加上间隙锁（ 20,25 ）
然后 开始在索引上向左遍历扫描 扫描过程中 记录 c=15 符合条件 加上 next-key 锁 (10,15]
可能存在不止一条 c=15 的记录 继续向左扫描 得到记录 c=10 （第一个不符合条件 c>=15 停止遍历） 加上 next-key 锁 （ 5,10]

因为没有使用覆盖索引 在 c=15,c=20 对应行记录加上 主键的 行锁

[initObject]

正常的 asc 排序中 用最小的值来找第一个
所以首先找到 c=15 的第一条记录
在 c=15 加上 next-key 锁 （ 10,15]
接着向右遍历 找到 c=20 的记录 满足条件 c<=20 在 c=20 加上 next-key 锁（ 15,20]
因为是普通索引 引擎认为可能存在不止一条的 c=20 的记录 因此接着向右遍历 找到 c=25 的记录 第一个不满足条件 停止遍历 在 c=25 加上 next-key 锁（ 20,25] 因为优化规则 优化为间隙锁 （ 20,25 ）

加锁的顺序其实就是索引的遍历顺序 遍历到的记录或者区间都要加锁

[RedisMasterNode]

@lazyfighter 3F 我已经回复过了会阻塞.....

[jiangcheng97]

@initObject 感谢回答，理解了

[UN2758]

@initObject ‘（ 20,25] 因为优化规则 优化为间隙锁 （ 20,25 ）‘，我测试了一下，25 是闭区间啊

[initObject]

@UN2758 感谢纠正 非唯一索引范围查询 不会优化为间隙锁

[UN2758]

@initObject #16 降序查询的时候，比如 where c<=20 的情况下，25 确实又是开区间，我真的好蛋疼

[initObject]

@UN2758 找第一个值用的是等值查询 接着范围查询 访问到第一个不满足的记录为止
普通索引等值查询 优化规则 2 向右找到第一个不符合条件的值 退化为间隙锁
降序查询 等值查询找第一个值为 20 接着向右找到第一个不符合条件（不等于 20 ）的值 25 退化为间隙锁 （ 20,25 ）接着范围查询（从 20 往左直到 10 ）对于访问到的记录加上 next-key 锁
升序查询 等值查询找第一个值为 15 接着向右找到第一个不符合条件（不等于 15 ）的值 20 但是 20 是符合条件<=20 的 所以不会退化为间隙锁（ 15,20]（也可以理解为先退化为间隙锁， 在之后的范围查询（从 15 往右直到 25 ）中 加上了 next-key 锁）

等值查询之后就是范围查询 普通索引范围查询都加的是 next-key 锁 不会退化为间隙锁

[initObject]

@UN2758
select * from t where c >=15 and c<=20 order by c desc for update;
加锁过程：
1.等值查询找第一个值 条件为等于 20 找到记录 20 加上 next-key 锁 （ 15,20]
2.向右找到第一个不满足条件的记录 25 加上 next-key 锁 然后退化为间隙锁 (20,25)
3.接着从第一步找的记录 20 开始向左范围查询（有待考证 有大神可以查查源码看下这一步是从第二步得到的记录 25 还是第一步得到的记录 20 开始） 条件为 c >=15 and c<=20 直到第一个不满足条件的记录 10 访问到的数据都加 next-key 锁 依次在 15 ，10 ，加上 next-key 锁
综上就是 (5,10] (10,15] (15,20] (20,25)

select * from t where c >=15 and c<=20 order by c asc for update;
加锁过程：
1.等值查询找到第一个条件为等于 15 找到记录 15 加上 next-key 锁 (10,15]
2.向右找到第一个不满足条件的记录 20 加上 next-key 锁 然后退化为间隙锁 (15,20)
3.接着从第一步找的记录 15 开始向右范围查询 条件为 c >=15 and c<=20 直到第一个不满足条件的记录 25 访问到的数据都加 next-key 锁 依次在 20 ，25 加上 next-key 锁
综上就是 (10,15] (15,20] (20,25]