MySQL死锁排查实战：SHOW ENGINE INNODB STATUS输出解读与索引优化

死锁是MySQL高并发写入场景的常见故障：两个事务互相持有对方需要的锁，谁都无法继续，InnoDB检测到环后回滚代价较小的一方，应用层收到Deadlock found when trying to get lock异常。线上偶发死锁不需要惊慌，但死锁频率上升说明存在锁设计问题。排查死锁的核心工具是SHOW ENGINE INNODB STATUS，读懂它的LATEST DETECTED DEADLOCK段是定位问题的关键。

复现死锁与LATEST DETECTED DEADLOCK段解读

用两个会话构造一个典型死锁。先建测试表并插入两条记录：

CREATE TABLE account (
  id BIGINT PRIMARY KEY,
  balance INT NOT NULL DEFAULT 0
) ENGINE=InnoDB;
INSERT INTO account VALUES (1, 1000), (2, 1000);

会话A先更新id=1，会话B更新id=2，然后A再更新id=2、B再更新id=1，交叉持锁形成环：

-- 会话A                          -- 会话B
BEGIN;
UPDATE account SET balance=balance-100 WHERE id=1;
                                  BEGIN;
                                  UPDATE account SET balance=balance-100 WHERE id=2;
UPDATE account SET balance=balance-100 WHERE id=2;
-- A阻塞，等B释放id=2的行锁
                                  UPDATE account SET balance=balance-100 WHERE id=1;
-- ERROR 1213: Deadlock found

B的事务被回滚，A继续执行。此时执行SHOW ENGINE INNODB STATUS，找LATEST DETECTED DEADLOCK段：

*** (1) TRANSACTION:
TRANSACTION 12345, ACTIVE 5 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 8, query id 100 updating
UPDATE account SET balance=balance-100 WHERE id=2
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 58 page no 4 n bits 72 index PRIMARY of table `test`.`account`
trx id 12345 lock_mode X locks rec but not gap waiting
*** (2) TRANSACTION:
TRANSACTION 12346, ACTIVE 3 sec starting index read
MySQL thread id 9, query id 101 updating
UPDATE account SET balance=balance-100 WHERE id=1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS ... index PRIMARY ... trx id 12346 lock_mode X
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS ... index PRIMARY ... trx id 12346 lock_mode X locks rec but not gap waiting
*** WE ROLL BACK TRANSACTION (2)

解读要点：两个TRANSACTION段分别是被回滚方与幸存方，WAITING FOR THIS LOCK TO BE GRANTED后面跟的SQL就是各自卡住的语句；lock_mode X locks rec but not gap表示排他记录锁；HOLDS THE LOCK(S)列出一方已持有的锁，与另一方的等待请求对照，锁环就浮出来了。把这个段里的两条SQL拿到业务代码里反查，定位到并发执行路径。

间隙锁与索引缺失引发的批量死锁案例分析

真实生产中比交叉更新更常见的是二级索引上的死锁，典型诱因是UPDATE的过滤列没有索引。没有索引时UPDATE会锁全表扫描路径上的所有行，并发下大量无关行被牵连进锁竞争。看案例：

CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  order_no VARCHAR(32) NOT NULL,
  status TINYINT NOT NULL,
  INDEX idx_status (status)
);
-- 事务1：按order_no更新（order_no无索引）
UPDATE orders SET status=2 WHERE order_no='ORD20260911A';
-- 事务2：按status更新（有索引）
UPDATE orders SET status=3 WHERE status=1 ORDER BY id LIMIT 10;

事务1走全表扫描，给扫过的每一行加锁，包括事务2已锁定的行；事务2按status索引扫描加锁，路径与事务1交叉，随机出现死锁。修复方式直接且有效：给order_no加索引，让事务1只锁目标行。

ALTER TABLE orders ADD UNIQUE INDEX uk_order_no (order_no);
-- 索引后事务1的锁范围从全表缩减到单行，死锁消失

RR隔离级别下的间隙锁是另一个死锁高发点。范围更新语句如UPDATE … WHERE status BETWEEN 1 AND 3会锁定二级索引的间隙，两个事务以不同顺序获取相邻间隙锁时死锁。间隙锁在RC隔离级别下关闭（唯一索引等值查询除外），如果业务能接受幻读（绝大多数互联网业务可以，因为依赖显式锁与状态机而非依赖隔离级别防幻读），把核心写库调到READ COMMITTED能直接消除一批间隙锁死锁：

-- 仅当前会话生效，用于灰度验证
SET SESSION transaction_isolation = 'READ-COMMITTED';

死锁预防的工程规范与监控采集配置

死锁排查靠事后分析，预防靠写代码时的规范。三条经过验证的实践：第一，同一事务内按固定顺序访问多行，比如统一按主键升序处理批量更新，锁获取顺序一致就不会成环，批量扣库存的常见写法是先ORDER BY id再循环更新；第二，事务尽量小，把RPC调用、耗时计算挪出事务，锁持有时间从秒级降到毫秒级，交叉概率指数级下降；第三，更新语句的条件列必须命中索引，用EXPLAIN确认type至少是range而非ALL。

监控层面，死锁次数在performance_schema有现成统计，采集到Prometheus的告警配置：

-- MySQL侧查询当前死锁累计数
SELECT * FROM performance_schema.global_status
WHERE VARIABLE_NAME = 'Innodb_deadlocks';

-- Prometheus告警规则：死锁增长速率异常
- alert: MySQLDeadlockRateHigh
  expr: rate(mysql_global_status_innodb_deadlocks[5m]) > 0.1
  for: 10m
  labels:
    severity: warning

再配合pt-deadlock-logger把死锁日志持久化到表里，保留完整的历史死锁上下文，比INNODB STATUS只保留最近一次的能力更适合高频死锁的追溯分析。应用侧对死锁异常要有重试逻辑，捕获1213错误后随机退避100到300毫秒重试，死锁被InnoDB自动检测回滚的代价远小于长事务阻塞。