开发者日记

外观

间隙锁踩坑实录：RR下范围更新的锁放大与死锁排查 ​

问题背景：一条范围更新拖垮整段库存更新 ​

线上曾出现一个典型故障：营销活动开始后，库存扣减接口大量超时，数据库 CPU 并不高，但活跃连接数迅速打满。通过 SHOW PROCESSLIST 看到大量会话处于 updating 状态，等待事件集中在 lock wait。最终定位到的元凶，是一条在可重复读（RR，REPEATABLE READ）隔离级别下执行的范围更新语句：它在二级索引上锁定了远大于业务预期的区间，也就是常说的间隙锁导致的锁放大。

很多开发者对 InnoDB 锁的理解停留在“行锁只锁命中的行”，但在 RR 下，为了防止幻读，InnoDB 在范围扫描时会使用 Next-Key Lock，它同时锁住记录本身和记录之前的间隙。一旦索引选择不佳或查询条件跨度过大，锁范围会急剧膨胀，不同事务以相反顺序申请间隙时还可能触发死锁。

方案对比：为什么不能简单改成 RC ​

遇到间隙锁问题，常见的应对思路有三类：

  1. 隔离级别降级为读提交（RC）：RC 下通常不使用间隙锁（外键检查等场景除外），能显著减少锁范围，但会改变业务的一致性语义，主从 binlog 通常也要配合 ROW 格式，属于架构级变更，不能贸然操作。
  2. 保持 RR，优化索引与语句：通过等值查询、唯一索引、精确区间收敛扫描范围，让 Next-Key Lock 退化为记录锁，这是改动最小、最稳妥的方式。
  3. 重构业务逻辑：把大范围更新拆分为小批次，或使用队列、乐观锁控制并发，适合高并发写场景，但改造成本较高。

对大多数线上系统而言，优先选择第二种：在不改隔离级别的前提下，理解加锁规则并修正 SQL 与索引。

加锁规则与范围更新流程 ​

在 RR 隔离级别下，InnoDB 对普通二级索引的范围扫描通常加 Next-Key Lock；若查询是唯一索引的等值查询且记录存在，则退化为记录锁。范围更新还会继续扫描到第一条不满足条件的记录，并对其所在区间加锁。

事务执行范围UPDATEWHERE status与时间二级索引范围扫描命中多条索引记录加Next-Key Lock记录锁加间隙锁锁区间放大阻塞相邻插入事务B反向申请锁插入或更新相邻键形成等待环InnoDB回滚检测

实操步骤：可复现的间隙锁与死锁 ​

下面用一个订单任务表复现问题。建议在测试库的两个会话中按顺序执行，所有 SQL 可直接运行。

sql
-- 建议使用 MySQL 8.x，确认隔离级别为 RR
SELECT @@transaction_isolation;
-- 若不是 RR，可在当前会话设置：
-- SET SESSION transaction_isolation = 'REPEATABLE-READ';

