在上一篇文章中，我们学习了间隙锁和下一键锁，但是不知道它们是如何加锁的，加锁的规则是什么。间隙锁的概念不太好理解，尤其是与上行锁结合使用时，在判断是否会发生锁等待时很容易出错。

今天我们来学习锁定规则。

学习前请注意以下规则仅限于版本范围：5.x系列<=5.7.24、8.0系列<=8.0.13。

锁定规则

这个锁定规则包含两个“原理”、两个“优化”和一个“bug”。

  1. 原理1：锁定的基本单位是下一键锁定。我希望你还记得下一键锁是一个开闭区间。
  2. 原则2：只有在搜索过程中访问过的对象才会被锁定。
  3. 优化一：对于索引的等价查询，锁定唯一索引时，下一键锁退化为行锁。
  4. 优化2：对于索引的等价查询，当向右遍历且最后一个值不满足相等条件时，next-key锁退化为间隙锁。
  5. 一个bug：唯一索引上的范围查询将访问第一个不满足条件的值。

下面以表t为例介绍这些规则。表t的建表语句和初始化语句如下。 

创建表 `t` (
  `id` int(11) NOT NULL,
  `c` int(11) 默认为 NULL，
  `d` int(11) 默认为 NULL，
  主键（`id`），
  键“c”（“c”）
) 引擎=InnoDB;

插入 t 值(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)

结论：

  • 会话 B 被阻止，因为 id=8 处于间隙锁 (5, 10)
  • 会话C可以执行，因为id=10的线路没有锁定

情况 2：非唯一索引相等锁


图2 非唯一索引等价锁

分析：

  • 第一步：根据原理1，锁定(0, 5]
  • 第2步：由于c是普通索引，所以需要继续遍历，直到找到c=10，不满足条件。根据原则2，访问的对象必须被锁定，所以必须加上(5, 10]。
  • 第3步：同时根据优化2，这是一个等价查询。第一个不满足向右遍历条件的值10，(5, 10]会退化为间隙锁(5, 10)
  • 所以锁定是索引c
    • 的下一键锁(0,5]和间隙锁(5,10)

结论：

  • 会话B可以执行，因为索引c被锁定，而不是主键索引
  • 会话 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 (10, 15]的下一键锁

结论：

  • 插入 id=13 被阻止：下一键锁定 (10, 15]
  • 更新 id=15 被阻止：下一键锁定 (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：独特的索引范围锁定错误


图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>插入t值(30,10,30);

图 6 非唯一索引上的“等价”示例

分析：

  • 第1步：根据原理1，(c=5,id=5)到(c=10,id=10)这个下一键锁
  • 第2步：向右搜索，直到遇到行（c=15，id=15）​​，循环结束。根据优化 2，这是一个等效查询。不满足条件的行都在右边找到，所以会退化为间隙锁，从(c=10,id=10)到(c=15,id=15)

因此将 (c=5,id=5) 锁定到 (c=10,id=10) 这个下一键锁定，将 (c=10,id=10) 锁定到 (c=15,id=15) 这个间隙锁

结论：

  • 插入c=12被间隙锁阻止并锁定，从(c=10, id=10)到(c=15, id=15)
  • 更新c=15成功，c=15未锁定

情况 7：限制语句锁定

先插入一条记录。 

mysql>插入t值(30,10,30);

图7 limit语句加锁

分析：

  • 第1步：根据原理1，(5, 10]，因为c=10有两行，所以遍历到这里结束
  • 第二步：因为是删除，所以添加两个行锁（id=10和id=30）

因此锁 c (5, 10) 和两个行锁（id=10 和 id=30）

结论：

  • c=12插入成功，因为c=12没有锁定

说明：这个例子对我们实践的指导意义是尝试在删除数据时添加限制。

案例8：死锁示例

这个案例的目的是为了说明：下一键锁实际上是间隙锁和行锁之和的结果。


图8 死锁示例

分析：

  • 第1步：根据原理1，添加锁(5, 10]
  • 第2步：继续遍历，直到c=15不满足条件，锁定(10, 15]，根据优化2，退化为(10, 15)

结论：

  • 会话 B 正在等待锁定。此时Session B已经添加了间隙锁(5, 10)，正在等待行锁c=10。
  • 会话A插入c=8，并且也在等待锁，导致死锁

说明：具体执行next-key lock时，分为两个阶段：间隙锁和行锁。

情况9：非唯一索引排序范围锁定


图9 非唯一索引排序范围锁

分析：

  • 第一步：首先执行c=20并锁定(15, 20]
  • 第2步：根据优化2，添加间隙锁(20, 25)
  • 第3步：再次执行c=15并锁定(10, 15]
  • 第四步：继续向左遍历，直到找到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：不等号条件等价查询

开始；
select * from t where id>9 and id<12 order by id desc for update;

执行过程中，通过树搜索定位记录时，采用“等价查询”的方法。

分析：

  • 第一步：根据原理1，锁定(10, 15]
  • 第2步：根据优化2，退化为(10, 15)
  • 第3步：向左遍历，找到id=10，锁定(5, 10]，继续找到id=5，锁定(0, 5]

情况11：范围锁定

开始；
select id from t where c in(5,20,10) 以共享模式锁定；

分析：

注：锁是一一添加的。

  • 第 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 进行更新；

存在同时执行逆序语句的情况。因为它们恰好同时执行，逐渐加锁（逆序加锁），就会出现死锁的情况。

参考文献

  • 21 |为什么我只改一行语句就有这么多锁？