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

SQL 2020-11-20 7936 字 331 浏览 点赞

起步

我着手一个需要高并发 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] 的数据

sessionAsessionB
begin;begin;
update u set d=1 where c between 1 and 2; ok
update u set d=1 where c between 3 and 4 blocked
(1205, 'Lock wait timeout exceeded; try restarting transaction')

可以查看两个 sql 的执行计划,确定它们真有走索引:
Alt text

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

行锁与间隙锁

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

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

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

sessionAsessionB
begin;
select c from u where c=1 for update;
insert into u(id, c) value(null, 0); block
(1205, 'Lock wait timeout exceeded; try restarting transaction')

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 也相应做了小小变化:

sessionAsessionB
begin;begin;
update u set d=1 where c between 2 and 4; ok
update u set d=1 where c between 6 and 8; blocked
(1205, 'Lock wait timeout exceeded; try restarting transaction')

前提已知,对 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 是普通索引:

sessionAsessionBsessionC
begin;
select c from u where c=4 for update;
insert into u(id, c) values(null, 6); ok
insert into u(id, c) values(null, 5); blocked
(1205, 'Lock wait timeout exceeded; try restarting transaction')insert into u(id, c) values(null, 3); blocked
(1205, 'Lock wait timeout exceeded; try restarting transaction')

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 是唯一索引:

sessionAsessionB
begin;begin;
select id from u where id=4 for update;
insert into u(id, c) values(5, 100); ok
insert into u(id, c) values(3, 200); ok

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

实战分析 3

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

sessionAsessionB
begin;
select id from u where id between 4 and 8 for update;
insert into u(id, c) values(9, 100); blocked
(1205, 'Lock wait timeout exceeded; try restarting transaction')

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

sessionAsessionB
begin;begin;
select c from u where c=9 for update; ok
select c from u where c=9 for update; ok
insert into u(id, c) values(null, 9); blocked
insert into u(id, c) values(null, 9); 触发死锁检查
Query OK, 1 row affected (1213, 'Deadlock found when trying to get lock; try restarting transaction')

我们已经知道了 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 试试。

sessionAsessionB
begin;
select id from u where id >3 and id <=8 for update;
insert into u(id, c) values(3, 100); blocked
(1205, 'Lock wait timeout exceeded; try restarting transaction')

尽管 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 是分两步骤完成的:第一步,尝试上间隙锁;第二步,尝试上行锁。第一步成功之后才会执行第二步,第二步阻塞了不会影响第一步的执行结果,也就是说,这不是一个原子操作。对“两步骤”有清晰的认识是重要的。

sessionAsessionBsessionC
begin;
select id from u where id=4 for update; ok
select id from u where id>3 for update; blocked
insert into u(id, c) values(3, 100); blocked
(1205, 'Lock wait timeout exceeded; try restarting transaction')Query OK, 1 row affected

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 也可能锁住不满足条件的记录。尤其在有间隙锁的存在下,会严重影响写语句的执行。所以高并发写场景下一般会把隔离级别设置为 RC(执行set global transaction_isolation = 'READ-COMMITTED';,重新开启一个会话),凭此提升并发能力。

参考



本文由 Guan 创作,采用 知识共享署名 3.0,可自由转载、引用,但需署名作者且注明文章出处。

还不快抢沙发

添加新评论