DROP TABLE IF EXISTS t_order_task;
CREATE TABLE t_order_task (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  order_no VARCHAR(32) NOT NULL,
  status TINYINT NOT NULL,
  shop_id BIGINT NOT NULL,
  updated_at DATETIME NOT NULL,
  KEY idx_shop_status (shop_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO t_order_task
  (order_no, status, shop_id, updated_at)
VALUES
  ('A001', 0, 100, '2025-01-01 10:00:00'),
  ('A002', 0, 100, '2025-01-01 10:01:00'),
  ('A003', 1, 100, '2025-01-01 10:02:00'),
  ('A009', 0, 200, '2025-01-01 10:03:00');

场景一：范围更新锁住意外间隙 ​

会话 A 执行一个看似普通的状态批量更新：

sql
-- 会话 A：不提交，持有关联索引记录与间隙锁
START TRANSACTION;
UPDATE t_order_task
SET status = 2, updated_at = NOW()
WHERE shop_id = 100
  AND status = 0;

此时会话 B 尝试向同一个 shop_id 区间插入新的待处理任务：

sql
-- 会话 B：可能被阻塞，而不是立刻插入成功
START TRANSACTION;
INSERT INTO t_order_task
  (order_no, status, shop_id, updated_at)
VALUES
  ('A010', 0, 100, NOW());

业务上看，插入的是一条“新任务”，与要更新的旧任务并不冲突；但在索引 (shop_id, status) 上，新值落在被 Next-Key Lock 保护的区间内，InnoDB 为了防止其他事务插入幻读行，会让该插入等待。会话 A 执行以下命令可验证锁信息：

sql
-- MySQL 8.0 查看当前锁与等待关系
SELECT
  r.trx_id AS waiting_trx,
  r.trx_mysql_thread_id AS waiting_thread,
  b.trx_id AS blocking_trx,
  b.trx_mysql_thread_id AS blocking_thread,
  now() - INTERVAL r.trx_wait_started SECOND AS wait_since
FROM information_schema.innodb_lock_waits w
JOIN information_schema.innodb_trx b
  ON b.trx_id = w.blocking_trx_id
JOIN information_schema.innodb_trx r
  ON r.trx_id = w.requesting_trx_id;

-- 也可以直接观察最新的死锁与锁等待诊断
SHOW ENGINE INNODB STATUS\G

场景二：反向加锁触发死锁 ​

间隙锁还有一个容易被忽略的特点：多个事务可以同时持有同一间隙的间隙锁，因为间隙锁的作用是阻止插入，彼此之间并不冲突；真正冲突发生在它们都想向该间隙插入记录时。复现顺序如下：

sql
-- 会话 A：先通过范围查询或更新持有间隙锁
START TRANSACTION;
SELECT id FROM t_order_task
WHERE shop_id = 100 AND status >= 0
FOR UPDATE;

-- 会话 B：执行不同范围，也可能持有重叠间隙锁
START TRANSACTION;
SELECT id FROM t_order_task
WHERE shop_id = 100 AND status <= 1
FOR UPDATE;

-- 回到会话 A：尝试插入重叠间隙，等待会话 B
INSERT INTO t_order_task(order_no, status, shop_id, updated_at)
VALUES ('A011', 0, 100, NOW());

-- 回到会话 B：也尝试插入重叠间隙，形成等待环，触发死锁
INSERT INTO t_order_task(order_no, status, shop_id, updated_at)
VALUES ('A012', 1, 100, NOW());

其中一个事务会收到 ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction。这并不代表数据损坏，而是 InnoDB 的死锁检测主动回滚了代价较小的事务。

索引与 SQL 调优：把锁范围收敛到精确记录 ​

针对上面的故障，核心优化是让写语句通过更合适的索引精确定位，避免大范围扫描。例如批量状态更新应先通过主键或唯一键锁定少量 ID，再按主键更新；如果必须按条件更新，应建立与查询条件匹配的复合索引，并控制批次大小。

sql
-- 先确认执行计划，检查是否使用索引、预计扫描行数
EXPLAIN
UPDATE t_order_task
SET status = 2, updated_at = NOW()
WHERE shop_id = 100
  AND status = 0;

-- 高并发系统更推荐：先查主键，再小批次按主键更新
SELECT id
FROM t_order_task
WHERE shop_id = 100
  AND status = 0
ORDER BY id
LIMIT 100;

-- 用上一步得到的 ID 精确更新，锁范围最小
UPDATE t_order_task
SET status = 2, updated_at = NOW()
WHERE id IN (1, 2);

如果业务经常按店铺、状态、更新时间筛选，应结合选择性与排序设计复合索引，而不是盲目给每个字段建单列索引：

sql
-- 适合“按店铺与状态筛选，再按时间排序分批”的访问模式
ALTER TABLE t_order_task
ADD INDEX idx_shop_status_id (shop_id, status, id);

-- 若更新条件包含时间范围，可评估以下索引，避免在聚簇索引上回表过滤过多行
ALTER TABLE t_order_task
ADD INDEX idx_shop_status_time (shop_id, status, updated_at);

需要强调：索引不是越多越好。新增二级索引会降低插入、更新性能，也会占用更多空间。上线前应使用真实数据量验证 EXPLAIN 的 key、rows、type，确认优化器选择的是预期索引。

排查思路：从现象到锁根因 ​

线上遇到锁问题时，建议按固定路径排查，而不是直接重启连接池：

  1. 看等待链路：通过 SHOW PROCESSLIST、information_schema.innodb_trx、innodb_lock_waits 找到阻塞者与等待者，记录 SQL 文本、事务开始时间、等待时长。
  2. 看执行计划：对阻塞 SQL 执行 EXPLAIN，重点确认是否走了正确索引、是否出现全表扫描或索引扫描行数过大。
  3. 看死锁日志：在 SHOW ENGINE INNODB STATUS 中关注 LATEST DETECTED DEADLOCK，识别两个事务持有锁、等待锁的索引名称和锁模式。
  4. 看事务边界：确认事务中是否混入了远程调用、消息发送、人工确认等慢操作，长事务会持续持有间隙锁。
  5. 做最小复现：在测试库用相同隔离级别、相同索引、近似数据分布复现，再验证索引或 SQL 修改是否消除锁等待。

避坑总结 ​

坑一：以为 UPDATE 只锁满足条件的行 ​

常见现象：更新 10 条记录，却阻塞了同店铺的新订单插入。原因是 RR 下范围扫描使用 Next-Key Lock，不仅锁记录，还锁记录前间隙，且可能扫描到第一条不满足条件的记录。修正方法：先用 EXPLAIN 确认访问路径，再用主键或唯一索引做小批次精确更新，避免在业务高峰期执行无界范围更新。

坑二：把 SELECT FOR UPDATE 当成普通查询 ​

常见现象：报表式的 SELECT ... FOR UPDATE 在事务中扫描大量索引，随后接口超时，插入全部排队。FOR UPDATE 会加排他锁，范围扫描同样可能带来间隙锁。修正方法：只读查询不要加 FOR UPDATE；必须锁定时，使用主键 IN 精确锁定，并保证事务尽快提交。

坑三：死锁后只记录错误不重试 ​

常见现象：应用日志出现 1213 死锁，但请求直接返回失败，用户重复提交造成更多写冲突。InnoDB 回滚死锁事务是正常保护机制。修正方法：对幂等写操作加入短暂退避重试，例如等待 20ms、50ms、100ms 后重新执行；同时保证一条事务内的多表、多行更新尽量按固定顺序访问。

坑四：长事务让间隙锁长期不释放 ​

常见现象：锁等待在低峰期也持续存在，杀掉某个 Sleep 会话后瞬间恢复。通常是事务开始后执行了外部 HTTP 调用或批量处理，未及时提交。修正方法：缩短事务，把外部调用移到事务外；监控 innodb_trx 中运行时间超过阈值的事务，并设置合理的 innodb_lock_wait_timeout。

间隙锁本身不是 MySQL 的“缺陷”，而是 RR 隔离级别用于防止幻读的机制。真正的问题往往来自不精确的索引、过大的扫描范围和过长的事务。把这三点收敛好，绝大多数范围更新导致的锁放大与死锁都能在上线前被发现并规避。

免责声明

本工具与文章内容仅用于学习测试场景，优化建议仅供参考，请勿直接用于生产环境，由此产生的风险自行承担。

本文来自开发者日记（devdiary.cn）· 记录开发者实战笔记与免费在线工具箱

免责声明

本工具与文章内容仅用于学习测试场景，优化建议仅供参考，请勿直接用于生产环境，由此产生的风险自行承担。