MySQL 8.0 InnoDB 锁体系实战手册（行锁/间隙锁/意向锁/MDL）+ 5 个典型死锁场景复现脚本 + 排查 Checklist

背景

我在生产环境反复遇到 InnoDB 锁相关故障：间隙锁导致的范围 UPDATE 阻塞、MDL 锁与 DDL 互斥、意向排他锁让批量 DML 卡死、跨会话死锁回滚后业务报 1205。每次排查都要翻文档、对日志、跑 SQL，缺少一份「拿来就能用」的排查手册。

最终要什么

一份 可直接用于生产排障的 InnoDB 锁排查实战手册，包含可复现的死锁场景脚本和一键诊断 Bash 脚本。目标读者：中高级 DBA 和遇到锁问题的后端开发。

输入与上下文

• 版本：MySQL 8.0.36+（Docker 镜像 mysql:8.0 即可）
• 字符集：utf8mb4，引擎 InnoDB
• 隔离级别：默认 REPEATABLE READ（需单独标注 SERIALIZABLE 下的差异）
• 诊断数据源：information_schema.INNODB_TRX、performance_schema.events_statements_current、SHOW ENGINE INNODB STATUS（LATEST DETECTED DEADLOCK 段）、performance_schema.data_locks / data_lock_waits
• 不需要：不要求覆盖 MySQL 5.7（锁模型差异可在附录一句话带过）

交付格式

1. 手册正文（Markdown，≥ 3000 字）：

   • 第 1 章：InnoDB 四种锁（行锁 / 间隙锁 / 意向锁 / MDL）的工作原理与适用场景，每种配 ER 图或文本示意图
   • 第 2 章：5 个典型死锁场景，每个包含：
     • 场景描述（业务背景一句话）
     • 建表 + 灌数据 SQL（可直接执行）
     • 两个会话的触发 SQL（标注 Session A / B）
     • 预期死锁日志（从 SHOW ENGINE INNODB STATUS 截取的关键段）
     • 根因分析 + 规避建议
   • 第 3 章：排查 Checklist（≥ 8 条），每条含：症状 → 定位 SQL → 判定标准 → 处置动作
   • 第 4 章：附录——SERIALIZABLE 差异、innodb_deadlock_detect 开关取舍、lock_wait_timeout 推荐值

2. 一键诊断 Bash 脚本（单文件 innodb_lock_diag.sh）：

   • 入参：-h 主机 -P 端口 -u 用户 -p 密码（支持 MYSQL_PWD 环境变量）
   • 输出：当前活跃事务、锁等待链（data_lock_waits JOIN data_locks）、最近一次死锁日志、长事务 TOP 10
   • 需兼容 mysql 客户端 8.0+

3. 验收 Checklist 表（Markdown 表格）：

   • 5 个死锁场景 × 4 列（场景名 / 复现步骤 / 预期错误码 / 规避措施）
   • 排查 Checklist ≥ 8 行

备注

• 所有 SQL 脚本必须可直接在 docker run -d --name diag-mysql -e MYSQL_ROOT_PASSWORD=test mysql:8.0 启动的实例上执行
• 不要贴大段官方文档原文，用自己的话讲清原理 + 配可执行命令