第 24 章 锁、加锁规则与死锁

本章解决"这条 SQL 到底锁了哪些行、锁到哪个区间"这一类只能靠推导回答的问题；它是第 22 章一致性读的另一半。

学习目标

读完本章，你应当能够：

1. 按作用范围复述 InnoDB 的锁层次（全局 → 表级 → 行级，含自增锁），说清 FLUSH TABLES WITH READ LOCK 的用途与危害，以及 mysqldump --single-transaction 绕开全局锁的机制与代价；
2. 画出元数据锁（metadata lock, MDL）的排队雪崩链条，并给出 performance_schema.metadata_locks、sys.schema_table_lock_waits、lock_wait_timeout 三个观测/干预入口；
3. 区分 ALGORITHM=COPY/INPLACE/INSTANT 各自允许的并发度，判断一条 DDL 会不会阻塞业务；
4. 用给定数据与索引结构逐区间推导 SELECT ... FOR UPDATE 的加锁范围，背出并验证加锁规则八条；
5. 说明唯一索引与非唯一索引等值查询为什么锁范围不同，以及意向锁为什么必须存在；
6. 解释 INSERT 的隐式锁、INSERT ... ON DUPLICATE KEY UPDATE 与 REPLACE INTO 的加锁差异；
7. 独立读懂 SHOW ENGINE INNODB STATUS 的 LATEST DETECTED DEADLOCK 段，并给出三条以上可落地的死锁预防手段；
8. 在乐观锁与悲观锁之间做取舍，写出用影响行数判断 CAS 成功与否的正确代码。

前置知识检查

24.1 锁的层次：从全局到行

表级锁与全局锁是"层次"关系而非互斥关系：同一条 UPDATE 会同时持有 MDL 读锁（保护表结构）、可能一个意向排他锁（intention exclusive lock, IX）、若干行级临键锁。线上事故往往不是行锁造成的，而是最外层那把"看不见的锁"。

24.1.1 全局锁：唯一真实用途是取一致性备份点

FLUSH TABLES WITH READ LOCK（FTWRL）先关闭所有打开的表（写回脏页、清理表缓存），再对整个实例加一把全局读锁。DML 被挡而 SELECT 仍能执行，这常让人误以为"库没问题"，实际写入早已超时。它唯一的正当用途是在不支持一致性快照的存储引擎（如 MyISAM）上做逻辑备份，或人为造一个静止点。危害有三层：全实例范围、持有期间连接池被等锁的写请求耗尽、会话断开不会自动释放（要靠 UNLOCK TABLES）。所以只允许"加锁 → 记录位点 → 立刻解锁"的毫秒级窗口。

mysqldump --single-transaction 之所以能不长时间持有全局锁，是因为先开事务并建立一致性快照（START TRANSACTION WITH CONSISTENT SNAPSHOT），之后整个导出读的都是这一份快照，业务写入可以继续而导出结果依然自洽。注意代价与前提：

1
2
3
4
5
6
7

-- mysqldump --single-transaction 在服务端的等价骨架
FLUSH TABLES WITH READ LOCK;                   -- 只为拿一个与快照对齐的 binlog 位点，毫秒级
SHOW MASTER STATUS;                            -- 8.0.26+ 改为 SHOW BINARY LOG STATUS
START TRANSACTION WITH CONSISTENT SNAPSHOT;    -- 快照在此刻建立，之后所有读都看这个版本
UNLOCK TABLES;                                 -- 立刻释放全局锁，业务写入恢复
-- …… 后续所有表的读取都在事务内完成，靠 MVCC 看到同一个一致性版本 ……
COMMIT;

三个前提必须同时成立，否则 --single-transaction 是假的一致性：只对事务型表有效（MyISAM 表在快照外，仍需 --lock-tables）、必须在 REPEATABLE READ 下（若隔离级别被改成 READ COMMITTED，快照读会在每条语句重新取视图，导出不再自洽）、导出期间不能有 DDL（DDL 会因 MDL 被阻塞，同时可能破坏导出映射）。

24.2 MDL：线上锁事故的第一名

24.2.1 为什么必须有 MDL：兼容矩阵与"一直持有"

