diag-mysql-deadlock

MySQL 死锁全链诊断。捕获 InnoDB 死锁状态,解析锁链(谁持有什么、谁等待什么), 分类死锁模式(AB-BA / Gap Lock / FK cascade / Auto-Inc),查找当前锁等待和长事务, 输出结构化报告并给出修复建议。支持 MySQL 5.7+ performance_schema 与 5.6 兼容回退。

SKILL.md

GitHub

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`。