背景
《MySQL45讲》里提到间隙锁(gap lock)与next-key lock的区别,并提到了索引上发生等值查询时,向右遍历时且最后⼀个值不满⾜等值条件的时候, next-key lock退化为间隙锁。
另外也阐述了在不等号查询(范围查询)中会存在等值查询所引起的退化。
然而在实测中发现相关实验出现了相互矛盾的结果。
实验(MySQL v8.0.11)
表结构
# 创建表
create table `table21` (
`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 table21 values (0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);
由此可见这批初始化的数据将表分成了(-∞,0]、 (0,5]、 (5,10]、 (10,15]、 (15,20]、 (20, 25]、 (25, +supremum]
几个区间
无优化的情况
会话A | 会话B |
---|---|
begin; sleect * from table21 where id<12 for update; | |
update table21 set d=d+1 where id=15; (被阻塞) |
由修改id=15
被阻塞的现象可见,在(12,15]加了next-key lock,即既锁定(12,15)这个间隙,同时为id=15
添加行锁。
有优化的情况
会话A | 会话B |
---|---|
begin; sleect * from table21 where id<12 order by id desc for update; | |
update table21 set d=d+1 where id=15; (更新成功) |
由更新成功可见next-key lock(12,15]
退化成了间隙锁(12,15)
,即id=15
未被锁定。
按照原文的说法是找id=12
时发生了等值查询,向右遍历的下一个值是id=15
,但由于不符合等值条件,所以退化成间隙锁。
我也曾经用非唯一索引c
做同样的实验,结果也是一样有矛盾。
问题
- 如何才算是等值查询?
- 为什么加入(order by id desc)字段后就可以发生优化?
实验结论
通过实验结果尝试回答以上两个问题。
即使是“等值查询”也没有优化
为此补做实验:
会话A | 会话B |
---|---|
begin; sleect * from table21 where id<15 for update; | |
update table21 set d=d+1 where id=15; (阻塞) |
如果按原文的说法,执行引擎会先进行id=15
的等值查询,然后开始向左搜索,则id=15
的记录不应该被上行锁。然而经过测试,行锁还是被加到该记录上了,说明优化跟访问查询里是不是“等值查询”关系不大。
倒序查询带来的优化
对本文开始的两个重要语句用explain解释一次,发现使用order by XX desc
后,Extra字段中会出现backward index scan
,即发生从后向前的倒序查询。
为了验证倒序查询是否有优化,做了以下两个实验:
- 实验一
会话A | 会话B |
---|---|
begin; sleect * from table21 where id<15 order by id desc for update; | |
update table21 set d=d+1 where id=15; (成功) |
- 实验二:MySQL45讲里面提到有个bug,即唯⼀索引上的范围查询会访问到不满⾜条件的第⼀个值为⽌。比如当查询范围是(10,15]时,被锁的范围会扩展成(10,20]。这里尝试通过倒序查询来优化成(10,20)。
会话A | 会话B |
---|---|
begin; sleect * from table21 where id>10 and id<=15 order by id desc for update; | |
update table21 set d=d+1 where id=20; (成功) | |
insert into table21 values(16,16,16); (阻塞) |
另外也曾尝试不加desc,此时查询方向为正向,id=20的记录会被上行锁。由此说明倒序查询在范围查询中会去掉不必要的行锁。从而推测上面出现的现象不是由等值查询引起优化。不过(15,20)区间还是会被上间隙锁。
总结
通过这次实验,仍未能准确认识next-key lock退化成间隙锁的条件,这一点我觉得需要等以后翻源代码才能够解决。但是当使用倒序查询的话会对边界有一定优化。
另外,比起next-key lock会不会退化成间隙锁,我认为next-key lock会不会退化成行锁更重要,因为前者影响的是作为边界的一行会不会被上行锁,影响的是更新操作,而后者则锁住了一个区间,影响插入操作。