MDL 解决的问题是：正在执行的一条语句，其表结构不能在执行期间被改掉。没有 MDL 时，会话 A 已按旧表定义开始逐行扫描，会话 B 迅速完成 ALTER TABLE t DROP COLUMN c，A 继续按旧定义解析后续记录就会读到错位的字节。它的两个特性决定了杀伤力：自动加（DML 加读锁、DDL 加写锁），且在显式事务里一直持有到事务结束（不是语句结束）。兼容性只有一句话：读锁与读锁兼容，其余两两互斥。

24.2.2 排队雪崩：为什么"一个长事务 + 一条 DDL"能把全表打死

MDL 的请求是一个先来先服务队列，而队列里的写请求会挡住它后面的读请求——即使读请求彼此兼容，这是最反直觉也最常考的一点：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

时间 ────────────────────────────────────────────────────────────────────▶
会话 A  BEGIN; SELECT ...;  └──── 持有 MDL 读锁，迟迟不 COMMIT ────┘
会话 B                        └─ ALTER TABLE ... 申请写锁，排在队首，等 A
会话 C/D/E                                   └─ SELECT ... 申请读锁，被 B 挡住（队列持续变长）
结果：A 不提交 ⟹ B 拿不到写锁 ⟹ C/D/E 全部排队 ⟹ 连接池耗尽 ⟹ 整个库不可用

MDL 请求队列（队首先获得锁；写锁是"排头兵"：它没拿到锁，后面兼容的读锁也不放行）
┌──────────────┬──────────────┬──────────────┬──────────────┐
│ A: 读锁(已得)│ B: 写锁(等)  │ C: 读锁(等)  │ D: 读锁(等)  │
└──────────────┴──────────────┴──────────────┴──────────────┘
长事务迟迟不释放，整条队列原地冻结

24.2.3 观测与干预

1
2
3
4
5
6
7
8

-- performance_schema 自 5.7 起记录 MDL 等待（5.7 上可能看不全），8.0 重写哈希结构后可信度提高
SELECT * FROM performance_schema.metadata_locks WHERE LOCK_STATUS = 'PENDING';
SELECT * FROM sys.schema_table_lock_waits;
SELECT trx_id, trx_mysql_thread_id,
       TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS age_sec, trx_query
FROM information_schema.innodb_trx ORDER BY trx_started;  -- MDL 读锁的持有者一定是未提交事务
-- lock_wait_timeout 只管表锁与 MDL 的等待上限（与行锁超时是两个独立参数），默认一年量级，发布脚本应显式设置
SET SESSION lock_wait_timeout = 5;

wait_timeout 管的是空闲连接存活时长（与应用连接池的 maxLifetime 配合），它不会解除一个正在等锁的会话——等锁的会话不是空闲状态。真正能打断"DDL 等 MDL"的是 lock_wait_timeout（会话级变量，超时后 DDL 以错误返回而不是无限等待）。两者常被混为一谈，属于必考辨析点。

24.2.4 Online DDL 的三种算法与各自锁范围

DDL 究竟阻塞多少业务取决于它用到哪种算法。优化器按 INSTANT > INPLACE > COPY 的顺序尝试，也可以用 ALGORITHM 与 LOCK 子句强制要求（不满足则报错而不是降级，这正是"上线前先验证 DDL 会不会重建表"的手段）：INSTANT（8.0.12+ 加列、8.0.29+ 任意位置加列/删列）只改数据字典、秒级完成，加列带默认值、改默认值、重命名列、ENUM 追加值都属于它；INPLACE 不拷贝整表但可能重建索引，加删二级索引与 VARCHAR 扩长（未跨 256 字节边界）属于它，是否阻塞 DML 取决于写成 LOCK=NONE 还是 LOCK=SHARED；COPY 建临时表逐行拷贝，改列类型、缩小 VARCHAR、加删主键、加空间索引、改字符集都要它，全程 DML 被阻塞、只能并发查询。

1
2
3
4

-- 让 DDL 要么以指定算法成功、要么直接失败暴露风险，
-- 而不是被服务端静默降级成 COPY 后在业务高峰把表锁死
ALTER TABLE t ADD COLUMN c INT, ALGORITHM=INSTANT;
ALTER TABLE t ADD INDEX idx_age(age), ALGORITHM=INPLACE, LOCK=NONE;

