MySQL45讲之InnoDB加锁规则

前言

本文介绍 MySQL InnoDB 的加锁规则，以及一些需要注意的点。

总结

可重复读隔离级别下，两个原则，两个优化，一个 bug：

原则1：加锁的基本单位是 next-key 锁，是一个前开后闭区间
原则2：查找过程中访问到的记录才会加锁

优化1：索引的等值查询，唯一索引加锁时，next-key 会退化为行锁
优化2：索引的等值查询，向右遍历时且最后一个值不符合时，最后一个 next-key 会退化为间隙锁

一个bug：唯一索引上的范围查询会访问到第一个不满足条件的值为止。

注意，以上的加锁规基于版本 MySQL 5.x <= 5.7.24，8.x <= 8.0.13

提交读隔离级别下，一个优化：语句执行过程中加的行锁，在语句执行完成后，会把不符合条件的行锁释放，不需要等待事务提交。

注意

1、lock in share mode 和 for update 的区别

使用 lock in share mode 上锁读时，可以进行索引覆盖，那么只会对覆盖索引树上锁，而不会对主键索引树上锁。使用 for update 上锁读时，系统认为接下来需要进行更新操作，所以会给符合条件的记录加上行锁。

这个指导我们，如果想用 lock in share mode 加读锁来避免记录被更新，那么需要避免索引覆盖的情况，否则通过主键索引还是可以修改记录。

2、使用 limit 可以减小加锁范围

当对 limit 数量的记录执行完操作后，将不再扫描后续的记录，也就不会再加锁。

3、虽然分析加锁区间用 next-key，但注意 next-key 是行锁和间隙锁的组合

看下图的事务流程：

你或许会疑问，session B 不是没有申请到 next-key 么。

是这样的，申请 next-key 分为两步，session B 首先申请 （5，10）的间隙锁，申请成功；然后申请 c=10 的行锁，阻塞，即申请 next-key 阻塞是阻塞在申请行锁的时候。因为 session B 申请了（5，10）的间隙锁，所以 session A 插入（8，8，8）阻塞。

参考

• [1] 为什么我只改一行的语句，锁这么多