MySQL 加锁案例--基于《MySQL 45 讲》的第 21 讲的总结

前言

丁奇大佬的《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 版本验证一致）

操作序列如下：

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 的操作就不会造成死锁。

案例九：结尾问题（8.0.34 版本验证一致）