24.3 行锁的三种形态与 RR 为什么必须加间隙锁

InnoDB 的行锁加在索引记录上，不是加在"行"上——这是后面所有推导的根基。如果一条 SQL 用不到任何索引，它只能扫描聚簇索引并给扫描过的每条记录加锁，效果接近锁全表。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

索引记录（按索引键升序）与三种锁的覆盖关系
        id=5            id=10           id=15           id=20
   ────────●───────────────●───────────────●───────────────●────────▶
   (-∞,5)      (5,10)      (10,15)     (15,20)      (20,+∞)    ← 间隙，不含端点

 记录锁 Record Lock：只锁 ● 这一个索引记录，阻止对它做 UPDATE/DELETE
 间隙锁 Gap Lock   ：只锁一个开区间 (a,b)，**只在 RR 下存在**；阻止区间内 INSERT，
                     但不阻止其他事务对区间内的记录加锁（这一点是 24.7 那类死锁的根源）
 临键锁 Next-Key Lock = 记录锁 + 该记录之前的间隙锁 = (a,b]，RR 下加锁的默认单位
 插入意向锁 Insert Intention：INSERT 前对目标间隙加的"我要插这里"的意向，彼此兼容

RR 必须有间隙锁，是因为它要挡住的不是"已有行的修改"，而是"原本不存在的行的出现"：RR 下的当前读必须让同一事务重复执行同一条范围查询时返回相同结果集，一旦允许别的会话在自己扫描过的区间插入新行，第二次查询就会多出记录（幻读），而当前读不走 MVCC 快照、无法靠版本链屏蔽，只能锁住插入。RC 下每条语句取一次新快照，幻读在语义上被接受，于是 InnoDB 在 RC 下关闭间隙锁（只留记录锁），锁范围显著缩小——代价是加锁读不再保证范围稳定，且半一致性读会让 UPDATE 参照最新已提交版本（见第 22 章 22.2 节）。

24.4 加锁规则八条：逐条场景与验证

以下规则以 MySQL 5.7/8.0 官方文档的 InnoDB 锁模型为准，适用于 RR 隔离级别下的当前读（SELECT ... FOR UPDATE / LOCK IN SHARE MODE / UPDATE / DELETE）。推导共用同一张表与同一批数据：

1
2
3
4
5
6
7
8
9

CREATE TABLE t (
  id   INT PRIMARY KEY,               -- 聚簇索引，唯一
  age  INT,
  name VARCHAR(16),
  KEY idx_age (age)                   -- 普通二级索引（非唯一），叶子节点按 (age, id) 有序
) ENGINE=InnoDB;

INSERT INTO t VALUES (5,5,'a'),(10,10,'b'),(15,15,'c'),(20,20,'d'),(25,25,'e');
-- 于是 idx_age 的叶子记录序列是：(5,5) (10,10) (15,15) (20,20) (25,25)

唯一索引与非唯一索引的差异（规则 3 与规则 5 的对照推导）——这是本章最值得背下来的对比：

1
2
3
4
5
6
7
8

【等值查询 id = 10】（唯一索引，命中）
  聚簇索引一趟定位到 id=10 → 记录锁（规则 3）；范围 { id=10 }，可插入 id=7/11/14
【等值查询 age = 10】（非唯一索引，命中）
  定位到第一条 age=10 的记录 (10,10)；但 age 不唯一，必须向右扫到第一条 age≠10 的记录
  (15,15) 才敢停——为确认后面没有第二个 age=10 的行，这一步无法省略
  → (10,10] 临键锁 + (15,15] 临键锁（(5,10) 来自 (10,10] 的前半段），最终范围 (5, 15]（左界是 5 而不是 10）
  可插入 id=1、id=100；但 (age=7,id=1)、(age=10,id=11)、(age=14,id=9) 全部阻塞，区间宽度 3 vs 1
  —— 唯一索引省下的是整个扫描尾部，不是"一点"

24.5 意向锁：让表锁不必逐行检查

