MySQL死锁的排查与解决：show engine innodb status分析

数据库出现死锁会导致业务报错，响应超时。快速定位并解决死锁是保障系统稳定性的关键能力。本文将演示如何使用 show engine innodb status 命令快速分析死锁根源并修复。

获取死锁日志

执行 命令查看 InnoDB 引擎状态。

1. 登录 MySQL 数据库服务器或客户端。

2. 输入 以下命令并回车：

   show engine innodb status\G

   注意结尾使用 \G 而非分号，这能让输出格式更易于阅读。

3. 查找 输出结果中的 LATEST DETECTED DEADLOCK 部分。

   • 如果看到类似 *** (1) TRANSACTION: 的内容，说明死锁信息被捕捉到了。
   • 如果该部分显示 no deadlock 或为空，说明当前内存中没有死锁记录（死锁日志是循环覆盖的，重启或新的死锁会覆盖旧的）。此时需开启 innodb_print_all_deadlocks 参数以便下次捕获。

解读死锁日志结构

死锁日志主要描述了两个（或多个）事务互相等待对方持有的锁。日志中包含以下关键信息：

• TRANSACTION：事务 ID 以及该事务正在执行的 SQL 语句。
• HOLDS THE LOCK(S)：该事务当前持有哪些锁。
• WAITING FOR THIS LOCK TO BE GRANTED：该事务正在等待哪个锁。
• WE ROLL BACK TRANSACTION (x)：InnoDB 判定回滚哪个事务（通常是代价较小的事务）。

分析经典死锁场景

假设日志中显示以下关键片段：

*** (1) TRANSACTION:
TRANSACTION 1234, ACTIVE 10 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 10, OS thread handle 123, query id 333 localhost root updating
update user set balance = balance - 100 where id = 1

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1 page no 3 n bits 72 index PRIMARY of table `test`.`user` ...

*** (2) TRANSACTION:
TRANSACTION 1235, ACTIVE 12 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 11, OS thread handle 124, query id 334 localhost root updating
update user set balance = balance + 100 where id = 2

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1 page no 3 n bits 72 index PRIMARY of table `test`.`user` ...

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1 page no 3 n bits 72 index PRIMARY of table `test`.`user` ...

梳理 上述逻辑关系，死锁形成过程如下：

分析可得：

1. 事务 1 持有 id=1 的锁，试图修改 id=2。
2. 事务 2 持有 id=2 的锁，试图修改 id=1。
3. 双方互不相让，形成闭环，死锁产生。

实施解决方案

针对不同成因的死锁，采取以下对应的修复措施。

1. 统一加锁顺序（解决循环等待）

修改 业务代码，确保所有事务必须按照相同的顺序（例如按 ID 升序）去操作数据。

• 错误逻辑：线程 A 更新 1->2，线程 B 更新 2->1。
• 正确逻辑：
  • 线程 A 先更新 1，再更新 2。
  • 线程 B 先更新 1，再更新 2。

应用 此逻辑后，事务 2 会先尝试获取 1 的锁，因为被事务 1 占用而进入等待状态，事务 1 随后顺利获取 2 的锁并提交，事务 1 释放锁后事务 2 即可继续执行，消除了死锁。

2. 优化索引消除间隙锁（解决锁范围过大）

如果日志中显示锁类型为 Gap lock 或 Next-key lock，说明当前索引未能精确命中，导致数据库锁住了一片范围而非单行。

1. 检查 语句的 WHERE 条件字段。

2. 执行 EXPLAIN 命令查看执行计划：

   EXPLAIN select * from user where name = 'Alice';

3. 观察 输出中的 type 和 key 列。如果 type 为 range 或 ALL，且 key 为 NULL，说明索引失效。

4. 添加 合适的索引（例如在 name 字段上建索引），将锁的粒度从“范围”缩小到“单行记录”，从而大幅降低死锁概率。

3. 缩短事务持有锁的时间

排查 业务代码中是否存在长事务。

1. 避免 在数据库事务中进行网络请求（如调用第三方 API）、复杂的数学计算或文件读写操作。
2. 将 耗时的非数据库操作移到事务外部。
3. 确保 事务中仅包含必要的数据库读写语句，快进快出。

4. 添加乐观锁（解决高并发更新）

对于并发极高的热点行更新，可考虑使用乐观锁机制。

1. 在 表中增加 version 字段。

2. 修改 更新语句逻辑：

   update user set balance = 100, version = version + 1 where id = 1 and version = old_version;

3. 检查 影响行数。如果返回 0，说明数据已被修改，重试 整个业务逻辑或提示用户重试，而不是让数据库直接加锁互斥。