Mysql next-key lock加锁规则
About 4 min
Mysql next-key lock加锁规则
最近在读丁奇老师的《Mysql 实战 45 讲》，收获颇多，在此把一些以前不懂的知识点记录下来，方便以后复习。
《Mysql 实战 45 讲》链接
地址在极客时间，链接奉上。MySQL实战45讲_MySQL_数据库-极客时间。
加锁规则总结
不同版本结论不一样
丁奇老师的总结（丁奇老师的版本是 5.x 系列 <=5.7.24，8.0 系列 <=8.0.13）：包含了两个“原则”、两个“优化”和一个“bug”。
- 原则 1：加锁的基本单位是 next-key lock。希望你还记得，next-key lock 是前开后闭区间。
- 原则 2：查找过程中访问到的对象才会加锁。
- 优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。
- 优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。
- 一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。
因为间隙锁只有在可重复读下才有效，所以以下分析都是在可重复度级别下，且笔者的 Mysql 版本是 8.0.27。
加锁规则包括 两个原则、两个优化：
原则 1：加锁的基本单位是 next-key lock， 是前开后闭区间。
原则 2：查找过程中访问到的对象才会加锁。
优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。
优化 2：索引上的等值查询，向右遍历时不满足条件的时候，next-key lock 退化为间隙锁、也可能缩小间隙锁的范围。
场景实战
Tips
以下案例来源于《Mysql 实战 45 讲》第 21 讲《为什么我只改一行的语句，锁这么多》。
丁奇老师的版本是 5.x 系列 <=5.7.24，8.0 系列 <=8.0.13，我的版本是 8.0.27，我的版本以下场景测试的结果跟丁奇老师的有些出入，即丁奇老师总结的这个 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);
下面开始实战场景分析。
等值查询间隙锁
| session A | session B | session C |
|---|---|---|
| begin ; | ||
| update t set d = d+1 where id = 7; | ||
| insert into t value (8,8,8);(阻塞) | ||
| update t set d = d+1 where id = 10;（成功） |
场景说明：
- session A 开启一个事务，更新 id = 7 的数据（该数据不存在）。
- session B 插入一条 id = 8 的数据，此时阻塞。
- session C 更新 id = 10 的数据，更新成功。
分析：
- session A 更新一条不存在的数据 id = 7，根据原则一和原则二，从左到右查找数据，找到间隙锁id(5,10)，再加上行锁 10 组成 id(5,10] 前开后闭的 next-key lock
。 - 根据优化2，因为 session A 的更新条件是等值查询，next-key lock 最后一个值是 10，不满足 id = 7，所以 session A 加锁退化为间隙锁，即 session A 最终的加锁范围是 id(5,10)。
- 由此分析 session B 插入数据会阻塞，而 session C 成功。
非唯一索引等值锁
| session A | session B | session C | session D |
|---|---|---|---|
| begin ; | |||
| select id from t where c=5 lock in share mode ; | |||
| update t set d = d+1 where id = 5;(成功) | |||
| insert into t value (7,7,7);(阻塞) | |||
| update t set d = d+1 where c = 5;(阻塞) |
场景说明：
- session A 开启一个事务，且查询覆盖索引 c=5加上了读锁。
- session B 成功更新 id =5 的数据。
- session C 插入 id = 7 阻塞。
- sessoin D 更新 c=5 的数据。
分析：
- session A 从左往右查询，第一个间隙锁是 c(0,5)，加上行锁 5 组成 c(0,5) 的 next-key lock。
- 因为 c 是普通索引，所以此时还要继续往右找，找到下一个间隙 (5,10)，因为加锁的基本但是是 next-key lock，所以 id =10 的行锁也会被加上，即 c(5,10] 也会被加上锁。根据优化2，最后一条数据 c=10
不满足 c=5 的条件，所以此时会退化成 c(5,10) 的间隙锁。 - 根据原则2，只有访问到的对象才会被加锁，因为查询结果是id，走的是覆盖索引不需要回表，主键 id=5 并不会被加锁，所以 session B 才能更新成功。
- session C 需要插入 id=7 的数据，此时会被间隙锁 c(5,10) 锁住。
- session D 更新 c=5 的数据，会被 next-key lock c(0,5] 锁住。
主键索引范围锁
这个案例我测试话来跟丁奇老师的结果不一样，这是跟 mysql 的版本有关系的。