意向锁（intention lock）是表级锁，只有一个用途：在给行加锁之前先在表上留下"这表里已经有行锁了"的标记，让随后到来的表锁能 O(1) 判断能否获得。没有它时，LOCK TABLES t WRITE 为判断能否加锁就必须遍历全表所有记录看有没有行锁——代价与表大小成正比，而表锁本身就是为"快速挡住一批操作"存在的。

矩阵里唯一需要背的是左上角 2×2 全兼容：意向锁之间从不冲突，因为它们的语义只是"表里有行锁”，而多个行锁并存是正常的。所以意向锁本身几乎不会成为瓶颈，它只在"有人在加表锁"时才有存在感——这也解释了为什么"意向锁会导致死锁"是错误说法。

24.6 INSERT、隐式锁与三条特殊路径

24.6.1 隐式锁：插入为什么不需要显式加锁

INSERT 不显式加锁，因为它插入的是尚不存在的新记录，其他事务在它提交前根本看不见它（一致性读靠版本链屏蔽）；而且为每次插入都建锁结构，纯插入型负载的内存开销会成倍上升。隐式锁（implicit lock）的实现极轻：新记录的记录头写上当前事务的 trx_id；当其他事务用当前读访问到它、发现这个 trx_id 对应未提交事务时，就替它补一把锁：

1
2
3
4

时刻 1  事务 T1: INSERT INTO t VALUES (12,12,'x');   -- 记录头 trx_id = T1，无显式锁
时刻 2  事务 T2: SELECT * FROM t WHERE id=12 FOR UPDATE;  └─ 读到 id=12，查记录头 trx_id = T1
                  且 T1 活跃（在 innodb_trx 里）⟹ T2 以 T1 的身份补加 LOCK_REC（即"隐式锁转显式锁"）
时刻 3  其他想锁 id=12 的事务现在会等这把补出来的锁 / 等 T1 提交

面试官会追问"判断 trx_id 是否活跃要查哪里"：information_schema.innodb_trx（内存中的事务表）。若该 trx_id 已不在活跃列表里，说明事务早已提交或回滚，无需补锁。

24.6.2 INSERT … SELECT、外键与冲突插入

24.6.3 ON DUPLICATE KEY UPDATE 与 REPLACE INTO

24.7 死锁：四条件在 InnoDB 里的具体形态

操作系统课上的死锁四条件在 InnoDB 里逐一对应：

最典型的死锁：两个事务以相反顺序更新两行。

1
2
3
4
5
6
7

时刻   事务 T1                                    事务 T2
 t1    BEGIN;                                      BEGIN;
 t2    UPDATE ... WHERE id=1;   ── 持有 id=1 的排他记录锁
 t3                                                UPDATE ... WHERE id=2;  ── 持有 id=2 的锁
 t4    UPDATE ... WHERE id=2;   ── 申请 id=2，被 T2 挡住，等待
 t5                                                UPDATE ... WHERE id=1;  ── 申请 id=1，等待
 t6    等待图出现环 T1→T2→T1，死锁检测触发，回滚"代价较小"的那个事务，另一个正常提交

RR 下还有一类只有 InnoDB 会有的死锁，它更能体现间隙锁性质：两个事务先对同一间隙各加一把共享间隙锁（间隙锁之间兼容，所以都成功），随后各自要在该间隙内插入，插入意向锁与对方持有的间隙锁冲突，互相等待。这解释了为什么"降低隔离级别到 RC"能消掉大量插入型死锁——RC 下根本没有间隙锁。

24.7.1 死锁日志怎么读

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

------------------------
LATEST DETECTED DEADLOCK
------------------------
*** (1) TRANSACTION:  TRANSACTION 4213, ACTIVE 3 sec starting index read   ← 牺牲者，已回滚
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s); thread id 12, query id 88 updating
UPDATE account SET bal = bal - 1 WHERE id = 2      ← 它当时在执行、也因此死锁的语句
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3 page no 4 n bits 72 index PRIMARY of table `test`.`account`
 lock_mode X locks rec but not gap waiting   ← 排他记录锁且不含间隙 ⟹ 争的是唯一索引上的行
