【MySQL锁篇】MySQL死锁问题以及解决方案

时间点	事务A	事务B
1	BEGIN;	BEGIN;
2	`UPDATE accounts SET balance = balance - 100 WHERE id = 1;` (成功获取id=1的行锁)
3		`UPDATE accounts SET balance = balance + 100 WHERE id = 2;` (成功获取id=2的行锁)
4	`UPDATE accounts SET balance = balance + 100 WHERE id = 2;` *(等待事务B释放id=2的锁)*
5		`UPDATE accounts SET balance = balance - 100 WHERE id = 1;` *(等待事务A释放id=1的锁)*

此时，循环等待形成：A在等B，B又在等A。InnoDB检测到死锁，会回滚其中一个事务。

根本原因： 虽然两个事务更新的行不同，但加锁的顺序不一致（A: 1->2, B: 2->1）。

场景2：select..for update和insert混合的场景

假设有下面这一张表:

id（主键索引)	no(非主键索引)	name
1	1001	小明
2	1002	小李
3	1003	小华
4	1004	小黄

在这一张表当中,id为主键索引，为二级索引,name这一列没有任何索引的约束。

现在这张表当中，有以上的一些数据。

现在，有两个事物，一个事物A，另外一个事物B

下面，根据步骤，分析一下下面两个事物的执行流程:

第一步:

在上述两个事物当中，事物A首先开启了，然后执行一条查询的sql语句:也就是select...for update这样的语句。因为记录的最大值为1004，1007不在这一个范围当中。此时，事物A对于表当中no范围为(1004,+∞)的no索引加上了一把锁间隙锁

第二步:

事物B开启了，因为no值为1008的记录，不在范围(1004,+∞)的范围之内。因此，事物B也会加一个间隙锁，范围是(1004,+∞);由于间隙锁之间是互容的，因此事物B在执行select语句的时候，不会发生阻塞。

第三步:

事物A执行了一条插入的索引为1007的数值。但是，由于事物B对于事物A插入的范围加上了间隙锁，因此事物A一定要等待到事物B释放锁，才可以继续执行

第四步:

事物B执行了一条插入的索引值为1008的sql语句。但是，由于事物A对于(1004,+∞)的范围加锁了。因此，事物B一定需要等待到事物A释放锁，才可以继续执行。

可以看到，此时，两个事物互相阻塞了。

三、Insert语句怎样加锁的

Insert语句在正常执行的时候，是不会生成锁结构的，它是靠聚簇索引自带的一个被称为trx_id的字段来作为隐式锁来保护记录的。

隐式锁&显示锁

在Insert的过程当中不加锁，只有在特殊的情况下面，才会把隐式锁转化为显示锁，也就是真正加锁的过程。

举两个例子来说明隐式锁转换为显示锁的场景:

①范围(a,b)内加有间隙锁，当有一条记录在范围(a,b)之内插入记录的时候，就会转化为显示锁。

②如果insert语句插入的记录和已有的记录之间出现了主键，也无法插入。

记录之间加有间隙锁

还是上面这个表:

id（主键索引)	no(非主键索引)	name
1	1001	小明
2	1002	小李
3	1003	小华
4	1004	小黄

此时，这一张表当中，假如有一条语句，执行:

select * from t_order where order_no = 1006 for update;

此时，事物如果插入一条语句,insert....values(1007...)，这个时候，由于插入的数据正好在前一个sql语句插入的范围之内，因此会被阻塞。

遇到唯一键冲突或者主键冲突的时候加锁

如果主键索引重复：

当隔离级别为读已提交的时候，对这一条记录的主键索引加S型记录锁;

当隔离级别为可重复读的时候，插入新记录的事物会给已存在的

如果唯一二级索引重复:

不论是哪个隔离级别，插入新记录的事务都会给已存在的二级索引列值重复的二级索引记录添加 S 型 next-key 锁。

四、如何避免MySQL当中的死锁现象

方案一、保持一致的访问顺序

这是最重要的一条。在所有业务代码中，对多个资源（表、行）的访问和修改顺序必须保持一致。

比如：规定修改一组资源的时候，都是从小到大开始修改。

反面教材： 一个地方先更新订单表再更新库存表，另一个地方先更新库存表再更新订单表。
最佳实践： 统一规定，所有业务都必须先更新订单表，再更新库存表。

方案二、设置任务超时等待时间

当在一个任务的等待时间超过了这个时间之后，就进行回滚;

