MySQL锁（四）行锁的加锁规则和案例
在上一篇文章，我们学习了间隙锁和next-key lock，但是不知道怎么加锁，有哪些规则。间隙锁的概念不太好理解，尤其是配合上行锁后，很容易在判断是否会出现锁等待的问题上犯错。
今天我们就来学习一下加锁规则吧。
在学习前要说明一点，以下的规则只限于版本范围：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：唯一索引上的范围查询会访问到不满足条件的第一个值为止。
下面以表t为例来介绍一下这些规则。表t的建表语句和初始化语句如下。
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：等值查询间隙锁
图1 等值查询间隙锁
分析：
- 步骤1：根据原则1，加锁(5, 10]
- 步骤2：根据优化2，id=10不满足查询条件，因此退化为间隙锁(5, 10)
结论：
- Session B阻塞是因为id=8在间隙锁(5, 10)内
- Session C可以执行是因为没有锁住id=10这行
案例2：非唯一索引等值锁
图2 非唯一索引等值锁
分析：
- 步骤1：根据原则1，加锁(0, 5]
- 步骤2：由于c是普通索引，因此还需要继续遍历，直到找到c=10，不满足条件。根据原则2，访问到的对象要加锁，因此要加(5, 10]。
- 步骤3：同时根据优化2，这是一个等值查询，向右遍历的不满足条件的第一个值10，(5, 10]要退化为间隙锁(5, 10)
- 因此加锁是索引c的next-key lock(0,5]和间隙锁(5,10)
结论：
- Session B可以执行是因为加锁的是索引c，而不是主键索引
- Session C阻塞是因为c的插入值是7，在间隙锁(5, 10)范围内
案例3：主键索引范围锁
图3 主键索引范围锁
分析：
- 步骤1：根据原则1，加锁(5, 10]
- 步骤2：根据优化1，退化为id=10的行锁
- 步骤3：继续遍历，找到不满足id<11的值id=15，加锁(10, 15]
因此加锁id=15的行锁和id的next-key lock(10, 15]
结论：
- 插入id=13被阻塞：next-key lock (10, 15]
- 更新id=15被阻塞：next-key lock (10, 15]
案例4：非唯一索引范围锁
图4 非唯一索引范围锁
分析：
- 步骤1：根据原则1，加锁(5, 10]
- 步骤2：继续遍历，找到不满足c<11的值c=15，根据原则2，加锁(10, 15]
因此加锁索引c (5, 10]和(10, 15]
结论：
- 插入c=8，被(5, 10]阻塞
- 更新c=15，被(10, 15]阻塞
案例5：唯一索引范围锁bug
图5 唯一索引范围锁bug
分析：
- 步骤1：根据原则1，加锁(10, 15]，再根据优化1，退化为id=15的行锁
- 步骤2：向右遍历，加锁(10, 15]
- 步骤3：根据BUG，要访问到不满足条件的第一个值，即id=20，加锁(15 ,20]。
因此加锁为(10, 15]和(15, 20]
结论：
- 更新id=20阻塞，被(15, 20]锁住
- 插入id=16阻塞，被(15, 20]锁住
案例6：非唯一索引上存在”等值”的例子
mysql> insert into t values(30,10,30);
图6 非唯一索引上存在”等值”的例子
分析：
- 步骤1：根据原则1，(c=5,id=5)到(c=10,id=10)这个next-key lock
- 步骤2：向右查找，直到碰到(c=15,id=15)这一行，循环才结束。根据优化2，这是一个等值查询，向右查找到了不满足条件的行，所以会退化成(c=10,id=10) 到 (c=15,id=15)的间隙锁
因此加锁(c=5,id=5)到(c=10,id=10)这个next-key lock和(c=10,id=10)到(c=15,id=15)这个间隙锁
结论：
- 插入c=12阻塞，被(c=10,id=10)到(c=15,id=15)这个间隙锁锁住
- 更新c=15成功，没有锁住c=15
案例7：limit 语句加锁
先插入一条记录。
mysql> insert into t values(30,10,30);
图7 limit 语句加锁
分析：
- 步骤1：根据原则1，(5, 10]，因为c=10有两条行，因此遍历到这里就结束
- 步骤2：因为是delete，因此加两个行锁（id=10和id=30）
因此加锁c (5, 10)和两个行锁（id=10和id=30）
结论：
- 插入c=12成功，因为c=12没有被锁住
说明：这个例子对我们实践的指导意义就是，在删除数据的时候尽量加limit。
案例8：一个死锁的例子
这个案例的目的是说明：next-key lock实际上是间隙锁和行锁加起来的结果。
图8 一个死锁的例子
分析：
- 步骤1：根据原则1，加锁(5, 10]
- 步骤2：继续遍历，直到c=15不满足条件，加锁(10, 15]，根据优化2，退化为(10, 15)
结论：
- Session B在等待锁，此时Session B已经加了间隙锁(5, 10)，在等待加行锁c=10。
- Session A插入c=8，也在等待锁，从而导致死锁
说明：next-key lock具体执行的时候，是要分成间隙锁和行锁两段来执行的。
案例9：非唯一索引排序范围锁
图9 非唯一索引排序范围锁
分析：
- 步骤1：先执行c=20，加锁(15, 20]
- 步骤2：根据优化2，加间隙锁(20, 25)
- 步骤3：再执行c=15，加锁(10, 15]
- 步骤4：继续向左遍历，找到记录id=10为止，加锁(5, 10]
在扫描过程中，c=20、c=15、c=10这三行都存在值，由于是select *，所以会在主键id上加三个行锁。
因此要加锁索引c (5, 25)和三个行锁（id=10，id=15，id=20）。
结论：
- 插入c=6，被c(5, 25)锁住
案例10：不等号条件里的等值查询
begin;
select * from t where id>9 and id<12 order by id desc for update;
在执行过程中，通过树搜索的方式定位记录的时候，用的是“等值查询”的方法。
分析：
- 步骤1：根据原则1，加锁 (10, 15]
- 步骤2：根据优化2，退化为(10, 15)
- 步骤3：向左遍历，找到id=10，加锁(5, 10]，继续找到id=5为止，加锁(0, 5]
案例11：in范围锁
begin;
select id from t where c in(5,20,10) lock in share mode;
分析：
说明：锁是逐个逐个加的。
- 步骤1：先c=5，加(0, 5]和(5, 10)
- 步骤2：再c=10，加(5, 10]和(10, 15)
- 步骤3：后c=20，加(15, 20]和(20, 25)
间隙锁是不互斥的，因为加锁范围是(0, 25)，除c=15外。
死锁情况
select id from t where c in(5,20,10) order by c desc for update;
有一种情况，同时执行倒序语句，因为刚好同时执行，逐渐加锁（倒序加锁），会出现死锁情况。
