diag-mysql-deadlock

MySQL Deadlock Full-Chain Diagnostics

用途

当用户报告 MySQL 死锁或怀疑锁竞争时触发。覆盖：

• 应用日志出现 Error: 1213 Deadlock found
• 监控发现 Innodb_deadlocks 计数器持续增长
• 高峰期锁竞争排查
• Schema 变更后可能影响锁行为

本技能是自包含诊断 Runbook——所有 SQL、模式分类、输出模板、告警阈值都在本文件内，不依赖外部 references。

输入

• MySQL 连接信息（host / port / user / password）
• 可选：应用日志中的死锁片段
• 可选：已知的表结构和索引信息

输出

• 结构化死锁分析报告（填充输出模板）
• 根因判定 + 修复建议（SQL / 代码 / 配置三级）

前置条件

项	要求
MySQL 权限	PROCESS（查看线程和 InnoDB 状态）
版本	MySQL 5.6+（5.7+ 使用 performance_schema，5.6 回退 information_schema）

诊断步骤

Step 1: 捕获死锁计数与最近状态

-- 死锁计数与锁等待指标
SHOW GLOBAL STATUS LIKE 'Innodb_deadlocks';
SHOW GLOBAL STATUS LIKE 'Innodb_row_lock_waits';
SHOW GLOBAL STATUS LIKE 'Innodb_row_lock_time_avg';

-- 完整 InnoDB 状态（包含 LATEST DETECTED DEADLOCK 区块）
SHOW ENGINE INNODB STATUS;

Step 2: 检查当前锁等待（MySQL 5.7+）

-- 谁阻塞了谁
SELECT
  waiting_trx.trx_id AS waiting_trx,
  blocking_trx.trx_id AS blocking_trx,
  waiting_trx.trx_state AS wait_state,
  waiting_trx.trx_started AS wait_started,
  LEFT(waiting_trx.trx_query, 200) AS wait_query,
  LEFT(blocking_trx.trx_query, 200) AS block_query
FROM performance_schema.data_lock_waits w
JOIN information_schema.innodb_trx waiting_trx ON w.requesting_engine_transaction_id = waiting_trx.trx_id
JOIN information_schema.innodb_trx blocking_trx ON w.blocking_engine_transaction_id = blocking_trx.trx_id;

-- MySQL 5.6 回退:
SELECT * FROM information_schema.innodb_trx ORDER BY trx_started;

Step 3: 查找长事务

-- 运行超过 60 秒的事务
SELECT
  trx_id, trx_state, trx_started,
  TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS duration_sec,
  trx_tables_locked, trx_rows_locked,
  LEFT(trx_query, 200) AS current_query
FROM information_schema.innodb_trx
WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 60
ORDER BY trx_started;

Step 4: 判定死锁模式

从 SHOW ENGINE INNODB STATUS 的 LATEST DETECTED DEADLOCK 区块提取并分类：

模式	特征	典型修复
AB-BA	事务 A 锁行1→行2，B 锁行2→行1	统一访问顺序
Gap Lock	Next-key lock 在索引间隙上	缩小扫描范围、使用 RC 隔离级别
FK Cascade	外键触发级联锁	在 FK 列上加索引
Auto-Inc	并发 insert 竞争自增锁	innodb_autoinc_lock_mode=2
Lock Upgrade	S-lock 升级为 X-lock	缩小事务范围