MYSQL 8.4 · 事务存储引擎锁专题

MySQL 行锁、间隙锁与死锁：SQL 到底锁住了哪里

SQL（Structured Query Language，结构化查询语言）是向数据库表达查询和修改的语言。InnoDB 是 MySQL 默认的事务存储引擎；它的行锁阻止冲突修改某条索引记录，间隙锁保护尚未插入的索引范围，死锁则是多个事务互相等待对方持有的锁。

适用：MySQL 8.4 / InnoDB前置：有序索引与事务重点：索引范围 · 临键锁 · 等待图
一、为什么需要锁：两个扣款不能同时相信旧余额

小林和小周同时读取余额 100，各自准备扣 80。如果都按旧值计算并写回 20，就丢失了一次扣款。锁的任务不是“让数据库变慢”，而是在会破坏业务约束的操作之间建立先后顺序。

START TRANSACTION;
SELECT balance FROM accounts WHERE id=7 FOR UPDATE;
UPDATE accounts SET balance=balance-80 WHERE id=7 AND balance>=80;
COMMIT;
先分清：普通 SELECT 多数时候走 MVCC 快照；FOR UPDATE 和写语句读取当前状态并加锁。锁是否精确，首先取决于查询实际扫描的索引范围。
二、三种范围锁：记录、空隙和两者组合
正式名称白话解释保护对象主要效果
Record Lock（记录锁）锁住已存在的一个索引记录例如索引值 20阻止冲突更新或删除
Gap Lock（间隙锁）锁住相邻索引值之间没有记录的区间例如 (10,20)阻止向该区间插入
Next-Key Lock（临键锁）一个间隙加右端记录例如 (10,20]同时保护范围与记录

意向锁是表级信号，表示事务准备在表中取得行级共享锁或排他锁，帮助表锁快速判断是否冲突；它不是“准备锁某一行”的排队票。

实现边界：InnoDB 的“行级锁”建立在索引记录上。缺少合适索引时，执行计划可能扫描并锁住远大于业务想象的范围；不要把 WHERE 写了一行等同于只锁一行。
三、图解完整索引：范围条件究竟覆盖哪些位置

假设二级索引 idx_score 当前完整有序值为 5、10、20、30、40，下标从 0 开始。事务执行 SELECT * FROM users WHERE score BETWEEN 12 AND 25 FOR UPDATE，在默认 REPEATABLE READ（可重复读）下讨论典型 next-key 范围。

候选全貌 → 条件定位 → 实际保护范围共 5 条记录，下标 0…4；口述的“第 3 条”是下标 2，不能混用 510203040 (-∞,5)(5,10)(10,20)(20,30)(30,40)(40,+∞)下标 0下标 1下标 2下标 3下标 4 条件 12 ≤ score ≤ 25返回记录只有 score=20 扫描从 (10,20] 进入，再检查 (20,30]30 是第一个越过上界的索引记录 典型覆盖：(10,20] 与 (20,30]多扫一个边界并保护插入范围；实际以执行计划与版本规则为准
最应记住：范围锁从完整有序索引和实际扫描过程推出来，不是只圈 WHERE 最终返回的行。常见误读是“查询只返回 20，所以只锁 20”。
不能拿图当万能公式：唯一索引精确命中通常只锁记录；READ COMMITTED 通常不使用间隙锁完成普通搜索和扫描，但外键检查、重复键检查等仍可能使用。最终范围还受索引选择、边界是否存在和版本影响。
四、从 SQL 推导锁：按五步而不是背结论
  1. 确定读取类型：普通快照读，还是 FOR UPDATE、UPDATE、DELETE 等当前读。
  2. 确定隔离级别：REPEATABLE READ 与 READ COMMITTED 的间隙策略不同。
  3. 看执行计划：实际选择哪个索引、扫描什么范围，而不是只看 WHERE。
  4. 画完整索引：列出现存值、负无穷与正无穷间隙，再沿扫描方向走。
  5. 区分返回与加锁：扫描集合可能大于最终返回集合。
