索引下 update 只锁住符合条件的记录吗？

快来打我* 2022-12-22 13:29 68阅读 0赞

### 文章目录 ###

*  起步
 *  环境准备
 *  奇怪的 update 行为
 *  行锁与间隙锁
 *  加锁规则
 *   *  实战分析 1
     *  实战分析 2
     *  实战分析 3
     *  实战分析 4
 *  如何理解“等值查询”和“范围查询”
 *  next-key lock 是两步骤
 *  总结
 *  参考

# 起步 #

我着手一个需要高并发 update 的功能，进行压测时，发现了大量锁超时。于是所有矛头就指向我了，认为我做任务分发时重复分发了两个或两个以上相同的子任务。

这一假设基于现有的子任务 update 范围，已知更新语句走索引 c，则有：子任务 A 更新 c=\[1,10\] ；子任务 B 更新 c=\[11,20\] … 子任务 N 更新 c=\[n-9, n\]。所以同事认为，只有可能会出现相同的 A 任务（B、C … N 都行），才可能引发锁超时。

假设同事是正确的，那就意味着 update 只锁主符合条件的记录。但果真如此吗？

# 环境准备 #

压测环境隔离级别查看：

show variables like 'transaction_isolation'

得压测环境的隔离为 RR 级别，也就是：**REPEATABLE-READ**。

准备实验数据，方便研究 update 走索引时的行为：

CREATE TABLE `u` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `c` int(11) DEFAULT NULL,
      `d` int(11) DEFAULT '0',
      PRIMARY KEY (`id`),
      KEY `c` (`c`)
    ) ENGINE=InnoDB;
    
    insert into u(id, c) values(null, 1), (null, 2), (null, 3), (null, 4), (null, 5), (null, 6), (null, 7), (null, 8), (null, 9), (null, 10);

**以下 sql 均在 MySQL 5.7.31 上执行。**

# 奇怪的 update 行为 #

尝试还原业务逻辑。准备子任务 A、B：**A 负责更新 c=\[1,2\] 的数据，B 负责更新 c=\[3,4\] 的数据**。

可以查看两个 sql 的执行计划，确定它们真有走索引：  
![在这里插入图片描述][20201122214828311.png_pic_center]

所以，尽管任务 A、B 需要更新的数据没有交集，可还是造成了它们互斥，引发锁超时。也就得出了一个结论：**走索引的情况下，update 不只会锁住符合条件的记录。**

# 行锁与间隙锁 #

针对上述的“奇怪行为”其实一点也不奇怪，不过是当隔离级别为 RR 时，有一种叫“**间隙锁**”的东西。（为什么需要间隙锁，以后有空我会单独写一文进行说明，这里就只是探讨其行为）

基于上述制造的假数据，我们可以知道 c 的取值范围为 \[1, 10\]。c=1 的记录与 c=2 的记录中间就有个间隙，MySQL 上锁时可能会在这中间加一把锁，这个锁就叫间隙锁。c=1 的左边和 c=10 的右边会有默认的无穷小边界和无穷大边界，记为 `-∞` 和 `+supremum`，因此 c=1 的左边和 c=10 的右边也是有间隙的，**任何一条有效记录两边都有间隙的存在**。

姑且感受一下间隙锁的存在：

<table> 
 <thead> 
  <tr> 
   <th align="left">sessionA</th> 
   <th align="left">sessionB</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">begin;</td> 
   <td align="left"></td> 
  </tr> 
  <tr> 
   <td align="left">select c from u where c=1 for update;</td> 
   <td align="left"></td> 
  </tr> 
  <tr> 
   <td align="left"></td> 
   <td align="left">insert into u(id, c) value(null, 0); <code>block</code></td> 
  </tr> 
  <tr> 
   <td align="left"></td> 
   <td align="left"><code>(1205, 'Lock wait timeout exceeded; try restarting transaction')</code></td> 
  </tr> 
 </tbody> 
</table>

sessionA 的 select 语句只能找到一条符合条件的记录 (id=1, c=1, d=0)。如果 update 只会锁住符合条件的记录，也就只锁住 c=1 这一行，但从 sessionB insert c=0 被阻塞住来看，**c(-∞, 1) 这个间隙上也是有把锁的**。

