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	缩小事务范围

Step 5: 检查配置

SHOW VARIABLES LIKE 'innodb_print_all_deadlocks';
SHOW VARIABLES LIKE 'tx_isolation';
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';
SHOW VARIABLES LIKE 'innodb_autoinc_lock_mode';

输出模板

MySQL Deadlock Analysis Report
════════════════════════════════════════
Instance:       {host}:{port}
Analysis Time:  {timestamp}
MySQL Version:  {version}

Deadlock Status
  Total Deadlocks:   {deadlock_count}
  Row Lock Waits:    {lock_waits_count}
  Avg Lock Wait:     {avg_lock_wait_ms}ms

Latest Deadlock (from InnoDB Status)
────────────────────────────────────────
  Transaction A (ID: {trx_a_id})
    Status:   {status_a}   Duration: {duration_a}s
    Holds:    {holds_a}
    Waits:    {waits_a}
    SQL:      {sql_a}

  Transaction B (ID: {trx_b_id})
    Status:   {status_b}   Duration: {duration_b}s
    Holds:    {holds_b}
    Waits:    {waits_b}
    SQL:      {sql_b}

  Pattern: {AB-BA / Gap Lock / FK Cascade / Auto-Inc / Lock Upgrade}

Current Lock Waits
  {blocking_trx} → {waiting_trx} | {wait_query}

Long Transactions ({long_trx_count} found)
  | Trx ID  | Duration | State    | SQL          |
  |---------|----------|----------|--------------|
  | {trx_id}| {dur}s   | {state}  | {sql_snippet}|

Root Cause
  {root_cause_analysis}

Recommendations
  1. [SQL]    {sql_level_fix}
  2. [Code]   {code_level_fix}
  3. [Config] {config_level_fix}

告警阈值

适用于 Grafana / Prometheus 监控集成：

指标	Warning	Critical
Deadlocks/min	> 1	> 5
Row lock waits/min	> 100	> 500
Avg lock wait	> 100ms	> 500ms
Long transactions (>60s)	> 3	> 10

推荐输出格式

执行完毕后按以下结构输出：

状态：✅ 成功 / ⚠️ 部分成功 / ❌ 失败

<结论/证据/建议>	<值/状态>	说明

下一步：<可执行动作> MySQL Deadlock Analysis Report ════════════════════════════════════════ Instance: db-prod-01:3306 Analysis Time: 2026-07-08 23:30:00 MySQL Version: 8.0.35

Deadlock Status Total Deadlocks: 12 Row Lock Waits: 847 Avg Lock Wait: 42ms

Latest Deadlock: Pattern = AB-BA Transaction A: UPDATE orders SET status='paid' WHERE id=1001 Transaction B: UPDATE inventory SET qty=qty-1 WHERE product_id=55

Root Cause: 两个事务以不同顺序访问 orders 和 inventory 表。

Recommendations

1. [SQL] ALTER TABLE inventory ADD INDEX idx_product_id (product_id)
2. [Code] 统一事务内访问顺序：先 orders 后 inventory
3. [Config] SET GLOBAL innodb_print_all_deadlocks = ON

## 专题引用

无外部 references——本技能为自包含诊断 Runbook。
如需 MySQL 数据源连接管理，联动 `infra-datasource-ops`。