*** (2) TRANSACTION:  对称地持有 id=1、在等 id=2
UPDATE account SET bal = bal + 1 WHERE id = 1
*** (2) HOLDS THE LOCK(S): index PRIMARY ... lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED: ... lock_mode X locks rec but not gap waiting

读法固定为三步：先看每个 TRANSACTION 块最后一条 SQL（通常直接暴露顺序不一致）；再看 WAITING FOR THIS LOCK TO BE GRANTED 的 index 与 lock_mode（判断争的是主键还是二级索引、是纯记录锁还是含间隙的 locks gap before rec）；最后用 HOLDS THE LOCK(S) 与对方的 WAITING FOR 对照，闭合等待环。index PRIMARY 与 index idx_age 的差别极为关键——同一个业务逻辑走了不同索引，加锁范围完全不同，死锁形态也会不同。

1
2

SHOW ENGINE INNODB STATUS\G   -- 只保留"最近一次"死锁；排查频发死锁必须开下一个
SET GLOBAL innodb_print_all_deadlocks = ON;    -- 把每次死锁写进错误日志，用于统计与聚类

24.7.2 innodb_deadlock_detect 的代价与预防手段

死锁检测的开销容易被低估：每当一个加锁请求需要等待，InnoDB 就要做一次等待图环检测，复杂度与此时等待中的事务数同阶（O(n) 量级）。在热点行（例如同一商品的库存行）上成千上万个事务同时排队时，这部分 CPU 会急剧膨胀，表现为"CPU 打满但 QPS 不涨"。两种处理方式：保留检测（默认，推荐）让冲突立即以 1213 返回、应用捕获后重试；关闭检测（innodb_deadlock_detect=OFF）把环检测的 CPU 换成"等待 innodb_lock_wait_timeout（默认 50 s）的连接占用"，只有在业务能保证不会真正死锁（所有访问严格按主键升序、单语句事务）时才可接受，否则一个真死锁会挂满连接池，比 1213 危险得多。预防手段按性价比排序：

24.8 乐观锁与悲观锁、锁等待监控

24.8.1 悲观锁与"先查后改"的非原子性

SELECT ... FOR UPDATE 是悲观锁的标准用法，它把"读到的行"锁到事务结束；必须走索引，否则会锁住扫描到的全部记录（退化成近似锁表）。不用锁的"先查后改"则会出现经典的检查-使用竞态（check-then-act）：两个线程都读到库存 1、都判断"够扣"、各自扣减，最终超卖——解法不是加长事务，而是把判断与写入合并成一条原子语句，用影响行数判断结果：

1
2
3
4
5

-- 乐观锁：把"读-判断-写"压成一条带条件的 UPDATE。
-- 受影响行数是唯一可信的成功标志，比"再查一次版本号"可靠——后者又引入一个读取窗口
UPDATE account
   SET bal = bal - #{n}, version = version + 1
 WHERE id = #{id} AND version = #{oldVersion} AND bal >= #{n};

1
2
3
4
5
6
7

// 必须用影响行数判断；catch 不到异常不代表更新成功，这是最容易漏的一步。
// affectedRows = 0 有三种来源：版本过期、条件不满足（余额不足）、"新旧值相同"（MySQL 不计数），
// 因此要么保证语义上不存在等值更新，要么再查一次区分原因，不要一律当冲突重试
int rows = mapper.casUpdate(id, n, oldVersion);
if (rows == 0) {
    throw new ConcurrentModifyException();
}

冲突率决定取舍：冲突率低时乐观锁几乎零等待、吞吐高；冲突率高时它退化为"重试风暴"（大量失败请求反复重试，CPU 花在无用功上），此时悲观锁串行反而更稳。工程上的第三条路是把串行化下沉到唯一索引：用 INSERT 一条唯一键记录表达"我抢到了"，唯一约束天然原子，既不需要版本号也不需要 FOR UPDATE。

24.8.2 两个超时参数与锁监控

1
2
3
4
5
6
7

