线上一个订单状态回调接口突然开始超时，检查慢查询日志发现一条 `UPDATE orders SET status = 2 WHERE order_no = '...' AND user_id = 123` 的语句执行时间超过 3 秒，但 `order_no` 和 `user_id` 上明明有联合索引。进一步排查发现该表在事务中执行这条 UPDATE 时，锁住了大量不相关的行甚至整个范围，导致其他订单的更新请求被阻塞。问题的根源在于事务隔离级别为 RR（可重复读）时，InnoDB 对没有命中唯一索引或索引范围过大的更新会加间隙锁，而联合索引的顺序与查询条件不匹配时优化器可能选择扫描范围更大的索引。这种情况在外卖、电商等订单表频繁更新的场景中很常见，往往表现为接口偶发超时、锁等待超时甚至死锁。这篇文章从一次真实的锁冲突出发，分析 UPDATE 语句加锁的规则、索引选择对锁范围的影响，以及如何通过调整索引顺序、拆分 SQL、控制事务大小来降低锁冲突概率。

先还原表结构。订单表大致如下（省略无关字段）：

```sql
CREATE TABLE `orders` (
 `id` bigint NOT NULL AUTO_INCREMENT,
 `order_no` varchar(32) NOT NULL,
 `user_id` bigint NOT NULL,
 `status` tinyint NOT NULL DEFAULT 0,
 `amount` decimal(10,2) NOT NULL,
 `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`),
 UNIQUE KEY `uk_order_no` (`order_no`),
 KEY `idx_user_status` (`user_id`,`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
```

回调逻辑在一个事务里先 `SELECT ... FOR UPDATE` 查订单，再根据当前状态决定是否更新：

```sql
START TRANSACTION;

SELECT id, status FROM orders
WHERE order_no = '202406120001' AND user_id = 12345
FOR UPDATE;

-- 业务判断后执行
UPDATE orders SET status = 2
WHERE order_no = '202406120001' AND user_id = 12345;

COMMIT;
```

`order_no` 有唯一索引，理论上应该只锁一行。但问题出在 `user_id` 这个条件上。优化器在评估时发现 `SELECT` 只需要 `id` 和 `status`，而 `idx_user_status` 覆盖了 `status` 列，于是选择了走 `idx_user_status` 按 `user_id` 扫描。`user_id = 12345` 在 RR 隔离级别下，如果该用户只有一个订单，InnoDB 会在 `idx_user_status` 上对该 `user_id` 区间加间隙锁，锁住 `(12345, 0)` 到 `(12345, 2)` 之间的间隙，防止其他事务插入新的订单行。这个间隙范围比想象中大，因为 `status` 列的分布导致间隙跨越了多个状态值。如果同一个用户在短时间内有多个订单回调，这些 UPDATE 就会在间隙锁上互相等待。

验证方式很直接，开两个会话模拟：

```sql
-- 会话 A
START TRANSACTION;
SELECT id, status FROM orders
WHERE order_no = '202406120001' AND user_id = 12345
FOR UPDATE;
-- 不提交

-- 会话 B
START TRANSACTION;
UPDATE orders SET status = 2
WHERE order_no = '202406120002' AND user_id = 12345;
-- 这里会阻塞，等待会话 A 释放间隙锁
```

用 `SHOW ENGINE INNODB STATUS\G` 能看到会话 B 在等待 `idx_user_status` 上的 `LOCK_MODE: X, GAP` 锁。这解释了为什么不同订单号的回调会互相影响——它们共享同一个 `user_id` 的索引区间。

解决思路不是简单地把隔离级别降到 RC 了事，虽然 RC 下间隙锁会消失，但业务上依赖 RR 来保证事务内多次读取一致（比如同一个事务里先查订单再查优惠券）。更稳妥的做法是让 UPDATE 和 SELECT 都明确走唯一索引，避免优化器选错索引。可以在查询里加上 `FORCE INDEX(uk_order_no)`，或者改写条件让 `order_no` 成为唯一驱动列：

```sql
SELECT id, status FROM orders
FORCE INDEX(uk_order_no)
WHERE order_no = '202406120001'
FOR UPDATE;

