MySQL 锁监控与死锁分析手册

如何系统化监控行锁/间隙锁与快速定位死锁？本文提供可复现脚本、标准化视图与操作手册。

1. 死锁复现（RR 下 Next-Key）

CREATE TABLE t_lock(
  id INT PRIMARY KEY,
  k  INT,
  KEY idx_k(k)
) ENGINE=InnoDB;
INSERT INTO t_lock VALUES (1,10),(2,20),(3,30);

会话A：

SET tx_isolation='REPEATABLE-READ'; START TRANSACTION;
SELECT * FROM t_lock WHERE k BETWEEN 10 AND 30 FOR UPDATE; -- 锁住[10,30]

会话B：

SET tx_isolation='REPEATABLE-READ'; START TRANSACTION;
UPDATE t_lock SET k=11 WHERE id=1; -- 等待A释放
-- 回到会话A：
UPDATE t_lock SET k=21 WHERE id=2; -- 互等 -> 死锁

2. 死锁日志解读

SHOW ENGINE INNODB STATUS\G

关注：

LATEST DETECTED DEADLOCK 中 WE ROLL BACK TRANSACTION 指出被回滚事务；
lock_mode X locks rec but not gap / locks gap before rec 定位间隙锁；
index idx_k of table 对应的索引与范围。

3. 在线监控

8.0：performance_schema.data_locks / data_lock_waits 关联 threads 得到阻塞链；
5.7：information_schema.innodb_locks / lock_waits（较旧）。

4. 自动化抓取脚本（示意）

SELECT l.*, w.*
FROM performance_schema.data_lock_waits w
JOIN performance_schema.data_locks l
  ON w.BLOCKING_ENGINE_LOCK_ID = l.ENGINE_LOCK_ID;

周期抓取 + 告警（阻塞>5s）。

5. 规避策略

精确索引，避免大范围扫描引发大量 gap 锁；
将写入拆分为更细粒度范围；
业务侧重试+幂等；
读写分离，长事务落只读副本。

6. 死锁成因全景（结合 InnoDB 锁模型）

死锁的四个必要条件：互斥使用、占有并等待、不可剥夺、循环等待。InnoDB 在行级与间隙级锁语义下，以下模式最易触发循环等待：

不一致的访问顺序：
- 事务A更新 A→B，事务B更新 B→A；或一个通过二级索引回表更新，另一个直接按主键更新，锁顺序不同。
范围更新引入 Next-Key/Gap Lock：
- 在 RR 下，SELECT ... FOR UPDATE/UPDATE/DELETE 会对命中记录及其间隙加锁，多个事务在相邻范围内插入/更新易互等。
唯一键检查 + 插入意向锁：
- 插入唯一键前需要在唯一索引区间做 Next-Key 检查；与并发范围更新/去重查询交错形成环路。
二级索引与回表：
- 一方以二级索引扫描并回表，另一方以主键路径进入，组合出的锁申请序列不一致。
外键检查：
- 父/子表在不同顺序读写，外键检查需要额外 S 锁/间隙锁，改变锁顺序。
自增锁与热点：
- 大并发插入时，旧模式下 AUTO-INC 锁可能放大等待链；热点值导致索引页竞争与页分裂叠加。
缺索引与大事务：
- 无索引范围扫描扩大锁集合；慢 SQL/长事务持锁时间长，死锁概率上升。

典型两会话交错示意：

-- 场景1：访问顺序不一致
-- 会话A
START TRANSACTION;
UPDATE t SET v=v+1 WHERE id=1; -- 锁 id=1
UPDATE t SET v=v+1 WHERE id=2; -- 等待会话B释放

-- 会话B
START TRANSACTION;
UPDATE t SET v=v+1 WHERE id=2; -- 锁 id=2
UPDATE t SET v=v+1 WHERE id=1; -- 等待会话A释放 -> 环

-- 场景2：RR 下范围更新 + 插入唯一键
-- 会话A
START TRANSACTION;
UPDATE t SET status=1 WHERE idx BETWEEN 10 AND 20; -- Next-Key 锁

-- 会话B
START TRANSACTION;
INSERT INTO t(uq, idx, ...) VALUES('X', 15, ...); -- 唯一检查 + 插入意向
-- 两边在唯一索引/二级索引与主键上的锁互等

7. 事务隔离级别与锁行为（InnoDB 语义）

Read Uncommitted（读未提交）：几乎不用；当前读仍会加锁，允许脏读。
Read Committed（读已提交）：一致性读不加锁；大多数场景不使用 Gap Lock（外键/唯一检查仍可能使用），死锁概率低于 RR。
Repeatable Read（可重复读，默认）：一致性读用 MVCC；当前读采用 Next-Key Lock 防幻读，范围操作更易死锁。
Serializable（可串行化）：读也加锁，吞吐大降，除非强隔离需求。

要点：

普通 SELECT（快照读）不加行锁；FOR UPDATE/LOCK IN SHARE MODE/UPDATE/DELETE 属于当前读会加锁。
RC 相比 RR 减少间隙锁；RR 下 Next-Key 是默认策略。
参数参考：innodb_autoinc_lock_mode=2 降低自增锁争用；innodb_deadlock_detect 控制检测器；innodb_lock_wait_timeout 控制等待上限。

8. InnoDB 内部死锁处理机制（源码视角）

