likes
comments
collection
share

透过实例看行锁：深入探讨加锁原则与优化（下）

作者站长头像
站长
· 阅读数 7

在MySQL数据库运维和开发过程中，理解加锁原则并灵活运用是非常重要的。本文作为系列文章的下篇，将更深入地探讨加锁的实践应用。我们将结合实际案例，重点解析无索引修改间隙锁、非唯一索引上存在"等值"的情况，还有令人头疼的死锁问题。此外，本文也将讲述如何查看锁等待，以及根据update语句学习加锁顺序。最后，我们将从next-key lock的角度来分析加锁规则。让我们一起深入探究，理解MySQL的行锁和间隙锁的运作机制，掌握如何在复杂场景中灵活使用这些工具，优化我们的数据库运维和开发工作。

无索引修改间隙锁

情景一：

如下表结构：

CREATE TABLE t5 (a INT NOT NULL, b INT) ENGINE = InnoDB; 
INSERT INTO t5 VALUES (1,2),(2,3),(3,2),(4,3),(5,2);

事务A执行如下语句：

begin;
update t5 set b=5 where b=3;

事务B执行：

update t5 set b=4 where b=2;

可以发现，事务B会被阻塞，最终报错 Lock wait timeout exceeded; try restarting transaction。

情景二

同样还是该表结构，我们改变一下数据插入的顺序，如下所示：

CREATE TABLE t6 (a INT NOT NULL, b INT) ENGINE = InnoDB; 

INSERT INTO t6 VALUES (1,2),(3,2),(5,2),(2,3),(4,3);

重复上述操作，发现事务B正常执行。

那么为什么呢？

首先我们需要知道，表上没有索引，所以对于记录锁会用到隐藏主键（row_id）。

情景一用下图进行分析：

透过实例看行锁：深入探讨加锁原则与优化（下）

事务A需要加的锁为 row_id=1和3 的记录锁，以及(1,3)间隙锁，所以事务B执行时，因为获取不到 row_id=2 的记录锁，最终阻塞。如果将事务B改为：update t5 set a=11 where a=2;照样阻塞。

情景二按照上述方法进行分析，可以发现 row_id 不存在交叉部分，所以不会阻塞。

非唯一索引上存在"等值"

插入记录

mysql> insert into t values(30,10,30);

新插入的这一行 c=10，也就是说现在表里有两个 c=10 的行。那么，这时候索引 c 上的间隙是什么状态了呢？你要知道，由于非唯一索引上包含主键的值，所以是不可能存在“相同”的两行的。

透过实例看行锁：深入探讨加锁原则与优化（下）

这次我们用 delete 语句来验证。注意，delete 语句加锁的逻辑，其实跟 select ... for update 是类似的，也就是文章开始总结的两个“原则”、两个“优化”和一个“bug”。

透过实例看行锁：深入探讨加锁原则与优化（下）

-- session A
delete from t where c=10;

-- session B、C、D三者
-- session B. block
update t set d=11 where c=10; 

-- session C. block
update t set d=12 where id=10;
-- session D. block
update t set d=31 where id=30;

这时，session A 在遍历的时候，先访问第一个 c=10 的记录。同样地，根据原则 1，这里加的是 (c=5,id=5) 到 (c=10,id=10) 这个 next-key lock(5,10]。

然后，session A 向右查找，直到碰到 (c=15,id=15) 这一行，循环才结束。根据优化 2，这是一个等值查询，向右查找到了不满足条件的行，所以会退化成 (c=10,id=10) 到 (c=15,id=15) 的间隙锁(10,15)。

最终 SessionA 的加锁范围为 next-key lock(5,10] 和间隙锁(10,15)，还有两个行锁 id=10 和 id=30，因为 delete 语句会根据主键索引，将扫描到的行记录加上行锁。（索引c=10和索引id=10、30的锁都是行锁）