在 InnoDB 中，参数 innodb_lock_wait_timeout 是用来设置超时时间的，默认值时 50 秒。

方案三、主动开启死锁检测

将参数 innodb_deadlock_detect 设置为 on。

当innodb检测发现死锁之后，就会进行回滚死锁的事物。

方案四、为查询创建合适的索引

示例： UPDATE table WHERE name = ‘Alice’;
如果 name 字段上没有索引，这条语句会进行全表扫描，从而给所有记录（甚至间隙）加上锁，极易引发死锁。而加上索引后，InnoDB只需在 name 索引上对 ‘Alice’ 加锁，锁的范围大大缩小。

方案五、对SELECT操作显式加锁（慎用）

如果你确定之后要更新数据，可以在查询时直接使用 SELECT ... FOR UPDATE 或 SELECT ... LOCK IN SHARE MODE 来提前获取锁，避免在后续更新时才尝试加锁，从而减少锁持有的总时间。但这需要谨慎使用，因为它本身也会增加锁竞争。

方案六、减小事务的粒度

对于一个事务，减小他所绑定的事务的粒度，这样可以避免他跟其他的事务并行执行的可能性，从而减少锁冲突发生的概率

五、经典面试问题：两个人同时转账如何避免发生死锁

我们用一个具体场景来分析：用户A向用户B转账100元，同时用户B也向用户A转账50元。这两个操作并发执行。

会产生死锁的典型错误做法：

事务1 (A -> B)：先锁住A的账户，然后尝试锁住B的账户。
事务2 (B -> A)：先锁住B的账户，然后尝试锁住A的账户。
可能发生的情况：
- 时刻1：事务1锁住了A。
- 时刻2：事务2锁住了B。
- 时刻3：事务1请求锁B（已被事务2占用），等待...
- 时刻4：事务2请求锁A（已被事务1占用），等待...
- 结果：互相等待，形成死锁。

避免死锁的解决方案

核心思想就是破坏死锁的四个条件。对于转账这个场景，最常用、最有效的方法是：

破坏“循环等待”条件 —— 通过定义资源的锁顺序

解决方案：对资源（账户）进行排序，规定所有事务都必须按照统一的顺序来申请锁。

在转账操作中，资源就是两个账户。我们可以制定一个固定的规则，例如：

按照账户ID的大小排序：总是先锁ID较小的账户，再锁ID较大的账户。

应用到这个例子：
假设A的ID是 2，B的ID是 1。

事务1 (A -> B)：规则要求先锁ID小的（B，ID=1），再锁ID大的（A，ID=2）。
- 但它想从A转出，给B转入。这看起来矛盾？
- 关键：无论谁转给谁，锁的顺序只由账户ID决定。
- 所以事务1的正确加锁顺序是：1. 锁B， 2. 锁A。
事务2 (B -> A)：同样，先锁ID小的（B，ID=1），再锁ID大的（A，ID=2）。
- 所以事务2的正确加锁顺序是：1. 锁B， 2. 锁A。

现在让我们看执行流程：

时刻1：事务1成功锁定了账户B（ID较小者）。
时刻2：事务2也想来锁账户B，但它发现已经被事务1锁定了，所以事务2必须等待。
时刻3：事务1成功锁定了账户A（ID较大者）。现在它持有了两把锁，可以安全地完成转账操作（检查余额、扣款、加款、提交事务）。
时刻4：事务1完成，释放了账户A和账户B的锁。
时刻5：事务2（正在等待）现在终于可以获取到账户B的锁了。然后它继续获取账户A的锁，执行转账，最后释放所有锁。

这样就完全避免了死锁的可能，因为所有事务都遵循相同的申请顺序，不可能出现“你等我、我等你”的循环局面。

GitCode 开源社区

旨在为数千万中国开发者提供一个无缝且高效的云端环境，以支持学习、使用和贡献开源项目。

更多推荐

沁言学术 vs Grammarly：中文学术写作与语料库本地化支持的表现剖析

Grammarly是全球写作工具，语料库以英文为主，支持基本中文检查；沁言学术是本土AI平台，语料库深度本地化，针对中文学术设计。中文学术写作：Grammarly基础语法/拼写（本地化弱），沁言学术AI生成/优化（深度支持）。语料库本地化：Grammarly通用库（英文主导），沁言学术本土库（CNKI等集成）。整体：Grammarly免费版通用，付费版高级；沁言学术免费版入门，AI付费优化。表现亮