结论先行：UPDATE 的锁范围由「扫描范围」决定，不是由「实际修改行数」决定。RR 隔离级别下，条件列没有索引时，InnoDB 会对扫描路径上的每一行加 next-key lock——我们一条只改了 3200 行的 UPDATE，实际锁了 284 万行，把同表所有 INSERT 和其他人的 UPDATE 全部堵了 50 秒。治理三件套：复合索引收敛扫描范围（首选）、RC 隔离级别去掉 gap lock（要评估）、分批 + 快速失败（兜底）。
一、现象与背景
- 环境：MySQL 8.0.32（8C16G 云主机，NVMe），
innodb_buffer_pool_size=8G，隔离级别 RR，innodb_lock_wait_timeout=50（默认）。 - 表：工单表
t_ticket，2840 万行、5.2GB，索引只有PRIMARY(id)和idx_status(status)（PENDING 占比约 10%，即 284 万行）。 - 肇事 SQL（客服侧的「批量认领」功能，每天 9 点触发）：
UPDATE t_ticket
SET status = 'PROCESSED', closed_at = NOW()
WHERE handler_name = 'zhangsan' AND status = 'PENDING';
handler_name（1200 个取值）没有索引。这条 SQL 实际只匹配 3200 行，但执行期间：
| 指标 | 正常 | 执行期间 |
|------|------|---------|
| 新工单 INSERT P99 | 8ms | 41s |
| 其他客服的 UPDATE | 6ms | 排队 50s |
| Threads_running | 3~5 | 68 |
| ERROR 1205（锁等待超时） | 0 | 当晚 47 次 |
应用侧的直觉是「才改 3200 行，怎么把整张表锁了？」——这个直觉错在把「修改行数」当成了「锁范围」。
二、根因：RR 下 DML 的锁 = 扫描路径上的 next-key lock
- 优化器选
idx_status：status='PENDING'是唯一可用索引，扫描 284 万行 → 回表逐行过滤handler_name。 - RR 的 DML 锁规则：UPDATE 对扫描路径上每一行加 next-key lock（record lock + 前后 gap lock），不管这行最终改不改。这是 RR 防幻读的核心机制：锁住「可能被插入新行」的间隙。
- 结果：284 万行 + 所有间隙全部被锁。并发影响：
- 其他客服的 UPDATE 命中被锁行 → X 锁排队，等满 50s 报 1205；
- 新工单 INSERT 需要在聚簇索引对应 gap 上加 gap lock → 与已持有的 gap lock 冲突 → 同样排队 50s（这就是「只 UPDATE 却堵死 INSERT」的原因）。
- RC 下对比：RC 的 DML 只对实际匹配的行加 record lock（无 gap lock，靠 semi-consistent read），锁范围从 284 万行缩到 3200 行，INSERT 不再被 gap lock 挡住。
定位证据（可复现）
-- 会话 A 执行上面的 UPDATE（大事务未提交），会话 B 查锁：
SELECT trx_id, trx_rows_locked, trx_rows_modified
FROM information_schema.innodb_trx
WHERE trx_mysql_thread_id = <会话A线程号>;
-- 结果：trx_rows_locked = 2,841,203 ← 锁了 284 万行
-- trx_rows_modified = 3,200 ← 只改 3200 行
-- 两个数字的差，就是「扫描锁放大」的直接证据
-- 看谁在等谁：
SELECT * FROM sys.innodb_lock_waits;
-- 或 SHOW ENGINE INNODB STATUS 的 LATEST DETECTED DEADLOCK / TRANSACTIONS 段
三、三种治理（按成本从低到高）
方案 A（首选，成本最低）：复合索引收敛扫描范围
-- handler_name 在前：扫描范围直接 = 该客服名下 PENDING 行（~3200）
ALTER TABLE t_ticket
ADD INDEX idx_handler_status (handler_name, status),
ALGORITHM=INPLACE, LOCK=NONE;
注意列顺序：如果写成 (status, handler_name)，扫描范围仍是 284 万行（先按 status 过滤 10% 再找 handler_name），锁范围没变——复合索引的列顺序直接决定锁范围，这比决定执行计划更值得先想清楚。
改造后实测：trx_rows_locked 从 284 万降到 3200，INSERT P99 回到 9ms，当晚 0 次 1205。
方案 B（要评估）：切 RC
SET GLOBAL transaction_isolation = 'READ-COMMITTED';
收益：所有 DML 的 gap lock 消失，锁范围全面收窄（站内有从 52 次死锁/天降到 0 的完整记录，见引用）。代价：
- 丢失 RR 的幻读保护——如果业务依赖「事务内两次查询结果集行数不变」，必须逐条过；
- RC 并非完全没有 gap lock：INSERT 的 duplicate key 检查、锁冲突时的 S 锁仍然用 gap lock。所以「RC 不锁间隙」是不完整的话术。
- 全局切换影响所有库所有业务，落地成本远高于方案 A，通常作为架构级决策而非单表修复。
方案 C（兜底）：分批 + 快速失败
批量认领功能本身就该分批，单条大 UPDATE 是设计缺陷：
-- 应用侧循环，每批 2000 行，批间 sleep 10ms 让出锁
UPDATE t_ticket SET status='PROCESSED', closed_at=NOW()
WHERE handler_name='zhangsan' AND status='PENDING'
LIMIT 2000;
-- 直到 affected_rows < 2000
配合 OLTP 侧把锁等待压短、快速失败：
SET innodb_lock_wait_timeout = 3; -- 或会话级 SET SESSION
-- 应用侧必须捕获 1205 并重试，否则只是把「慢」变成「报错」
四、踩过的坑（反直觉之处）
EXPLAIN的rows是估算，但锁范围是精确的：开发看到rows≈2840000没当回事，看到「只返回 3200 行」就认定影响小。锁范围取决于扫描路径，trx_rows_locked才是真相。- UPDATE 能堵死无关的 INSERT：gap lock 锁的是「间隙」不是「行」，INSERT 想往被锁间隙里塞新行就排队。现象是「我明明没碰这张表的数据，为什么写不进去」。
- 加了索引不等于锁范围收敛：本次如果开发按「高频过滤列放前面」的直觉建
(status, handler_name)，锁范围依然是 284 万。正确判断标准是：索引前缀要能把扫描范围收敛到目标行数量级。 innodb_lock_wait_timeout调小是双刃剑：默认 50s 意味着连接池里每个被堵线程都占着连接睡满 50s，68 个 Threads_running 直接把连接池打穿。降到 3s 后连接占用从 50s 缩到 3s，但前提是应用侧有 1205 重试逻辑。max_execution_time管不到等锁阶段：等锁走lock_wait_suspend_thread的独立超时，上界是innodb_lock_wait_timeout——这个细节在站内热点行排队的文章里也被专门指出，值得记牢。
五、结论与可复用清单
一句话：RR 下 UPDATE/DELETE 的锁范围 = 扫描范围。条件列无索引时，「改 3200 行」可能等于「锁全表」。先查 trx_rows_locked，再决定加索引、切 RC 还是分批。
| 检查项 | 动作 |
|--------|------|
| 锁范围审计 | 大 DML 执行时查 innodb_trx.trx_rows_locked vs trx_rows_modified，差值 >10x 即扫描锁放大 |
| 索引设计 | 复合索引前缀必须收敛扫描范围到目标行数量级；列顺序按「等值列在前」而非「高频列在前」 |
| 隔离级别 | RC 能消除 DML gap lock，但丢失幻读保护且保留两类 gap lock，属架构级决策 |
| 批量 DML | 必须分批（LIMIT 2000 级）+ innodb_lock_wait_timeout 3~5s + 应用侧 1205 重试 |
| 监控 | 对 Threads_running 突增（>50）和 ERROR 1205 计数配告警，比慢日志更早暴露锁风暴 |