事实上，MySQL 在对一条记录加锁时，总是会先尝试加间隙锁，如果成功了，再加行锁。**间隙锁和行锁合称 next-key lock，总是前开后闭地出现**。就 sessionA 来说，通过索引树找到 c=1 的记录之后，先在 c=1 的左边上一把间隙锁，由于 c=1 的左边没有数据了，所以上锁范围为 c(-∞, 1)，再对 c=1 上行锁。**加锁的基本单位是 next-key lock**，因此这一阶段锁住的范围是：**c(-∞, 1\]**。

我们知道，行锁分读锁和写锁，它们之间的关系如下：

但与行锁不同，**间隙锁与间隙锁之间没有冲突，间隙锁只和“往这个间隙插入一个记录”这个操作冲突**。“插入一个记录” 即可以理解为 insert 语句的插入，也可以看作是 update 语句 —— 将符合条件的数据删除，再把更新后的数据插入。

# 加锁规则 #

MySQL 对记录加锁时，有**两个“原则”，两个“优化”，一个 “bug”**：

*  原则 1：加锁的基本单位是 next-key lock （前开后闭区间）
 *  原则 2：查找过程中，访问到的对象才会加锁。
 *  优化 1：索引上的**等值查询**，给**唯一索引加锁**时， next-key lock 退化为行锁。
 *  优化 2：索引上的**等值查询**，向右遍历遇到第一个不满足条件的记录时，next-key lock 退化为间隙锁。
 *  bug 1 ：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

--------------------

为了方便对上述规则演示说明，清空表重新插入数据：

truncate u;
    
    insert into u(id, c) values(2, 2), (4, 4), (6, 6), (8, 8), (10, 10), (12, 12), (14, 14), (16, 16), (18, 18), (20, 20);

## 实战分析 1 ##

追本溯源，先来分析 “奇怪的 update 行为” 小节 sessionB 被阻塞的原因。

由于表中的数据做了修改，所以 sql 也相应做了小小变化：

前提已知，对 c 建立了普通索引。

根据**原则 1**，加锁的基本单位是 next-key，sessionA 通过索引树找到了 c=2 的记录，所以对 (-∞, 2\] 上锁。继续向右遍历，找到 c=4，对 (2, 4\] 上锁。继续向右遍历，找到 c=6，不满足条件了，但是由于**原则 2** 访问过的对象要加锁，于是对 (4, 6\] 上锁。 综合而得，sessionA 上锁范围是： (-∞, 6\]。

同样思路对 sessionB 分析，可得如果 sessionB 能成功执行，其上锁范围是：(4, 10\]。与 sessionA 存在 c=6 的行锁冲突，所以 sessionB 被阻塞。

## 实战分析 2 ##

再来看看如何理解 优化 1 和 优化 2。

已知 c 是普通索引：

<table> 
 <thead> 
  <tr> 
   <th align="left">sessionA</th> 
   <th align="left">sessionB</th> 
   <th align="left">sessionC</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">begin;</td> 
   <td align="left"></td> 
   <td align="left"></td> 
  </tr> 
  <tr> 
   <td align="left">select c from u where c=4 for update;</td> 
   <td align="left"></td> 
   <td align="left"></td> 
  </tr> 
  <tr> 
   <td align="left"></td> 
   <td align="left">insert into u(id, c) values(null, 6); <code>ok</code></td> 
   <td align="left"></td> 
  </tr> 
  <tr> 
   <td align="left"></td> 
   <td align="left">insert into u(id, c) values(null, 5); <code>blocked</code></td> 
   <td align="left"></td> 
  </tr> 
  <tr> 
   <td align="left"></td> 
   <td align="left"><code>(1205, 'Lock wait timeout exceeded; try restarting transaction')</code></td> 
   <td align="left">insert into u(id, c) values(null, 3); <code>blocked</code></td> 
  </tr> 
  <tr> 
   <td align="left"></td> 
   <td align="left"></td> 
   <td align="left"><code>(1205, 'Lock wait timeout exceeded; try restarting transaction')</code></td> 
  </tr> 
 </tbody> 
</table>

sessionA 中，根据索引树找到 c=4，对 (2, 4\] 上锁，向右找到 c=6 不满足条件，由于是等值查询，根据 **优化 2** next-key lock 退化为间隙锁，因此对 (4, 6) 上锁。综合得 sessionA 上锁范围：(2, 6)。

所以，在 sessionB 中插入 c=6 的记录执行成功，而插入 c=5 的记录被阻塞。