InnoDB 锁与事务核心位于 storage/innobase：

锁子系统（lock_sys）：表锁 LOCK_TABLE、记录锁 LOCK_REC；模式 S/IS、X/IX、AUTO-INC、INSERT INTENTION；记录锁细分 Record/GAP/Next-Key。
事务结构（trx_t）：持有锁集合、等待中的锁 trx->lock.wait_lock、undo 信息。
锁入队：lock_rec_add_to_queue() / lock_table_add_to_queue() 将请求加入等待队列。
死锁检测：等待入队时，基于等待图进行环检测（lock0lock.cc 等）。从当前等待事务出发，遍历“阻塞我的锁→持锁事务→该事务正在等待的锁”，若回到起点即形成环。
牺牲者选择：按代价（已修改行数/undo 量/事务年龄/持有锁数量等）选择最小代价事务回滚，向其返回 1213，其余唤醒继续执行。
超时路径：未开检测器或无环，等待至 innodb_lock_wait_timeout 抛 1205。

伪代码抽象：

on_lock_wait(trx, lock_req):
  if deadlock_detect_on and detect_cycle_from(trx):
    victim = choose_lowest_cost_trx()
    rollback(victim)
    wake_up_waiters()
  else:
    wait_until_granted_or_timeout()

诊断入口：SHOW ENGINE INNODB STATUS\G 会输出最近一次死锁的等待链；设置 innodb_print_all_deadlocks=ON 可将所有死锁写入错误日志（生产谨慎）。

9. 系统化诊断步骤（生产可用）

1) 快速取证：

执行 SHOW ENGINE INNODB STATUS\G，定位 LATEST DETECTED DEADLOCK。
抓取两个（或以上）事务的 SQL、索引名、锁模式（locks rec but not gap/locks gap before rec）。

2) 在线视图：

SELECT * FROM performance_schema.data_lock_waits; -- 等待边
SELECT * FROM performance_schema.data_locks;      -- 锁明细
SELECT * FROM sys.schema_lock_waits LIMIT 50;     -- 友好视图

3) 执行计划核验：

对死锁 SQL 逐条 EXPLAIN，确认是否走了不同索引、是否回表、是否大范围扫描。

4) 事务画像：

检查是否存在跨请求长事务、事务内外部 IO、批量扫描；确认提交点是否靠后。

5) 复现与回归：

用最小化数据集在两个会话复现；将复现脚本纳入回归库。

10. 工程实践：如何避免死锁

统一访问顺序（首要）：
- 规定跨表/跨索引的访问次序（如按主键升序 A→B→C），避免交叉顺序。
拆小事务，尽早提交：
- 批量更新分批提交；把只读校验前置；缩短持锁窗口。
索引到位，路径一致：
- where 条件命中索引，尽量落到 Record Lock；必要时 FORCE INDEX 统一路径，减少“二级索引回表 vs 主键直达”的差异。
隔离级别策略：
- OLTP 优先 RC（若业务允许），RR 场景要避免范围更新；或将“读→算→改”改为“精确定位→改”。
善用 8.0 特性：
- FOR UPDATE SKIP LOCKED 跳过已锁行避免互等；NOWAIT 立即失败，应用快速重试。
热点打散与自增锁：
- 设计上分片热点键；配置 innodb_autoinc_lock_mode=2 减少 AUTO-INC 锁争用。
事务内禁止外部 IO：
- 避免 RPC/磁盘慢操作扩大持锁时间。
失败重试与幂等：
- 捕获 1213/1205 指数退避重试；以唯一键/业务幂等键确保多次提交安全。
代码层策略化：
- 收敛到统一 DAO/仓储层，内置锁序策略与“精确更新”模板；CR 强制检查。
监控预警与演练：
- 建立锁等待指标阈值；压测期开启 innodb_print_all_deadlocks，以现场日志反推 SQL 与索引设计。

11. 可复现实战案例

案例A：二级索引范围更新 vs 主键更新

CREATE TABLE t_a(
  id BIGINT PRIMARY KEY,
  k  INT,
  KEY idx_k(k)
) ENGINE=InnoDB;
INSERT INTO t_a VALUES (1,10),(2,20),(3,30);

-- 会话A（走二级索引）
START TRANSACTION;
UPDATE t_a SET k=k+1 WHERE k BETWEEN 10 AND 30; -- Next-Key + 回表

-- 会话B（走主键）
START TRANSACTION;
UPDATE t_a SET k=25 WHERE id=2; -- Record Lock
-- 两者在主键回表记录与二级索引范围上互相等待

修复建议：

先用覆盖索引挑出主键集合，再按主键升序逐条精准 UPDATE；或在 RC 下改造范围操作；或对 B 强制走相同索引路径；或用 SKIP LOCKED 处理任务类更新。

案例B：唯一键插入与范围更新

CREATE TABLE t_b(
  id BIGINT PRIMARY KEY,
  uq VARCHAR(32) UNIQUE,
  k  INT,
  KEY idx_k(k)
) ENGINE=InnoDB;

-- 会话A
START TRANSACTION;
UPDATE t_b SET k=k+1 WHERE k BETWEEN 100 AND 200; -- Next-Key

-- 会话B
START TRANSACTION;
INSERT INTO t_b(uq, k) VALUES('U-1', 150); -- 唯一检查 + 插入意向