UPDATE orders SET status = 2
WHERE order_no = '202406120001';
```

`user_id` 只作为业务校验条件放在应用层判断，不参与索引选择。这样加锁范围收敛到唯一索引的一行记录上，间隙锁只影响相邻的 `order_no` 值，不会因为同一个用户的其他订单而阻塞。

另外，事务里 `SELECT ... FOR UPDATE` 和 `UPDATE` 之间如果隔了 RPC 调用或者复杂的业务计算，持锁时间会被拉长，锁冲突概率成倍增加。把状态判断和更新合并成一条带条件的 UPDATE，利用影响行数判断是否成功，能显著缩短持锁窗口：

```sql
UPDATE orders SET status = 2
WHERE order_no = '202406120001' AND status = 1;
-- affected_rows = 1 表示更新成功，0 表示状态已变更或订单不存在
```

回到线上那次故障，最终调整是给回调接口的 SQL 加上 `FORCE INDEX(uk_order_no)`，同时把状态校验从先查后改改成条件更新。上线后慢查询里这条语句消失了，锁等待超时也没有再出现。间隙锁本身不是 bug，它是 RR 隔离级别下保证可重复读的机制，但前提是索引选择符合预期。当优化器出于覆盖索引或统计信息选择了非唯一索引时，UPDATE 的加锁范围就会超出业务预期。排查这类问题，先看执行计划里的 `key` 列，再对照 `SHOW ENGINE INNODB STATUS` 里的锁信息，基本能定位到是哪条索引在放大锁范围。

 推荐查看英文单词：https://www.tianqi92.com/danci/13255.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13256.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13257.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13258.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13259.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13260.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13261.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13262.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13263.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13264.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13265.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13266.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13267.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13268.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13269.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13270.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13271.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13272.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13273.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13274.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13275.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13276.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13277.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13278.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13279.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13280.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13281.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13282.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13283.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13284.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13285.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13286.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13287.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13288.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13289.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13290.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13291.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13292.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13293.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13294.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13295.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13296.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13297.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13298.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13299.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13300.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13301.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13302.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13303.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13304.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13305.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13306.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13307.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13308.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13309.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13310.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13311.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13312.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13313.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13314.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13315.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13316.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13317.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13318.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13319.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13320.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13321.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13322.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13323.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13324.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13325.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13326.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13327.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13328.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13329.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13330.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13331.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13332.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13333.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13334.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13335.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13336.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13337.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13338.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13339.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13340.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13341.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13342.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13343.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13344.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13345.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13346.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13347.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13348.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13349.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13350.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13351.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13352.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13353.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13354.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13355.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13356.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13357.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13358.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13359.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13360.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13361.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13362.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13363.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13364.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13365.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13366.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13367.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13368.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13369.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13370.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13371.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13372.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13373.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13374.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13375.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13376.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13377.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13378.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13379.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13380.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13381.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13382.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13383.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13384.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13385.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13386.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13387.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13388.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13389.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13390.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13391.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13392.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13393.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13394.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13395.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13396.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13397.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13398.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13399.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13400.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13401.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13402.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13403.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13404.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13405.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13406.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13407.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13408.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13409.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13410.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13411.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13412.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13413.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13414.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13415.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13416.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13417.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13418.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13419.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13420.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13421.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13422.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13423.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13424.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13425.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13426.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13427.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13428.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13429.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13430.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13431.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13432.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13433.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13434.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13435.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13436.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13437.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13438.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13439.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13440.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13441.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13442.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13443.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13444.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13445.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13446.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13447.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13448.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13449.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13450.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13451.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13452.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13453.html
 推荐查看英文单词：https://www.tianqi92.com/danci/13454.html

线上一个订单状态回调接口突然开始超时，检查慢查询日志发现一条 UPDATE orders SET status = 2 WHERE order_no = '...' AND user_id = 123 的语句执行时间超过 3 秒，但 order_no 和 user_id 上明明有联合索引。进一步排查发现该表在事务中执行这条 UPDATE 时，锁住了大量不相关的行甚至整个范围，导致其他订单的更新请求被阻塞。问题的根源在于事务隔离级别为 RR（可重复读）时，InnoDB 对没有命中唯一索引或索引范围过大的更新会加间隙锁，而联合索引的顺序与查询条件不匹配时优化器可能选择扫描范围更大的索引。这种情况在外卖、电商等订单表频繁更新的场景中很常见，往往表现为接口偶发超时、锁等待超时甚至死锁。这篇文章从一次真实的锁冲突出发，分析 UPDATE 语句加锁的规则、索引选择对锁范围的影响，以及如何通过调整索引顺序、拆分 SQL、控制事务大小来降低锁冲突概率。

MySQL 中 UPDATE 语句锁住了全表？聊聊 WHERE 条件不当引发的间隙锁问题

线上一个订单状态回调接口突然开始超时，检查慢查询日志发现一条 UPDATE orders SET status = 2 WHERE order_no = '...' AND user_id = 123 的语句执行时间超过 3 秒，但 order_no 和 user_id 上明明有联合索引。进一步排查发现该表在事务中执行这条 UPDATE 时，锁住了大量不相关的行甚至整个范围，导致其他订单的更新请求

数据库

后端

运维

线上订单回调接口偶发超时，根因是MySQL RR隔离级别下UPDATE语句误选非唯一索引，导致间隙锁范围扩大，阻塞同用户其他订单更新。通过强制走唯一索引、合并条件更新，有效收敛锁粒度，解决锁等待与死锁问题。

代码解释

文章

问答

视频

教程

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云智能顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

EdgeOne AI 安全实战专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

聚焦“写作效率、视觉美观与运行性能”三方面进行全面升级，为您提供更高效、稳定的创作环境

社区富文本&Markdown编辑器全新改版上线，欢迎大家体验!

诚挚邀请您参与本次调研，分享您的真实使用感受与建议。您的反馈至关重要，感谢您的支持与参与！

社区新版编辑器体验调研

MySQL 中 UPDATE 语句锁住了全表？聊聊 WHERE 条件不当引发的间隙锁问题-腾讯云开发者社区-腾讯云

MySQL 中 UPDATE 语句锁住了全表？聊聊 WHERE 条件不当引发的间隙锁问题

MySQL 中 UPDATE 语句锁住了全表？聊聊 WHERE 条件不当引发的间隙锁问题

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