阻塞、死锁与 MDL 实验

← 本站索引 ｜ 练习答案

可运行双会话 SQL

先准备两行：

INSERT INTO sales_order(customer_id, status, total_amount)
SELECT customer_id, 'pending', 10 FROM customer ORDER BY customer_id LIMIT 1;
INSERT INTO sales_order(customer_id, status, total_amount)
SELECT customer_id, 'pending', 20 FROM customer ORDER BY customer_id LIMIT 1;
SELECT order_id FROM sales_order ORDER BY order_id DESC LIMIT 2;

会话 A 先更新较小 id，再更新较大 id；会话 B 反向操作：

START TRANSACTION;
UPDATE sales_order SET version_no = version_no + 1 WHERE order_id = 1;
-- 等待 B 锁住 order_id=2
UPDATE sales_order SET version_no = version_no + 1 WHERE order_id = 2;

START TRANSACTION;
UPDATE sales_order SET version_no = version_no + 1 WHERE order_id = 2;
-- 等待 A 锁住 order_id=1
UPDATE sales_order SET version_no = version_no + 1 WHERE order_id = 1;

用实际存在的两个 id 替换示例值。

机制解释

InnoDB 检测等待图中的环并回滚一个事务。避免死锁的首要手段是统一访问顺序、缩短事务并使用合适索引，不是关闭检测。REPEATABLE READ 下范围当前读可能使用 next-key locks 防止幻影。

MDL 保护对象定义。一个长期未提交事务即使只读过表，也可能让后续 ALTER 等待；DDL 的 ALGORITHM 和 LOCK 请求不消除所有开始/提交阶段的阻塞。

真实边界

• 被回滚事务必须整体重试，且重试次数、退避和幂等有上限。
• kill 连接前要确认事务所有者和未提交影响。
• performance_schema 显示当前锁快照，排查还需事务、语句和应用 trace。
• 大表 DDL 需评估空间、复制延迟、日志增长、故障恢复和回滚路径。

练习

1. 捕获死锁后查看 SHOW ENGINE INNODB STATUS。
2. 统一两个会话的更新顺序并证明死锁消失。
3. 保持一个读事务不提交，再执行 ALTER TABLE，观察 MDL 等待。

验收清单

• 死锁证据包含事务、语句、索引、持有锁和等待锁。
• 统一访问顺序后重复实验不再形成同一等待环。
• MDL 实验能定位持有者并给出安全处置选择。

完成后核对答案

深度加餐：锁实验要同时看等待者和持有者

“卡住了”不是诊断；要知道谁持有什么锁、等待哪一把锁、事务多久没提交。

先做一个可观察实验

-- session A
START TRANSACTION;
UPDATE orders SET status = 'PAID' WHERE id = 1;

-- session B
START TRANSACTION;
UPDATE orders SET status = 'CANCELLED' WHERE id = 1;

SELECT waiting_pid, blocking_pid, waiting_query
FROM sys.innodb_lock_waits;

预期：会话 B 等待 A 的记录锁；sys.innodb_lock_waits 给出阻塞关系，提交/回滚 A 后 B 才能继续。

原理下潜与工程边界

记录锁、间隙锁、临键锁和元数据锁解决不同冲突；隔离级别、索引访问路径和外键检查都会影响锁范围。死锁是循环等待，InnoDB 会主动回滚一个受害者。

普通一致性读通常不等待记录锁；DDL 可能被 MDL 卡住；锁等待超时与死锁错误码不同，重试策略也应不同。

反例排错路线

先 SHOW ENGINE INNODB STATUS，再查 performance_schema.data_locks/data_lock_waits；记录事务开始时间、SQL、索引和受影响行数。

练习加码（仍不提供答案）

加码：构造两个事务相反顺序更新两行的死锁，记录错误日志，并改成统一排序后证明死锁消失。

验收时请补充：一份可复现的环境/数据前提、正常结果、失败结果以及可回滚或清理动作。

继续查阅：MySQL 8.4 参考手册（外部资料已收录于证据账本） · InnoDB 文档（外部资料已收录于证据账本）