📖 本篇简介：锁是 MySQL 并发控制的核心，也是"这条 SQL 为什么把库堵了"的根源。本章作为并发与锁的上篇，聚焦行锁、索引与加锁范围、间隙锁这些基础又容易写错的点：没命中索引锁全表、间隙锁意外堵插入、唯一/普通索引加锁差异、快照读被骗等 11 个坑。示例基于 MySQL 8.0 InnoDB（本机无服务，加锁行为按官方文档与 innodb_locks 视图说明书写）。读完你能说清一条 SQL 到底锁了哪些行。
目录
- 坑 1：UPDATE 没命中索引以为只锁一行
- 坑 2：间隙锁在 RR 下意外堵插入
- 坑 3：快照读读不到最新行还以为锁了
- 坑 4：以为锁的是行不是索引记录
- 坑 5：全表 UPDATE 锁整张表
- 坑 6：唯一索引等值查不到行也加间隙锁
- 坑 7：普通索引和唯一索引加锁范围不同
- 坑 8：把锁等待超时当死锁
- 坑 9：唯一键冲突时 INSERT 互相等
- 坑 10：长事务持锁引发锁等待雪崩
- 坑 11：以为 RC 隔离级别没有间隙锁
- 速查表：加锁范围速记
- 注意事项：排查锁问题的 Checklist
- 自测：你懂 InnoDB 加锁吗
坑 1：UPDATE 没命中索引以为只锁一行
场景 / 错误示范：
-- 按 name 更新，开发者以为 WHERE name='甲' 只锁甲那一行
UPDATE account SET balance = balance - 10 WHERE name = '甲';
-- 但 name 列没索引！InnoDB 只能全表扫，扫描到的每一行都加行锁
-- 结果：整张表的行全被锁住，别的更新全部堵死
为什么是坑：InnoDB 的行锁是加在索引上的。WHERE 条件没走索引，存储引擎就把扫描过的每一行都加上锁。本意锁一行，实际锁了全表。这是线上"一条 UPDATE 堵死整张表"最常见的原因。
正确做法：
-- 先 EXPLAIN 确认 WHERE 走了索引，再执行写操作
EXPLAIN UPDATE account SET balance = balance - 10 WHERE name = '甲';
-- type 不是 ALL、key 命中了索引才放心
-- 给过滤列建索引
ALTER TABLE account ADD INDEX idx_name(name);
-- 更稳：用主键/唯一键定位，锁范围最小
UPDATE account SET balance = balance - 10 WHERE id = 123;
-- 反面教材现场：EXPLAIN 看到 type=ALL（全表扫描），这条 UPDATE 就是在锁全表
EXPLAIN UPDATE account SET balance = balance - 10 WHERE name = '甲';
-- 如果 key 列是 NULL，千万不要直接在生产执行，先建索引或改条件
-- 经验法则：写操作的 EXPLAIN 必须 type 至少是 range/ref，绝不接受 ALL
加分点：写操作前先 EXPLAIN 看 type 和 key，确认不是全表扫。没索引的写操作在生产就是定时炸弹。
坑 2：间隙锁在 RR 下意外堵插入
场景 / 错误示范：
-- 表 id 现有值 1, 5, 10
-- 事务 A：范围查
START TRANSACTION;
SELECT * FROM t WHERE id > 5 AND id < 20 FOR UPDATE;
-- 这条锁住了 (5,10] 和 10 之后的间隙
-- 事务 B：往间隙里插 id=8
INSERT INTO t VALUES (8, 'x'); -- 被间隙锁堵住，卡住
为什么是坑：RR 隔离级别下，范围查询（FOR UPDATE）不仅锁命中的记录，还锁记录之间的间隙（gap），防止别的事务在间隙里插入新行——这就是间隙锁，用来防幻读。开发者不知道它存在，明明"没查到行"却插入被堵，百思不得其解。
正确做法：
-- 了解间隙锁的存在，范围写操作前预估它会堵哪些间隙
-- 业务上确需高并发插入相邻值，可把隔离级别降到 RC（RC 没有间隙锁，只有记录锁）
SET GLOBAL transaction_isolation = 'READ-COMMITTED';
-- 注意：RC 下 binlog 必须是 ROW 格式，否则主从数据会不一致
-- 查看当前锁
SELECT * FROM performance_schema.data_locks;
SELECT * FROM performance_schema.data_lock_waits;
-- lock_mode 里带 Gap 的就是间隙锁
加分点：间隙锁是 RR 防幻读的代价。很多互联网公司生产用 RC + ROW binlog，就是为了去掉间隙锁、提升并发插入性能。
坑 3：快照读读不到最新行还以为锁了
场景 / 错误示范：
-- 事务 A
START TRANSACTION;
SELECT balance FROM account WHERE id = 1; -- 快照读，balance=100
-- 事务 B 把 balance 改成 50 并提交
SELECT balance FROM account WHERE id = 1; -- 还是 100！开发者以为数据没改
UPDATE account SET balance = balance - 10 WHERE id = 1; -- 基于旧值 100 更新
为什么是坑：RR 下普通 SELECT 是快照读，读的是事务启动时的一致性视图，看不到别的事务新提交的修改。开发者以为"我查了就是最新的"，其实读的是历史快照，基于它做判断就错了。
正确做法：
-- 要读最新值并加锁，用当前读
START TRANSACTION;
SELECT balance FROM account WHERE id = 1 FOR UPDATE; -- 读到最新已提交值
-- 或 SELECT ... FOR SHARE 共享锁
-- 业务逻辑基于这个最新值往下走
加分点："读历史快照还是读最新"是 RR 下必须分清的：普通 SELECT 快照读，加 FOR UPDATE/SHARE 才是当前读。
坑 4：以为锁的是行不是索引记录
场景 / 错误示范：
-- 开发者：我用 name=甲 更新，怎么把 id=999 那行也锁了？
UPDATE t SET v = 1 WHERE name = '甲';
-- name 是普通索引（非唯一），name=甲 可能对应多行
-- 而且会锁住 name=甲 在索引上的记录 + 后面的间隙
为什么是坑：InnoDB 锁的是索引记录，不是表的物理行。你 WHERE 用哪个索引，锁就加在那个索引的记录上。普通索引等值查询，除了命中的索引记录，还会顺着索引向后扫到第一个不满足的位置，把那里的间隙也锁住。
正确做法：
-- 理解"锁索引"：定位条件尽量用唯一索引/主键，锁范围最小最可控
UPDATE t SET v = 1 WHERE id = 123; -- 主键等值，只锁这一条索引记录
-- 普通索引等值：知道它会额外向后锁间隙，高并发插入相邻值时留意
-- EXPLAIN 看 type=ref、key 命中哪个索引，就知道锁加在哪棵索引树上
-- 实例：name 普通索引，name='甲' 对应 id=3,id=7 两行
-- InnoDB 不只锁这两行，还沿索引向后锁到第一个 name>'甲' 的位置
-- 所以同样是 WHERE name='甲'，更新和插入相邻 name 值都可能互相阻塞
-- 这就是"锁索引"而非"锁行"的直接后果
加分点：面试说清"InnoDB 行锁是索引锁，锁哪些索引记录取决于 WHERE 走哪个索引"，就超过大部分候选人了。
坑 5：全表 UPDATE 锁整张表
场景 / 错误示范：
-- 需求：把所有用户等级刷新
UPDATE user SET level = level + 1; -- 没 WHERE，全表更新
-- 扫描全表，每行加锁，整表锁住，期间任何写操作都排队
为什么是坑：不带 WHERE 的 UPDATE/DELETE 扫全表，每行都加行锁，等于表锁效果。数据量大时，这条语句跑多久就锁多久，库基本不可写。这是事故级操作。
正确做法：
-- 必须全表刷新时，分批按主键走，每批小事务
UPDATE user SET level = level + 1 WHERE id BETWEEN 1 AND 1000;
UPDATE user SET level = level + 1 WHERE id BETWEEN 1001 AND 2000;
-- 每批只锁一段主键，间隔执行，避开高峰
-- 保护手段：开启 sql_safe_updates，没 WHERE 或没索引的写操作直接报错
SET GLOBAL sql_safe_updates = 1;
-- 之后执行 UPDATE user SET level=level+1 会报错：
-- Error 1175: You are using safe update mode...
-- 强制开发者补上 WHERE + 索引，从源头防住全表写
加分点：全表写操作前先评估影响行数，能分批就分批。sql_safe_updates 能让没 WHERE/没索引的写操作直接报错，是个保护。
坑 6：唯一索引等值查不到行也加间隙锁
场景 / 错误示范：
-- 表 id 现有 1, 10
-- 事务 A
START TRANSACTION;
SELECT * FROM t WHERE id = 5 FOR UPDATE; -- id=5 不存在！
-- 开发者以为没查到行就没加锁
-- 事务 B：插入 id=5
INSERT INTO t VALUES (5, 'x'); -- 被间隙锁堵住了
为什么是坑：即使等值查询没命中行，InnoDB 为了防止别的事务插入这个"不存在的值"，会在该位置加间隙锁。查不到行 ≠ 没加锁，这是间隙锁最反直觉的地方。
正确做法：
-- 知道这个行为：插入前先 FOR UPDATE 探测，探测本身会锁住间隙
-- 高并发"先查后插同一唯一键"的场景，用 INSERT ... ON DUPLICATE KEY UPDATE
-- 或 INSERT IGNORE，让数据库处理冲突，别靠"先 SELECT 再 INSERT"
INSERT INTO t(id) VALUES (5)
ON DUPLICATE KEY UPDATE v = v; -- 冲突了也不报错
加分点：并发防重插入，不要"SELECT 判存在再 INSERT"，要用唯一索引+INSERT 冲突处理，原子且省锁。
坑 7：普通索引和唯一索引加锁范围不同
场景 / 错误示范：
-- 同样是等值查询，开发者以为加锁范围一样
-- 唯一索引：WHERE id=5（存在），只锁 id=5 这一条记录
-- 普通索引：WHERE name='甲'，name=甲 可能有多条，且向后锁到第一个不匹配处的间隙
为什么是坑：唯一索引等值命中，精确锁一条记录即可；普通索引等值，InnoDB 要沿索引向后扫描，直到找到第一个不满足条件的键，把它前面的记录和间隙都锁住。普通索引加锁范围天然比唯一索引大。
正确做法：
-- 高并发更新，定位列优先用主键/唯一索引
UPDATE t SET v = 1 WHERE id = 123; -- 锁范围最小
UPDATE t SET v = 1 WHERE name = '甲'; -- 普通索引，锁范围更大，注意间隙
-- EXPLAIN 确认走的是唯一索引（const/ref）还是普通索引（ref）
加分点：同一条 WHERE，走主键和走普通索引，锁范围可能差好几倍。写并发代码时把这层算进去。
坑 8：把锁等待超时当死锁
场景 / 错误示范：
-- 报错 Lock wait timeout exceeded; try restarting transaction
-- 开发者当死锁处理，让应用自动重试
UPDATE t SET v = 1 WHERE id = 1; -- 被一个 50 秒前的事务堵住
为什么是坑：锁等待超时（innodb_lock_wait_timeout 默认 50 秒）是"等别人的锁等太久"，不是死锁。死锁是双方互等、MySQL 主动回滚一方；锁超时是单向等待。盲目重试只会再堵 50 秒。
正确做法：
-- 先区分错误码：1213=死锁，1205=锁等待超时
-- 锁超时去查谁占着锁
SELECT trx_started, trx_query FROM information_schema.innodb_trx
ORDER BY trx_started;
-- 找到占锁的长事务，KILL 或优化它，而不是重试当前事务
加分点：锁等待超时的根因永远在"占锁的那个长事务"，不在被堵的这条 SQL 上。
坑 9：唯一键冲突时 INSERT 互相等
场景 / 错误示范：
-- 两个事务同时插入同一个唯一键 user_code='A100'
-- 事务 A：INSERT 还没提交
-- 事务 B：INSERT 同样的 'A100'，被堵住等 A 的锁，而不是立刻报错
为什么是坑：唯一索引冲突时，后插入的事务不是立刻报 Duplicate key，而是先等持锁事务提交或回滚——等 A 提交了，B 才报冲突；等 A 回滚了，B 才插入成功。这种"等唯一键"的锁等待，在高并发防重插入时容易堆积。
正确做法：
-- 接受等待是正常的；要快速失败可加 NOWAIT/SKIP LOCKED（8.0）
INSERT INTO t(user_code) VALUES ('A100'); -- 正常让它排队等
-- 应用层做好唯一冲突的捕获和重试，别把"等待"当异常
-- 8.0 任务抢出示例：多个 worker 抢同一批待处理任务，谁先抢到谁干
-- 不会两个 worker 抢同一条，也不会空等
SELECT id FROM task
WHERE status = 'READY'
ORDER BY id
LIMIT 10
FOR UPDATE SKIP LOCKED; -- 跳过已被别人锁住的行
-- 拿到这批 id 后再 UPDATE 状态为 DOING
加分点：8.0 的 SELECT ... FOR UPDATE NOWAIT / SKIP LOCKED 能让查询拿不到锁立刻返回，适合做任务抢占，减少无谓等待。
坑 10：长事务持锁引发锁等待雪崩
场景 / 错误示范：
-- 一个忘了提交的事务，持有 account 表几行的锁
-- 后续几百个更新这几行的请求全堵在锁等待队列里
-- 连接池被占满，整个应用对外不响应
为什么是坑：一行锁被占住，所有要写这行的请求排队等待，等待又占着数据库连接，连接池耗尽，故障从"一行"蔓延到"整个应用不可用"。这就是锁等待雪崩。
正确做法：
-- 事前：监控长事务（innodb_trx 里 trx_started 超阈值告警）
-- 事中：发现雪崩，先 KILL 掉持锁的长事务，立刻恢复
SELECT trx_mysql_thread_id FROM information_schema.innodb_trx
ORDER BY trx_started LIMIT 1;
-- KILL 最老的那个，等待队列瞬间释放
-- 事前告警：把长事务监控做成告警（trx_started 距今超过 30 秒就报警）
SELECT
TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS long_sec,
trx_query
FROM information_schema.innodb_trx
WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 30;
-- 这个查询本身就该接进监控系统，长事务早发现早处理
加分点：锁等待雪崩时，最快的恢复不是扩容，而是找到并 kill 占锁的那个长事务——它才是症结。
坑 11：以为 RC 隔离级别没有间隙锁
场景 / 错误示范：
-- 开发者：我们用 RC，所以没有间隙锁，可以放心并发插入
-- 但 RC 下唯一索引的"外键检查"和"唯一性检查"仍然会加间隙锁
为什么是坑：RC 级别普通的范围查询确实没有间隙锁，但不是完全没有间隙行为——唯一索引做唯一性检查时，仍可能在插入位置加短暂的间隙性质的锁。笼统说"RC 没有间隙锁"是不准确的，面试容易被追问翻车。
正确做法：
-- 准确表述：RC 下普通范围查询的 next-key lock 退化为记录锁，不再锁间隙
-- 但唯一索引插入时的唯一性校验仍会加必要的锁
-- 生产用 RC 的前提：binlog_format 必须是 ROW
SET GLOBAL transaction_isolation = 'READ-COMMITTED';
SET GLOBAL binlog_format = 'ROW';
加分点：面试答"RC 去掉了间隙锁提升并发，但唯一性检查仍有必要加锁，且 binlog 必须 ROW"，比只说"RC 没间隙锁"专业得多。
速查表：加锁范围速记
| 场景 | 加锁范围 | 风险 |
|---|---|---|
| UPDATE 没走索引 | 全表每行 | 锁死整表 |
| 范围查询 FOR UPDATE (RR) | 记录+间隙 | 堵插入 |
| 普通 SELECT | 不加锁（快照读）| 读到历史值 |
| 唯一索引等值命中 | 单条记录 | 最小 |
| 普通索引等值 | 记录+向后间隙 | 范围更大 |
| 等值查不到行 (RR) | 间隙锁 | 堵插入 |
| 全表 UPDATE | 整表 | 事故级 |
| RC 范围查询 | 记录锁，无间隙 | 并发更好 |
| 唯一键冲突 INSERT | 等持锁事务 | 排队 |
| 长事务持锁 | 等待雪崩 | 连接池耗尽 |
注意事项：排查锁问题的 Checklist
- [ ] 慢写操作先 EXPLAIN，确认 WHERE 走了索引？
- [ ] 知道这条 SQL 在 RR 下会加间隙锁吗？
- [ ] 是快照读还是当前读？业务要最新值吗？
- [ ] 锁等待超时先查 innodb_trx 找占锁长事务了吗？
- [ ] 长事务告警配置了吗？
- [ ] 高并发插入场景用 RC+ROW 去掉间隙锁评估过吗？
- [ ] 并发防重插入用唯一索引+冲突处理，不是先查后插？
一句话：行锁加在索引上，没索引就锁全表；RR 有间隙锁，RC 才去掉——这两条想清楚，锁问题就解决一半。
自测：你懂 InnoDB 加锁吗
题 1：UPDATE t SET v=1 WHERE name='甲' 没命中索引，锁了多少行？
<details>
<summary>参考答案</summary>
InnoDB 全表扫描，扫描到的每一行都加行锁，等于锁全表。写操作前必须 EXPLAIN 确认走索引。
</details>
题 2：RR 下范围查询 FOR UPDATE 为什么会堵住后面的 INSERT？
<details>
<summary>参考答案</summary>
间隙锁（gap lock）锁住记录间的间隙，防止幻读。别的事务往间隙里插入会被堵住。这是 RR 防幻读的代价。
</details>
题 3：普通 SELECT 为什么不加锁却可能读错？
<details>
<summary>参考答案</summary>
它是 MVCC 快照读，读事务启动时的一致性视图，看不到别的事务新提交的修改。要最新值得用 FOR UPDATE/SHARE 当前读。
</details>
题 4：唯一索引等值查询没查到行，加锁了吗？
<details>
<summary>参考答案</summary>
加了间隙锁。为防止别的事务插入这个不存在的值，InnoDB 在该位置加 gap lock。查不到行 ≠ 没加锁。
</details>
题 5：同一条 WHERE，走主键和走普通索引，锁范围差在哪？
<details>
<summary>参考答案</summary>
唯一索引等值精确锁一条记录；普通索引要向后扫到第一个不匹配处，把中间记录和间隙都锁住，范围更大。
</details>
题 6：Lock wait timeout exceeded 为什么不该直接重试？
<details>
<summary>参考答案</summary>
这是单向等别人的锁超时，根因是占锁的长事务。重试只会再等 50 秒。要查 innodb_trx 找到并处理占锁事务。
</details>
题 7：一行锁被长事务占住，为什么会演变成整个应用不可用？
<details>
<summary>参考答案</summary>
写这行的请求排队等待，等待又占着数据库连接，连接池耗尽，故障从一行蔓延到整个应用。这是锁等待雪崩。
</details>
题 8：生产用 RC 隔离级别想去掉间隙锁，前提条件是什么？
<details>
<summary>参考答案</summary>
binlog_format 必须设为 ROW。否则 RC 下语句级 binlog 会导致主从数据不一致。
</details>
题 9：并发防重插入，为什么不要"先 SELECT 判存在再 INSERT"？
<details>
<summary>参考答案</summary>
两步之间有时间窗，且探测会加间隙锁。应建唯一索引，用 INSERT ... ON DUPLICATE KEY UPDATE / INSERT IGNORE 让数据库原子处理冲突。
</details>
题 10：8.0 里想让查询拿不到锁立刻返回，用什么语法？
<details>
<summary>参考答案</summary>
SELECT ... FOR UPDATE NOWAIT（拿不到锁立刻报错）或 SKIP LOCKED（跳过锁行），常用于任务抢占，减少无谓等待。
</details>
题 11：线上突然大量写请求卡住，第一步该查什么？
<details>
<summary>参考答案</summary>
查 information_schema.innodb_trx 按 trx_started 排序，找最老的长事务；再查 performance_schema.data_lock_waits 看谁在等谁。大概率是某个泄漏/慢事务占着锁，KILL 掉它即可恢复。
</details>
下一篇预告：第 55 章 并发与锁踩坑（二）
文章回复
0 条公开回复