场景典型推导最容易漏掉
唯一索引等值命中定位一个唯一记录通常不锁前方间隙
唯一索引等值未命中定位其应落入的间隙可能保护该间隙
普通索引等值相同值可能有多条不能套唯一索引规则
范围查询从起点扫描到越界位置返回集合不等于扫描集合
无可用索引可能扫描大量聚簇记录业务以为“只改一行”
五、死锁怎样长出来：把持有和等待画成环

下面不是数据库随机出错，而是两个事务采用相反顺序锁账户。初始余额 A=100、B=100。

状态来历：四步形成等待环，第五步必须选牺牲者 步骤 1 · T1锁住账户 A步骤 2 · T2锁住账户 B步骤 3 · T1 请求 BB 被 T2 持有 → 等待步骤 4 · T2 请求 AA 被 T1 持有 → 成环 事务 T1持有 A · 等待 B 事务 T2持有 B · 等待 A T1 等 T2 释放 B T2 等 T1 释放 A InnoDB 检测到环，回滚一个事务另一个继续；应用仍要重试完整事务
最应记住：死锁需要“各自已经持有资源，又等待对方资源”构成环；普通锁等待不等于死锁。常见误读是认为提高等待超时就能消除死锁。
-- T1                                  -- T2
UPDATE account SET balance=90 WHERE id=1;
                                      UPDATE account SET balance=90 WHERE id=2;
UPDATE account SET balance=90 WHERE id=2; -- 等待
                                      UPDATE account SET balance=90 WHERE id=1; -- 成环
六、定位与治理：先读等待关系，再改访问顺序
  1. 保留失败事务的业务标识、SQL、参数与重试次数。
  2. 在 mysql 客户端执行 SHOW ENGINE INNODB STATUS\G，读出双方各自持有什么、等待什么；\G 表示把宽结果按字段纵向显示。
  3. 结合执行计划确认实际索引与扫描范围。
  4. 让不同事务以相同顺序访问表与行，缩短事务。
  5. 对牺牲者做有界、带退避的完整事务重试，不能只重跑最后一条 SQL。

有效办法

固定加锁顺序；建立合适索引；小事务；条件更新；监控等待；重试具有幂等边界。

无效口号

“禁止并发”“把超时调大”“隔离级别调低就绝不会死锁”。写操作之间仍可能死锁。

设计代价：索引让锁范围更精确，但增加写放大；悲观锁保护强但会等待；乐观条件更新减少持锁流程，但冲突时需要业务重试。
七、代码坑点、面试话术与应用场景

六个高频坑

  1. 普通 SELECT 查完再更新，却以为查询已经锁住。
  2. 只看 WHERE，不看执行计划使用的索引。
  3. 事务中调用远程接口，让锁跟着网络超时一起存活。
  4. 不同业务路径用相反顺序更新相同资源。
  5. 捕获死锁后只重试单条 UPDATE，破坏事务整体语义。
  6. 把死锁和慢锁等待混为一谈。
90 秒回答：InnoDB 的行级锁建立在索引记录上。记录锁保护现存记录，间隙锁保护两个索引值之间的插入空间，next-key lock 通常是间隙加右端记录，用于范围当前读。具体锁范围要从隔离级别、执行计划选择的索引和实际扫描边界推导，不能只看返回行。死锁发生在事务分别持有锁又互等对方资源形成环时，InnoDB 会回滚一个事务，因此应用必须重试完整事务。治理上要统一访问顺序、缩短事务、建立合适索引并从死锁日志还原等待图。

应用场景

账户扣款、库存预占、任务抢占等必须基于最新状态做互斥决策的场景适合使用数据库锁。若可以把约束写进一条条件 UPDATE 或唯一约束，通常比“先查再锁再改”更简单。

资料依据