MySQL 死锁怎么用 show engine innodb status 排查和避免？

Question

仓乃文 · Accepted Answer

MySQL 死锁是两个或多个事务互相持有对方需要的锁，形成循环等待。InnoDB 检测到死锁后会自动回滚代价最小的事务，抛出 Deadlock found when trying to get lock 异常。生产环境死锁极难复现，必须通过日志排查。

排查步骤：
```sql
-- 1. 查看最新死锁详情（最核心）
SHOW ENGINE INNODB STATUS;
-- 重点关注 LATEST DETECTED DEADLOCK 段：
-- - TRANSACTION：死锁事务 ID、持有的锁、等待的锁
-- - HOLDS THE LOCK：事务当前持有的锁资源
-- - WAITING FOR THIS LOCK：事务等待的锁资源
-- - SQL statement：触发死锁的具体 SQL

-- 2. 开启死锁日志持久化（默认仅存内存，重启丢失）
SET GLOBAL innodb_print_all_deadlocks = 1;
-- 写入 my.cnf 永久生效：
-- innodb_print_all_deadlocks = 1

-- 3. 查看当前事务和锁持有情况
SELECT * FROM information_schema.innodb_trx;
-- MySQL 8.0 用 performance_schema
SELECT * FROM performance_schema.data_locks;
SELECT * FROM performance_schema.data_lock_waits;
```

常见死锁场景与修复：
```sql
-- 场景 1：加锁顺序不一致（最常见，占 48.7%）
-- 事务 A：先锁 id=1，再锁 id=2
-- 事务 B：先锁 id=2，再锁 id=1 → 死锁！
-- 修复：统一加锁顺序，所有事务按 id 升序更新

-- 场景 2：间隙锁导致的死锁（最隐蔽）
-- RR 隔离级别下，SELECT ... FOR UPDATE 会加间隙锁
-- 两个事务在不同间隙插入，互相等待对方的间隙锁
-- 修复：改用 RC 隔离级别（无间隙锁）或先插入再更新

-- 场景 3：批量更新未命中索引
-- UPDATE 无索引 → 行锁升级为表锁 → 多事务争抢表锁
-- 修复：确保 WHERE 条件命中索引
```

预防措施：
```sql
-- 1. 统一加锁顺序
-- 2. 缩短事务，尽快提交
-- 3. 确保更新/删除走索引
-- 4. 降低隔离级别为 RC（减少间隙锁）
-- 5. 应用层捕获死锁异常后自动重试
```

补充说明：
- 死锁日志中 lock_mode X locks rec but not gap 表示记录锁
- lock_mode X locks gap before rec 表示间隙锁
- lock_mode X 表示临键锁（Next-Key Lock）
- 规则：生产环境务必开启 innodb_print_all_deadlocks，否则重启后死锁信息丢失