也就是说，这个 delete 语句在索引 c 上的加锁范围，就是下图中蓝色区域覆盖的部分。

透过实例看行锁：深入探讨加锁原则与优化（下）

这个蓝色区域左右两边都是虚线，表示开区间，即 (c=5,id=5) 和 (c=15,id=15) 这两行上都没有锁。（ 如果 session b 插入(4,5,50)，不会被锁，如果插入（6,5,50） 会被锁住，因为 6,5,50 根据主键来排列的话 是在 5,5,5 后面的 ）

limit 语句加锁

对比上述案例，看如下内容：

透过实例看行锁：深入探讨加锁原则与优化（下）

这个例子里，session A 的 delete 语句加了 limit 2。你知道表 t 里 c=10 的记录其实只有两条，因此加不加 limit 2，删除的效果都是一样的，但是加锁的效果却不同。

在遍历到 (c=10, id=30) 这一行之后，满足条件的语句已经有两条，循环就结束了。

因此，索引 c 上的加锁范围就变成了从（c=5,id=5) 到（c=10,id=30) 这个前开后闭区间，如下图所示：

透过实例看行锁：深入探讨加锁原则与优化（下）

在删除数据的时候尽量加 limit。这样不仅可以控制删除数据的条数，让操作更安全，还可以减小加锁的范围。 同理，在我们执行查询语句时，如果只是查询单条记录，尽量也加上 limit。

一个死锁的例子

-- session A
begin;
select id from t where c=10 lock in share mode;
insert into t values(8,8,8);
-- success
insert into t values(12,12,12);

-- session B
update t set d=d+1 where c=10;

透过实例看行锁：深入探讨加锁原则与优化（下）

  1. session A 启动事务后执行查询语句加 lock in share mode，在索引 c 上加了 next-key lock(5,10] 和间隙锁 (10,15)；
  2. session B 的 update 语句也要在索引 c 上加 next-key lock(5,10] ，先加间隙锁(5,10)，成功后继续获取行锁10，因为该锁被 session A 持有，所以进入锁等待；注意，此时 session B没能获取到间隙锁(10,15)。
  3. 然后 session A 要再插入 (8,8,8) 这一行，被 session B 的间隙锁锁住（session A加的间隙锁是为了阻塞其他事务的操作，对本事务不影响）。由于出现了死锁，InnoDB 让 session B 回滚。

简单来说就是：sessionA 等 sessionB 的间隙锁, sessionB 等 sessionA 的行锁，最终死锁。

session B 的“加 next-key lock(5,10] ”操作，实际上分成了两步，先是加 (5,10) 的间隙锁，加锁成功；然后加 c=10 的行锁，这时候才被锁住的。

我们在分析加锁规则的时候可以用 next-key lock 来分析。但是要知道，具体执行的时候，是要分成间隙锁和行锁两段来执行的。

怎么看死锁？

在出现死锁后，执行 show engine innodb status 命令得到的部分输出。这个命令会输出很多信息，有一节 LATEST DETECTED DEADLOCK，就是记录的最后一次死锁信息。

透过实例看行锁：深入探讨加锁原则与优化（下）

我们来看看这图中的几个关键信息。

1、这个结果分成三部分：
(1) TRANSACTION 2644529，是第一个事务的信息,对应 session B
(2) TRANSACTION 2644530，是第二个事务的信息，对应 session A
WE ROLL BACK TRANSACTION (1)，是最终的处理结果，表示回滚了第一个事务。
2、第一个事务的信息中：
WAITING FOR THIS LOCK TO BE GRANTED，表示的是这个事务在等待的锁信息；
lock_mode X waiting,表示自己加了一个next-key 锁，当前状态是等待中
0: len 4; hex 8000000a; asc     ;;是第一个字段，也就是 c。值是十六进制 a，也就是 10；
1: len 4; hex 8000000a; asc     ;;是第二个字段，也就是主键 id，值也是 10；