-- 8.0 的排障入口：data_locks 是"当前持有/等待的锁"，data_lock_waits 是"谁在等谁"；
-- 二者替代了 5.7 的 information_schema.innodb_locks / innodb_lock_waits（8.0 已移除）
SELECT * FROM performance_schema.data_locks;       -- 逐把锁：索引、lock_mode、LOCK_STATUS
SELECT * FROM performance_schema.data_lock_waits;  -- 等待关系：REQUESTING vs BLOCKING
SELECT * FROM sys.innodb_lock_waits;               -- 上面两张表 join 好的人类可读视图
-- 拿到 blocking_thread 后去 innodb_trx / processlist 看它的 SQL 与事务年龄，定位源头
SELECT * FROM sys.innodb_lock_waits WHERE locked_table = '`test`.`account`';

本章小结

自测题

Q1（推导）给定 24.4 节的表 t 与数据 (5,5),(10,10),(15,15),(20,20),(25,25)，在 RR 隔离级别下执行 SELECT * FROM t WHERE age = 15 FOR UPDATE;，请写出 idx_age 上的完整加锁区间，并说明为什么左边界不是 15。

Q2（判断）“在 REPEATABLE READ 下，事务 A 对某个不存在的 id=12 执行 SELECT ... FOR UPDATE 会给 (10,15) 加间隙锁；由于间隙锁之间兼容，事务 B 也对 id=12 加间隙锁不会阻塞。因此两个事务可以安全地同时申请同一个间隙并都成功。“这句话哪一部分对、哪一部分会导致问题？

Q3（设计）线上出现大面积查询超时，监控显示大量会话处于 Waiting for table metadata lock，且错误日志里有一条十几分钟前开始的 ALTER TABLE orders ADD COLUMN remark VARCHAR(64)。请给出排查与处置顺序。

Q4（分析）死锁日志里两个事务的最后一条 SQL 分别是 UPDATE account SET bal=bal-1 WHERE id=2 和 UPDATE account SET bal=bal+1 WHERE id=1，两处 WAITING FOR THIS LOCK TO BE GRANTED 都写着 index PRIMARY ... lock_mode X locks rec but not gap waiting。请还原死锁过程，并说明如果这两条 SQL 走的是 idx_uid 而不是主键，日志和锁范围会有什么不同。

Q5（实现）用 Java 实现"扣减库存并防止超卖"，要求不使用 SELECT ... FOR UPDATE，并说明重试策略与边界条件。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

// 库存判断写在 WHERE 里，由数据库在持锁期间原子完成——应用侧的 if 无法阻止
// 两个线程同时通过检查（check-then-act 竞态）；再用 affectedRows 判断成败
public boolean deduct(Long skuId, int n, int maxRetry) {
    for (int i = 0; i < maxRetry; i++) {
        int rows = mapper.casDeduct(skuId, n);   // UPDATE stock SET qty = qty - #{n}
                                                 //  WHERE sku_id = #{id} AND qty >= #{n}
        if (rows == 1) {
            return true;                         // 原子扣减成功，无需二次读
        }
        // rows == 0 可能是库存不足/行不存在（不可重试），也可能是别人抢先（可重试），
        // 必须回查区分，否则会在无库存时做无意义重试放大压力
        Integer qty = mapper.selectQty(skuId);
        if (qty == null || qty < n) {
            return false;
        }
        sleepBackoff(i);                         // 指数退避 + 抖动，避免同步重试风暴
    }
    return false;
}

面试高频问题与陷阱

延伸阅读

• MySQL 官方手册：InnoDB Locking、InnoDB Locking Model、Locks Set by Different SQL Statements in InnoDB、Metadata Locking、Online DDL Operations、Deadlocks in InnoDB。
• MySQL 官方手册：performance_schema.data_locks、performance_schema.data_lock_waits、sys.innodb_lock_waits；以及 innodb_lock_wait_timeout、lock_wait_timeout、innodb_deadlock_detect、innodb_print_all_deadlocks、innodb_autoinc_lock_mode 变量条目。
• 《高性能 MySQL》相应章节（锁、事务、schema 变更与在线 DDL 运营实践）。
• 《数据库系统概念》第 15、16 章：两阶段锁、时间戳排序与等待图检测，用于把 InnoDB 的实现放回理论谱系（详见第 6 章 6.4 节的死锁四条件推导）。