📖 本篇简介：本篇是并发与锁的下篇，聚焦死锁、MDL 元数据锁、意向锁、自增锁、乐观锁等更"高级"的并发坑。上篇讲一条 SQL 锁哪些行，这篇讲多条事务怎么互相堵死、DDL 为什么被一条小查询堵住、死锁怎么复现和避免。示例基于 MySQL 8.0 InnoDB（本机无服务，死锁与 MDL 行为按官方文档说明）。读完你能定位死锁日志、避免线上锁事故。
目录
- 坑 1：两个事务加锁顺序相反导致死锁
- 坑 2：MDL 元数据锁被长查询堵死
- 坑 3：online DDL 被小事务堵住
- 坑 4：批量更新顺序不固定引发死锁
- 坑 5：自增锁 AUTO-INC 成表级瓶颈
- 坑 6：以为 InnoDB 有锁升级
- 坑 7：用 processlist 看锁信息不全
- 坑 8：乐观锁 version 字段忘了校验
- 坑 9：死锁日志没开事后查无对证
- 坑 10：以为意向锁会互相阻塞行锁
- 坑 11：REPEATABLE READ 下写后读不一致
- 速查表：死锁与高级锁速记
- 注意事项：避免死锁的 Checklist
- 自测：你能定位死锁吗
坑 1：两个事务加锁顺序相反导致死锁
场景 / 错误示范：
-- 事务 A：先锁 id=1，再锁 id=2
START TRANSACTION;
UPDATE account SET balance = balance - 10 WHERE id = 1; -- 锁住 id=1
UPDATE account SET balance = balance - 10 WHERE id = 2; -- 等 id=2 的锁
-- 事务 B：先锁 id=2，再锁 id=1
START TRANSACTION;
UPDATE account SET balance = balance - 10 WHERE id = 2; -- 锁住 id=2
UPDATE account SET balance = balance - 10 WHERE id = 1; -- 等 id=1 的锁
-- A 等 B 的 id=2，B 等 A 的 id=1，死锁！MySQL 回滚其中一个
为什么是坑：死锁的本质就是循环等待——两个事务以相反顺序申请同一组锁。只要并发事务的加锁顺序不一致，就可能互相等。转账、批量扣款这类多步更新业务，最容易踩。
正确做法：
-- 铁律：所有事务按相同顺序加锁。比如永远先锁 id 小的
-- 转账：先给金额转出方排序，再按 id 从小到大依次加锁
START TRANSACTION;
UPDATE account SET balance = balance - 10 WHERE id = LEAST(1, 2); -- 先锁小 id
UPDATE account SET balance = balance - 10 WHERE id = GREATEST(1, 2);
COMMIT;
-- 两个方向的转账都按同一顺序，就不会循环等待
加分点：统一加锁顺序是避免死锁最有效的手段。业务上多个资源要锁时，先排序再加锁，成本几乎为零。
坑 2：MDL 元数据锁被长查询堵死
场景 / 错误示范：
-- 一张热表上有个跑了 30 秒的长 SELECT
SELECT * FROM big_table JOIN ...; -- 持有 MDL 读锁跑了很久
-- 此时开发者执行 ALTER TABLE big_table ADD COLUMN ...;
-- ALTER 要拿 MDL 写锁，排在长查询后面
-- 而新进来的普通 SELECT 又排在 ALTER 后面，全被堵住，库瞬间打满
为什么是坑：MySQL 对表的任何访问都会先加 MDL（元数据锁）：普通读加 MDL 读锁，DDL 要 MDL 写锁。DDL 等长查询释放 MDL，后续所有新查询又排在 DDL 后面排队。一条 DDL 把整表的读写都堵死了。
正确做法：
-- DDL 避开高峰，且用 online DDL 减少锁窗口
ALTER TABLE big_table ADD COLUMN c INT, ALGORITHM=INPLACE, LOCK=NONE;
-- 更稳：用 pt-online-schema-change / gh-ost 这类影子表工具
-- 不在原表上长时间锁，业务无感知
-- 发现 MDL 排队：查 metadata_locks
SELECT * FROM performance_schema.metadata_locks WHERE object_name='big_table';
-- 典型现场：processlist 里一堆线程状态是 Waiting for table metadata lock
-- 排查第一步：找到那个持 MDL 读锁、跑了很久的查询/kill 它
SELECT * FROM performance_schema.metadata_locks
WHERE lock_status = 'GRANTED' AND object_name = 'big_table';
-- 把持锁的长事务 kill 掉，排在后面的 DDL 和新查询瞬间都通了
加分点：SHOW PROCESSLIST 里看到一堆 Waiting for table metadata lock，先找那个持锁的长查询/kill 它，别让 DDL 一直排队堵新人。
坑 3：online DDL 被小事务堵住
场景 / 错误示范：
-- 用了 ALGORITHM=INPLACE 以为不锁表了，放心在高峰执行
ALTER TABLE t ADD COLUMN c INT, ALGORITHM=INPLACE, LOCK=NONE;
-- 但 DDL 开始和结束时仍要短暂拿 MDL，被一个忘了提交的小事务挡住
-- DDL 卡在准备阶段，后续所有访问 t 的请求排队
为什么是坑：online DDL 中途不锁表，但开始和结束都要短暂拿 MDL 排他锁做元数据更新。这两个瞬间如果被任何一个持 MDL 的小事务（哪怕只读）挡住，DDL 就卡住，进而堵死全表。开发者以为 online DDL 万无一失。
正确做法：
-- 执行 DDL 前，先确认表上没有长事务
SELECT * FROM information_schema.innodb_trx
WHERE trx_started < NOW() - INTERVAL 5 SECOND; -- 老事务先处理掉
-- 用 LOCK=NONE 明确要求不锁；卡住时立刻 KILL DDL，别硬等
-- 大表 DDL 用 gh-ost，完全不碰原表 MDL
加分点：online DDL 的两个 MDL 瞬间是事故高发点。执行前清掉该表上的所有活动事务，是标准动作。
坑 4：批量更新顺序不固定引发死锁
场景 / 错误示范：
# 两个批量任务同时更新同一批 id，但传入顺序不同
# 任务 A 的 id 列表是 [3,1,2]，任务 B 的是 [2,1,3]
for uid in [3, 1, 2]:
cur.execute("UPDATE account SET bal=bal-1 WHERE id=?", (uid,))
# A 锁 3→1→2，B 锁 2→1→3，交叉处死锁
为什么是坑：批量更新多行时，每条 UPDATE 单独拿锁。不同事务拿到的 id 顺序如果不固定，就会以不同顺序申请锁，和坑 1 同理引发死锁。这种死锁偶发、难复现。
正确做法：
# 批量更新前，先把 id 列表排序，所有事务都按同一顺序加锁
uids = sorted([3, 1, 2]) # 统一成 [1,2,3]
for uid in uids:
cur.execute("UPDATE account SET bal=bal-1 WHERE id=?", (uid,))
# 或者一条 IN 批量更新（数据库内部按索引顺序加锁，更可控）
-- 一条 IN 更新，比循环单条更短更可控
UPDATE account SET bal = bal - 1 WHERE id IN (1, 2, 3);
加分点：批量写的 id 列表一定先排序。这条简单规则能消灭一大类偶发死锁。
坑 5：自增锁 AUTO-INC 成表级瓶颈
场景 / 错误示范：
-- 高并发 INSERT 同一张自增表，吞吐上不去
INSERT INTO t(name) VALUES ('甲');
-- 大量插入在等 AUTO-INC 表锁，并发插入被串行化
为什么是坑：插入自增列时要申请 AUTO-INC 锁来分配下一个自增值。老模式下这个锁在语句期间持有，高并发插入会串行化，成为写入瓶颈。
正确做法：
-- 8.0 默认 innodb_autoinc_lock_mode=2（ interleaved），自增锁粒度最小
SHOW VARIABLES LIKE 'innodb_autoinc_lock_mode';
-- 批量插入用单行 VALUES 多值形式，减少申请次数
INSERT INTO t(name) VALUES ('a'),('b'),('c'); -- 一次申请连续多个自增值
-- 高并发写热点表，考虑分表分散压力
-- 查看当前 autoinc 相关等待
SHOW STATUS LIKE 'Innodb_row_lock%';
-- 高并发单表自增插入热点，先看是不是写入都集中在一张表的末尾索引
-- 8.0 innodb_autoinc_lock_mode=2 已经把锁压到最小
SHOW VARIABLES LIKE 'innodb_autoinc_lock_mode';
加分点：自增锁只在分配自增值时短暂持有，8.0 的 mode=2 已经优化得很好；真遇到插入瓶颈，先看是不是单表写入热点，再考虑分库分表。
坑 6：以为 InnoDB 有锁升级
场景 / 错误示范：
-- 老 DBA："InnoDB 锁太多会自动升级成表锁"
-- 实际上 InnoDB 没有锁升级机制（这是 SQL Server 的概念）
为什么是坑：很多人把 SQL Server 的"行锁多了升级表锁"概念套到 InnoDB 上。InnoDB 不会升级锁，但当你的写操作扫描全表时，等于客观上锁了全表——效果类似，但原理不是"升级"。混淆概念会在面试和排查时出错。
正确做法：
-- 准确认知：InnoDB 行锁一直是行锁，不升级
-- 但没走索引的写操作会把扫描到的每行都加锁，等效表锁
-- 所以"锁全表"的根因是没索引，不是锁升级
EXPLAIN UPDATE ... ; -- 看是不是 type=ALL
加分点：面试答"InnoDB 没有锁升级；你看到的表级效果是全表扫描加了全表行锁"，区分度很高。
坑 7：用 processlist 看锁信息不全
场景 / 错误示范：
-- 锁出问题了，开发者只看 SHOW PROCESSLIST
SHOW PROCESSLIST; -- 只看到线程在 Waiting，看不出谁等谁
为什么是坑：PROCESSLIST 只显示每个线程当前状态，不显示锁的等待关系、谁持有哪把锁。排查死锁/锁等待用它，信息严重不足，只能猜。
正确做法：
-- 8.0 用 performance_schema 的锁视图
SELECT * FROM performance_schema.data_locks; -- 当前所有锁
SELECT * FROM performance_schema.data_lock_waits; -- 谁在等谁
-- 配合看事务
SELECT trx_id, trx_started, trx_query FROM information_schema.innodb_trx;
-- 老版本用 innodb_locks / innodb_lock_waits（5.7）
-- 一把梭：当前所有等待链，谁等谁一目了然
SELECT
waiting_execution_thread_id AS 等待线程,
blocking_execution_thread_id AS 占用线程
FROM performance_schema.data_lock_waits;
-- 再 JOIN threads / innodb_trx 拿到对应 SQL，定位就完整了
-- 这套视图是 8.0 排查锁问题的主力，比 5.7 的 innodb_locks 直观
加分点：排查锁问题的标准三件套——innodb_trx（谁的事务）+ data_locks（锁在哪）+ data_lock_waits（等待链），比 processlist 强太多。
坑 8：乐观锁 version 字段忘了校验
场景 / 错误示范：
-- 用 version 做乐观锁，更新时却没带上 version 条件
UPDATE product SET stock = stock - 1, version = version + 1
WHERE id = 1; -- 漏了 AND version = ?，并发下照样超卖
为什么是坑：乐观锁的全部精髓就是"更新时带上 version=旧值"这个条件——version 对不上就更新 0 行。漏了它，version 字段形同虚设，并发修改照样互相覆盖。
正确做法：
-- 正确：更新条件里必须带 version
UPDATE product SET stock = stock - 1, version = version + 1
WHERE id = 1 AND version = 5; -- 只有 version 还是 5 才更新
-- ROW_COUNT()=0 说明被别人抢先了，应用层重试或提示
# 应用层：读出旧 version，更新时带上，影响 0 行就重试
row = cur.execute("SELECT stock, version FROM product WHERE id=1").fetchone()
cur.execute(
"UPDATE product SET stock=stock-1, version=version+1 WHERE id=1 AND version=?",
(row['version'],))
if cur.rowcount == 0:
retry() # 版本变了，重读重试
加分点：乐观锁适合冲突少的场景；高冲突下它会大量重试，不如直接悲观锁（FOR UPDATE）。按冲突频率选型。
坑 9：死锁日志没开事后查无对证
场景 / 错误示范：
-- 线上偶发死锁，事后想复盘，却不知道哪两条 SQL 互相堵
-- innodb_print_all_deadlocks 没开，错误日志里没有死锁详情
为什么是坑：MySQL 默认只把最近一次死锁存在 SHOW ENGINE INNODB STATUS 里，重启或多次死锁后被覆盖。没开 innodb_print_all_deadlocks，死锁现场就丢了，事后无法分析根因。
正确做法：
-- 开死锁日志，把每次死锁都写进错误日志
SET GLOBAL innodb_print_all_deadlocks = ON;
-- 随时看最近一次死锁
SHOW ENGINE INNODB STATUS;
-- 找到 LATEST DETECTED DEADLOCK 段，看两个事务分别持锁/等锁的 SQL
-- 死锁日志长这样（SHOW ENGINE INNODB STATUS 的关键段）：
-- LATEST DETECTED DEADLOCK
-- *** (1) TRANSACTION: UPDATE account SET bal=bal-1 WHERE id=1
-- *** (2) TRANSACTION: UPDATE account SET bal=bal-1 WHERE id=2
-- *** WE ROLL BACK TRANSACTION (2)
-- 看两个事务最后各执行了哪条 UPDATE，那就是循环等待的两端
加分点：死锁日志一定要开。复盘死锁时，重点看两个事务"最后一条 SQL"——那就是循环等待的两端。
坑 10：以为意向锁会互相阻塞行锁
场景 / 错误示范：
-- 开发者：意向锁 IS/IX 是不是也会堵别的事务的行锁？
-- 其实意向锁是表级的，只和表级锁（DDL）冲突，不阻塞行锁
为什么是坑：意向锁（IS/IX）表示"我接下来要在某些行上加 S/X 锁"，是表级标记，目的是让 DDL 想加表锁时知道表里有行锁。它和普通行锁之间不互相阻塞。混淆它的作用会错误地判断锁等待来源。
正确做法：
-- 意向锁兼容性：IX 和 IX 之间不冲突（都能加行锁）
-- IX 只和表级锁（如 LOCK TABLES、DDL 的 MDL）冲突
-- 所以两个事务同时对不同行加 IX 再上行锁，完全没问题
-- 排查时看到 IX 锁别紧张，它不是你要找的阻塞源
-- 兼容性矩阵速记：
-- IX（意向排他）和 IX：兼容（都能加不同行的行锁）
-- IX 和 表级 X 锁：冲突（DDL/LOCK TABLES 会等）
-- 所以排查时：看到一堆 IX 锁互相等待，基本不是 IX 本身的问题，
-- 而是它们要加的那几行行锁在互相等
加分点：意向锁的存在是为了"表级锁快速判断表里有没有行锁"，避免 DDL 逐行检查。它不阻塞并发行写。
坑 11：REPEATABLE READ 下写后读不一致
场景 / 错误示范：
-- 事务 A 在 RR 下
START TRANSACTION;
SELECT balance FROM account WHERE id=1; -- 读快照，balance=100
UPDATE account SET balance = 50 WHERE id=1; -- 自己更新了
SELECT balance FROM account WHERE id=1; -- 读到 50（自己改的可见）
-- 开发者：不是 RR 吗？怎么两次读不一样？
为什么是坑：RR 的一致性视图是"事务第一次读时"建立的，但自己事务所做的修改，在本事务里永远可见。开发者把"RR 下读永远一样"绝对化了，忽略了自己的写会让本事务读到新值。
正确做法：
-- 准确理解：RR 保证的是"别人事务已提交的修改，本事务快照读看不到"
-- 但本事务自己的修改，自己随时可见
-- 所以"写后读"读到的是自己写的值，这是对的，不是 bug
START TRANSACTION WITH CONSISTENT SNAPSHOT; -- 显式在建事务时就建快照
加分点：START TRANSACTION WITH CONSISTENT SNAPSHOT 能在建事务时就固定快照，而不是等第一条 SELECT 才建，对"读一致性"要求严格的场景更可控。
速查表：死锁与高级锁速记
| 主题 | 错误认知 | 正确结论 |
|---|---|---|
| 死锁成因 | 偶发随机 | 加锁顺序相反→循环等待 |
| 避免死锁 | 靠运气重试 | 统一加锁顺序 |
| MDL | DDL 不锁表 | 开始/结束要 MDL，堵长查询 |
| online DDL | 完全不锁 | 两瞬间要 MDL，会被小事务堵 |
| 批量更新死锁 | 不固定顺序 | id 排序后再加锁 |
| 自增锁 | 不影响并发 | 高并发插入有串行化 |
| 锁升级 | InnoDB 会升级 | InnoDB 无锁升级 |
| 看锁 | processlist | data_locks/waits 三件套 |
| 乐观锁 | 加 version 列就行 | 更新必须带 version=旧值 |
| 死锁复盘 | 自动留档 | 要开 innodb_print_all_deadlocks |
| 意向锁 | 阻塞行锁 | 只和表锁冲突 |
注意事项：避免死锁的 Checklist
- [ ] 多步更新所有事务按同一顺序加锁了吗？
- [ ] 批量更新的 id 列表排序了吗？
- [ ] DDL 避开高峰、清掉表上长事务了吗？
- [ ] 开了 innodb_print_all_deadlocks 吗？
- [ ] 乐观锁更新带 version=旧值了吗？
- [ ] 排查锁用 data_locks 三件套，不是只看 processlist？
- [ ] 长事务会引发 MDL 排队，监控配了吗？
一句话：死锁靠"统一加锁顺序"预防，MDL 靠"避开长事务执行 DDL"规避——理解这两条，线上锁事故能少一大半。
自测：你能定位死锁吗
题 1：两个事务以相反顺序更新 id=1 和 id=2，为什么会死锁？怎么避免？
<details>
<summary>参考答案</summary>
A 锁 1 等 2、B 锁 2 等 1，循环等待。避免办法：所有事务按统一顺序（如 id 从小到大）加锁。
</details>
题 2：ALTER TABLE 为什么会把整张表的读写都堵住？
<details>
<summary>参考答案</summary>
DDL 要拿 MDL 写锁，排在持 MDL 读锁的长查询后面；新查询又排在 DDL 后面。一条 DDL 堵死全表。应避开高峰、用 online DDL 或 gh-ost。
</details>
题 3：online DDL 用了 LOCK=NONE，为什么还是被一个小事务卡住？
<details>
<summary>参考答案</summary>
online DDL 中途不锁，但开始和结束要短暂拿 MDL，被任何持 MDL 的小事务挡住就卡住。执行前要清掉该表上所有活动事务。
</details>
题 4：批量更新不同顺序的 id 列表，为什么偶发死锁？
<details>
<summary>参考答案</summary>
两条事务以不同顺序申请多行锁，交叉处循环等待。批量更新前先把 id 排序，统一加锁顺序即可。
</details>
题 5：InnoDB 行锁太多会自动升级成表锁吗？
<details>
<summary>参考答案</summary>
不会。InnoDB 没有锁升级。你看到的表级效果是没走索引全表扫描，给每行都加了锁，根因是缺索引。
</details>
题 6：排查锁等待，为什么不能只看 SHOW PROCESSLIST？
<details>
<summary>参考答案</summary>
processlist 只显示线程状态，不显示谁等谁、锁在哪。要用 innodb_trx + performance_schema.data_locks/data_lock_waits 三件套。
</details>
题 7：乐观锁更新为什么必须带 WHERE version=旧值？
<details>
<summary>参考答案</summary>
这是乐观锁的核心：version 对不上就更新 0 行，让应用重试。漏了它，version 字段失效，并发照样覆盖。
</details>
题 8：线上死锁偶发，事后查不到现场，该开什么参数？
<details>
<summary>参考答案</summary>
开 innodb_print_all_deadlocks=ON，把每次死锁写进错误日志。否则只有最近一次留在 INNODB STATUS，容易被覆盖。
</details>
题 9：意向锁 IX 和 IX 之间会互相阻塞吗？
<details>
<summary>参考答案</summary>
不会。意向锁是表级标记，只和表级锁（DDL/LOCK TABLES）冲突，不阻塞并发行锁。两个事务同时对不同行加 IX 上行锁没问题。
</details>
题 10：RR 事务里自己 UPDATE 后再 SELECT，为什么读到了新值？
<details>
<summary>参考答案</summary>
RR 的快照对"自己事务的修改"始终可见。它保证的是看不到别人已提交的修改，不是连自己写的也看不到。这是正确行为。
</details>
题 11：线上 DDL 执行后整表卡住，processlist 全是 Waiting for table metadata lock，怎么最快恢复？
<details>
<summary>参考答案</summary>
用 performance_schema.metadata_locks 找到持 MDL 读锁的长事务/kill 它，DDL 拿到 MDL 后队列瞬间释放。不要等 DDL 自然超时。
</details>
下一篇预告：第 56 章 存储过程与触发器踩坑
文章回复
0 条公开回复