所以，在 sessionC 中插入 c=3 的记录被阻塞。

--------------------

以上针对的是普通索引，我们再来看看唯一索引。执行 truncate u 清空表，重新插入数据。

已知 id 是唯一索引：

sessionA 中，先找到 c=4 的记录，本应该加 (2, 4\] 的 next-key lock，但因为 **优化 1** 的存在，next-key lock 退化为行锁，因此只对 (4, 4\] 上锁。又因为是唯一索引，不必再向右遍历数据。综合得 sessionA 上锁范围：(4, 4\]。因此 sessionB 中的 sql 都可以正常执行。

## 实战分析 3 ##

对于唯一索引来说，其存在的记录一定唯一，只要找到了符合条件的记录就可以终止查询。这也是 “实战分析 2 - 唯一索引” 中 sessionA 没有继续向右查找的原因。但在**范围查询**中，唯一索引却存在 **bug 1** 这样的“遗憾行为”。

<table> 
 <thead> 
  <tr> 
   <th align="left">sessionA</th> 
   <th align="left">sessionB</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">begin;</td> 
   <td align="left"></td> 
  </tr> 
  <tr> 
   <td align="left">select id from u where id between 4 and 8 for update;</td> 
   <td align="left"></td> 
  </tr> 
  <tr> 
   <td align="left"></td> 
   <td align="left">insert into u(id, c) values(9, 100); <code>blocked</code></td> 
  </tr> 
  <tr> 
   <td align="left"></td> 
   <td align="left"><code>(1205, 'Lock wait timeout exceeded; try restarting transaction')</code></td> 
  </tr> 
 </tbody> 
</table>

sessionA 中，先从索引树上找到 id=4 的记录，本来应该对 (2, 4\] 上锁，但因为 **优化 1**，所以加锁 (4, 4\]。向右遍历，找到 id=6，对 (4, 6\] 上锁；找到 id=8，对 (6, 8\] 上锁。照理说查询应该终止了，但因为 **bug 1**，继续向右遍历，找到 id=10，不满足条件，根据 **优化 2** 退化为间隙锁 (8, 10)。综合得 sessionA 上锁范围：\[4, 10)。

因此，sessionB 插入 id=9 的记录时，被阻塞住了。

## 实战分析 4 ##

<table> 
 <thead> 
  <tr> 
   <th align="left">sessionA</th> 
   <th align="left">sessionB</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">begin;</td> 
   <td align="left">begin;</td> 
  </tr> 
  <tr> 
   <td align="left">select c from u where c=9 for update; <code>ok</code></td> 
   <td align="left"></td> 
  </tr> 
  <tr> 
   <td align="left"></td> 
   <td align="left">select c from u where c=9 for update; <code>ok</code></td> 
  </tr> 
  <tr> 
   <td align="left">insert into u(id, c) values(null, 9); <code>blocked</code></td> 
   <td align="left"></td> 
  </tr> 
  <tr> 
   <td align="left"></td> 
   <td align="left">insert into u(id, c) values(null, 9); <code>触发死锁检查</code></td> 
  </tr> 
  <tr> 
   <td align="left"><code>Query OK, 1 row affected</code></td> 
   <td align="left"><code>(1213, 'Deadlock found when trying to get lock; try restarting transaction')</code></td> 
  </tr> 
 </tbody> 
</table>

我们已经知道了 c 有普通索引，所以 sessionA、B 的 select 语句根据 **原则 1、优化 2** 可得知 sessionA 上了间隙锁 (8, 10)，sessionB 上了间隙锁 (8, 10)。间隙锁不会互斥，所以 select 都能执行成功。

之后是 sessionA insert c=9，由于 sessionB 上了间隙锁 (8, 10)，所以 sessionA 被阻塞，等 sessionB 释放资源。然后 sessionB 也执行了 insert c=9，因为 sessionA 上了间隙锁 (8, 10)，所以 sessionB 也被阻塞，等 sessionA 释放资源。相互等待对方释放资源，触发死锁检查，在我的实验环境上 MySQL 强行回滚 sessionB，因此 sessionA 执行成功，输出 “Query ok, 1 row affected” 。

# 如何理解“等值查询”和“范围查询” #

不知道你对 “实战分析 3 - sessionA” 的上锁范围有没有疑问，明明 `between 4 and 8` 是范围查询，为什么对 id=4 的记录上锁时用到了等值查询的优化（优化1）。