3、第二个事务显示的信息要多一些：
“ HOLDS THE LOCK(S)”用来显示这个事务持有哪些锁；
lock mode S 表示自己持有读锁
lock_mode X locks gap before rec insert intention waiting 表示准备插入意向锁，尝试获取间隙锁

从上面这些信息中，我们就知道：

  • Session A持有 next-key lock(5,10] 和间隙锁 (10,15)
  • Session B 持有间隙锁(5,10)，在等 session A中 c=10 的记录锁，
  • Session A 的 insert 语句被 session B 的间隙锁锁住，最终进入死锁局面。

这里，我们可以得到两个结论：

  • 由于锁是一个个加的，要避免死锁，对同一组资源，要按照尽量相同的顺序访问；
  • 在发生死锁的时刻，for update 这条语句占有的资源更多，回滚成本更大，所以 InnoDB 选择了回滚成本更小的 lock in share mode 语句，来回滚。

怎么看锁等待？

 begin;
 select * from t where id>10 and id<=15 for update;
 
 delete from t where id=10;
 insert into t values(10,10,10);

透过实例看行锁：深入探讨加锁原则与优化（下）

查看锁信息

透过实例看行锁：深入探讨加锁原则与优化（下）

执行 show engine innodb status ，查看返回结果如下：

透过实例看行锁：深入探讨加锁原则与优化（下）

结合上面两张图，可知 session B 的 insert 语句是被 next-key lock(x,15] 阻塞的，准确来说是当中的间隙锁，下面作图来给你分析这一过程。

首先本例会有两处间隙锁：(10,15]、(15,20)，由于 delete 操作把 id=10 这一行删掉了，原来的两个间隙 (5,10)、(10,15）变成了一个 (5,15)，如下图所示：

透过实例看行锁：深入探讨加锁原则与优化（下）

注意：间隙和间隙锁并不相同，间隙 (5,10)、(10,15）是客观存在的，“锁”是执行过程中才加的，是一个动态的概念。

所以得出以下结论：所谓“间隙”，其实根本就是由“这个间隙右边的那个记录”定义的，向左延伸。

update语句学习加锁顺序

begin;
select c from t where c>5 lock in share mode;

update t set c=3 where c=5;
-- blocked
update t set c=5 where c=3;
-- success
select * from t where c=0 for update;

透过实例看行锁：深入探讨加锁原则与优化（下）

session A 的加锁范围是索引 c 上的 (5,10]、(10,15]、(15,20]、(20,25]和 (25,supremum]，简化为(5,supremum]。

之后 session B 的第一个 update 语句，要把 c=5 改成 c=1，你可以理解为两步：

  • 插入 (c=1, id=5) 这个记录，间隙锁(1,5)不会阻塞该操作
  • 删除 (c=5, id=5) 这个记录，session A 加锁范围为 (1,supremum]

session A 的加锁范围变成下图这样：

透过实例看行锁：深入探讨加锁原则与优化（下）

接下来 session B 要执行 update t set c = 5 where c = 1 这个语句了，一样地可以拆成两步：

  • 插入 (c=5, id=5) 这个记录，被间隙锁(1,supremum]阻塞
  • 删除 (c=1, id=5) 这个记录

第一步试图在已经加了间隙锁的 (1,10) 中插入数据，所以就被堵住了。

小结

可重复读隔离级别遵守两阶段锁协议，所有加锁的资源，都是在事务提交或者回滚的时候才释放的。

在分析加锁规则的时候可以用 next-key lock 来分析。但是要知道，具体执行的时候，是要分成间隙锁和行锁两段来执行的。

如果切换到读提交隔离级别 (read-committed) 的话，就好理解了，过程中去掉间隙锁的部分，也就是只剩下行锁的部分。

在读提交隔离级别下还有一个优化，即：语句执行过程中加上的行锁，在语句执行完成后，就要把“不满足条件的行”上的行锁直接释放了，不需要等到事务提交。