前言
丁奇大佬的《MySQL 45 讲》可以说是每个 DBA boy 的必读经典,但教材中,大佬用的毕竟是 5.7 版本,日常用 8.0 的我还是得持怀疑的态度阅读文章。毕竟实践是检验真理的唯一标准。因此对 21 讲做了个总结(幸好 8.0 和 5.7 有出入,不然白搞了)。
案例总结+验证+一点点个人思考
秉承着能白嫖就不花钱的原则,贴上《45 讲》的链接,本文是对 21 讲的一个总结和验证:
21 为什么我只改一行的语句,锁这么多? | MySql实战45讲 (gitbook.io)
但花钱是能看评论区的,评论区的干货不比正文少(yes,我在叠甲)。
有关加锁规则的两个“原则”、两个“优化”和一个“bug”:
- 原则1:加锁的基本单位是 next-key lock。next-key lock 是前开后闭区间。
- 原则2:查找过程中访问到的对象才会加锁。
- 优化1:索引上的等值查询,给唯一索引加锁的时候,next-key lock 退化为行锁。
- 优化2:索引上的等值查询,向右遍历时且最后一个值不满足等值条件的时候,next-key lock 退化为间隙锁。
- 一个 bug:唯一索引上的范围查询会访问到不满足条件的第一个值为止。(8.0.34 无法复现,官方应该已经修复)
示例数据:
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);
下面我会在自己的 MySQL 8.0.34 版本上一一验证文章中的案例。有同学要问了,问得好!说你要怎么验证呢?很简单,开两个会话一一执行,观察结果就可以了。
案例一:主键索引等值查询(8.0.34 版本验证一致)
update t set d = d + 1 where id = 7;
- 原则1 + 优化2,加锁范围是主键上的 (5,10)
案例二:非唯一索引等值查询(8.0.34 版本验证一致)
select id from t where c = 5 lock in share mode;
- 使用了覆盖索引,只给索引 c 加锁,主键不加锁
- 原则1,加锁范围 (0,5];c 是普通索引,还要向右遍历,加锁范围 (5,10];优化2,加锁范围(5,10] 退化为 (5,10)
- 总结:加锁范围索引 c 上的 (0,10)
案例三:主键索引范围查询(8.0.34 版本略有出入)
select * from t where id >= 10 and id < 11 for update;
- 由于id是int,语义上跟 select * from t where id = 10 for update 是一样的,但加锁范围不一样
- id=10 出发,从 (5,10] 退化为 id=10 上的行锁;继续向右找到 id=15,加 (10,15] 间隙锁
- 但经过 8.0.34 版本的验证,id=15 上并没有锁,所以后面的版本应该做了优化,只加了 [10,15)
- 总结:加锁范围是主键上的 [10,15)(《45 讲》原文是 [10,15])
案例四:非唯一索引范围查询(经 8.0.34 版本验证)
select * from t where c >= 10 and c < 11 for update;
- 与案例三的分析过程一样,不同的是 c 是非唯一索引,所以没有用上优化 2,最终加锁范围是 (5,10] 和 (10,15] 两个 next-key lock
- 总结:加锁范围是主键上的 (5,15]
案例五:唯一索引范围锁bug(8.0.34 版本已修复)
select * from t where id > 10 and id <= 15 for update;
- 加锁范围 (10,15] 和 (15,20]
- 8.0.34 只有 (10,15] 加了锁
- 总结:加锁范围是主键上的 (10,15](《45 讲》原文是 (10,20])
案例六:非唯一索引上有相同值(8.0.34 版本验证一致)
- 首先插入一行:
insert into t values(30,10,30);
使表中有两行c=10的记录 - 然后:
delete from t where c = 10;
- 加锁范围如图(图中是索引c上的锁,主键上只锁住了id=10和id=30两行)
案例七:案例六+limit(8.0.34 版本验证一致)
delete from t where c = 10 limit 2;
- 加了limit,找到了满足条件的数量后,就不需要再向右遍历了,所以加锁范围变成了
- 有些网上流传的 MySQL 规范说 delete、update 不要加 limit,是考虑到了主从数据一致性,但这里可以看出,加 limit 能减小锁范围,也就是改善了并发度。因此 delete、update 加不加 limit 见仁见智。(是的,我的思考就这么一点点)
案例八:next-key 锁申请过程中,是先申请间隙锁,再申请行锁的(8.0.34 版本验证一致)![](https://img-blog.csdnimg.cn/direct/8c298bd8ef7d4b66a3e960c98c8da350.png)
操作序列如下:
1. A的查询给索引c加上了 (5,10] 和间隙锁 (10,15);
2. B要 (5,10] 的间隙锁,进入锁等待(实际上已经获取了 (5,10),只是 [10] 不让获取,造成等待);
3. A 的插入被 B 的间隙锁锁住,出现死锁,B 的操作被回滚
结论:session B 的“加 next-key lock(5,10] ”操作,实际上分成了两步,先是加 (5,10) 的间隙锁,加锁成功;然后加 c=10 的行锁,这时候才被锁住的。如果是间隙锁和行锁一起作为整体获得,也就是第 2 步 B 什么锁也没拿到,那在第 3 步 A 的操作就不会造成死锁。