事实上，范围查询过程中，在找**起始点**时，可以理解为等值查询。所以找第一条记录 id=4 是等值查询，就用到了优化 1。

`select id from u where id between 4 and 8 for update;` 可以看作是 `select id from u where id>=4 and id<=8 for update;` ，现在将 `id>=4` 改成 `id>3` 试试。

<table> 
 <thead> 
  <tr> 
   <th align="left">sessionA</th> 
   <th align="left">sessionB</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">begin;</td> 
   <td align="left"></td> 
  </tr> 
  <tr> 
   <td align="left">select id from u where id &gt;3 and id &lt;=8 for update;</td> 
   <td align="left"></td> 
  </tr> 
  <tr> 
   <td align="left"></td> 
   <td align="left">insert into u(id, c) values(3, 100); <code>blocked</code></td> 
  </tr> 
  <tr> 
   <td align="left"></td> 
   <td align="left"><code>(1205, 'Lock wait timeout exceeded; try restarting transaction')</code></td> 
  </tr> 
 </tbody> 
</table>

尽管 id>=4 和 id>=3 的查询结果相同，但后者的起始点是 id=3，或者理解为：起始点在记录 id=2 与记录 id=4 之间（不存在 id=3 的记录）。之后往右遍历找到 id=4 的记录，此时处于范围查询，又满足查询条件，所以对 (2, 4\] 上锁。故 sessionB 被阻塞。

# next-key lock 是两步骤 #

原则 1 说加锁的基本单位是 next-key lock，即：间隙锁+行锁。但要清楚地知道，next-key lock 是分两步骤完成的：第一步，尝试上间隙锁；第二步，尝试上行锁。**第一步成功之后才会执行第二步，第二步阻塞了不会影响第一步的执行结果**，也就是说，这不是一个原子操作。对“两步骤”有清晰的认识是重要的。

<table> 
 <thead> 
  <tr> 
   <th align="left">sessionA</th> 
   <th align="left">sessionB</th> 
   <th align="left">sessionC</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">begin;</td> 
   <td align="left"></td> 
   <td align="left"></td> 
  </tr> 
  <tr> 
   <td align="left">select id from u where id=4 for update; <code>ok</code></td> 
   <td align="left"></td> 
   <td align="left"></td> 
  </tr> 
  <tr> 
   <td align="left"></td> 
   <td align="left">select id from u where id&gt;3 for update; <code>blocked</code></td> 
   <td align="left"></td> 
  </tr> 
  <tr> 
   <td align="left"></td> 
   <td align="left"></td> 
   <td align="left">insert into u(id, c) values(3, 100); <code>blocked</code></td> 
  </tr> 
  <tr> 
   <td align="left"></td> 
   <td align="left"><code>(1205, 'Lock wait timeout exceeded; try restarting transaction')</code></td> 
   <td align="left"><code>Query OK, 1 row affected</code></td> 
  </tr> 
 </tbody> 
</table>

id 为唯一索引，所以 sessionA 最后只会上 id=4 的行锁。

sessionB 以范围查询的方式找到了 id=4 的记录，开始上间隙锁 (2, 4) 成功，再上 id=4 的行锁。但因为 sessionA 已经在 id=4 上加了排他锁，于是 sessionB 只能阻塞住。

sessionC 准备在 id(2, 4) 之间插入一条数据，但被 sessionB 加的间隙锁给阻塞住了。直到 sessionB 因为 lock wait timeout 回滚而释放间隙锁，sessionC 几乎在同一时间 insert 成功。

# 总结 #

可知，即使走了索引，即使没有被优化器优化为全表扫描（[《update 走索引后会不会锁全表？》][update]）， update 也可能锁住不满足条件的记录。尤其在有间隙锁的存在下，会严重影响写语句的执行。所以高并发写场景下一般会把隔离级别设置为 RC（执行`set global transaction_isolation = 'READ-COMMITTED';`，重新开启一个会话），凭此提升并发能力。

# 参考 #

*  感谢 极客时间丁奇老师的 [《MySQL实战45讲》][MySQL_45]

[20201122214828311.png_pic_center]: /images/20221120/b51ecb8d2b294984a51d9c436f41adec.png
[update]: https://youguanxinqing.xyz/archives/146/
[MySQL_45]: https://time.geekbang.org/column/intro/139