本实验基于MySQL8.0.18版本
在《MySQL45讲》定义了如下加锁规则:
原则 1:加锁的基本单位是 next-key lock。next-key lock 是前开后闭区间。
原则 2:查找过程中访问到的对象才会加锁。
优化 1:索引上的等值查询,给唯一索引加锁的时候,next-key lock 退化为行锁。
优化 2:索引上的等值查询,向右遍历时且最后一个值不满足等值条件的时候,next-key lock 退化为间隙锁。
一个 bug:唯一索引上的范围查询会访问到不满足条件的第一个值为止。
初始化表:
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);
例1:
select * from t where id>10 and id<=15 for update;
思路:先找到id>10的第一条记录,即(15,15,15),加上next-key (10,15], 这时发现15已经达到右边界值,由于id是唯一索引,不用再往后找了(上面提到的bug在该种情况下已经解决),最终加锁范围为(10,15]。
验证结果表明确实如此,根 id>10 and id<=15的字面意思相符
例2:
select * from t where id>=10 and id<15 for update;
思路:先找到id>=10的第一条记录,即(10,10,10),加上next-key (5,10], 在这种情况下类似于优化1,退化成行锁10。然后再接着搜索下一行(15,15,15),加上next-key(10,15],此时已不满足id<15的条件,所以最终的加锁范围为[10,15]
验证结果发现加锁范围为[10,15),即在15这一行上没加行锁,上面的规则已经出现了问题,说明最后一个next-key退化成了间隙锁。 在这种情况下加锁范围还是与id>=10 and id<15 字面意思相符
例3:
select * from t where id>10 and id<=15 order by id desc for update;
思路:先找到id<=15的第一条记录,即(15,15,15),加上next-key(10,15],这时如果按照优化1规则,则退化成行锁15,接着往左搜索下一行(10,10,10),并加上next-key(5,10],所以最终的加锁范围为(5,10]行锁15;
验证结果表明最终的加锁范围为(5,10],(10,15],(16,20),这说明了什么?
首先在倒序情况下优化1已经失效了,还是加的next-key(5,10],并且此时在已经搜索到id=15的情况下,还是继续搜到了下一行(20,20,20),说明那个bug在倒序的情况下依然存在。另外最后一个next-key也退化成了间隙锁。
例4:
select * from t where id>=10 and id<15 order by id desc for update;
验证结果表明最终的加锁范围为(0,5],(5,10],(10,15),这同样表明上面的bug还是存在的,在查到(10,10,10)后,还是会搜索下一行和上一行,但是15上依旧不会加行锁
例5:
select * from t where c>10 and c<=15 for update;
思路:首先找到c>10的第一条记录(15,15,15),加上next-key(10,15],由于c非唯一索引,还要继续往下找,搜到下一行(20,20,20),加上next-key(15,20],所以最终加锁结果为(10,15](15,20]
验证结果表明最终加锁范围为(10,15],(15,20),即在20上也没加行锁,并且发现此时加锁范围也与字面意思不同了
例6
select * from t where c>=10 and c<15 for update;
思路:首先找到c>=10的第一条记录(10,10,10),加上next-key(5,10](由于c不是唯一索引,这里不能退化成行锁),接着搜到下一行(15,15,15),加上next-key(10,15],最终加锁范围为(5,10],(10,15]
验证结果表明最终的加锁范围为(5,10],(10,15),在15这一行也没有加锁,跟例2出现了类似的问题。
例7
select * from t where c>10 and c<=15 order by c desc for update;
同例3,验证结果表明最终的加锁范围为(5,10],(10,15],(16,20)
例8
select * from t where c>=10 and c<15 order by c desc for update;
同例4,验证结果表明最终的加锁范围为(0,5],(5,10],(10,15)
综上所述,在范围查询时,在原来的基础上再添加以下原则:
- 在正序查询时,如果查询条件满足优化1,依旧会退化成行锁;但在倒序查询时,不会采用优化1;
- 在范围查询时,如果有不涉及的行,那么最后一个next-key会退化成间隙锁(这是一个比较好的优化,不会锁住表中已经存在的行,提高了并发度)
- 在正序查询时,bug已经修复,如果发现唯一索引满足等值条件,不会再往下找;倒序查询时,bug依旧存在,在唯一索引满足等值条件时,依旧会往前或往后搜索。