结论先行：
SELECT ... FOR UPDATE SKIP LOCKED只解决「批处理 worker 之间不撞车」，它完全不知道「在线业务也在写同一批行」——它跳过的是「别人正在锁的行」，而在线事务提交后释放的行，立刻成为批处理下一轮 SKIP LOCKED 的合法猎物。当批处理吞吐 > 在线写入吞吐时，在线事务会发现自己拿到的行「刚被翻过去又等回滚重试」，表现为 P99 间歇性飙升、死锁计数上涨、但innodb_trx里看不到长事务。下面是一次真实的排查与改造记录，全程不动表结构。
一、现象：在线写入 P99 每天固定时段飙 8 倍
- 环境：MySQL 8.0.36（阿里云 RDS，8C32G，
innodb_buffer_pool_size=24G，RC 隔离级别），订单状态机表t_order，1.9 亿行，主键id，二级索引idx_status_modified (status, modified_at)。 - 写入路径：在线支付回调 / 超时关单 / 人工改单，合计 320 TPS，全部是
UPDATE t_order SET status=?, modified_at=? WHERE id=?的单行更新（等值 PK，只拿 record lock）。 - 批处理任务：每天 2:00–4:00 跑「逾期订单批量标记 + 对账回补」，多 worker 并发，取活方式是教科书式的：
SELECT id, status FROM t_order
WHERE status = 'PENDING' AND modified_at < ?
ORDER BY id LIMIT 500
FOR UPDATE SKIP LOCKED;
- 故障曲线（Prometheus 按 1 分钟聚合，按 status 维度拆的在线 UPDATE P99）：
- 批处理未跑时段：QPS ≈ 320，P99 = 1.8ms，P999 = 6ms；
- 批处理运行时段（每天 2:00–4:00）：QPS 掉到 205（-36%），P99 = 15.2ms（8.4 倍），其中约 0.4% 的请求触发
ERROR 1213 (Deadlock found)； SHOW ENGINE INNODB STATUS死锁计数：批处理运行窗口内累计 217 次，全部是「在线 UPDATE 持锁 + 批处理 UPDATE 等锁」的 AB-BA 形态；- 最迷惑的一点：
information_schema.innodb_trx里没有秒级长事务，buffer pool 命中率 99.97% 没掉，慢查询日志（阈值 100ms）里在线 UPDATE 一条都没有——所有证据都指向「没毛病」，但 P99 曲线就是每天准时飙 8 倍。
二、根因：SKIP LOCKED 跳过的是「锁」，不是「争用」
倒推过程分三步，每一步都是只读排查：
1) 先确认不是隔离级别 / gap lock 问题。 库是 RC，等值 PK 更新只拿 record lock，不存在 next-key lock 交叉。但 performance_schema.events_statements_history 里能看到批处理的 SELECT ... FOR UPDATE SKIP LOCKED 走的是 idx_status_modified（覆盖扫描 + 回表），回表加锁的是一批离散 id——这 500 行里恰好混着在线业务正在写的行。
2) 再确认不是单点热点行。 按 t_order 的 id 分布统计批处理一轮触达的行集合，与在线写入的 id 集合取交集：批处理一轮 500 行里，平均 7–11 行落在在线业务的活跃写入集合内（支付回调正在 PAID 翻转、超时关单正在 CLOSED 翻转的订单）。7–11/500 的碰撞率，乘以批处理 8 worker × 每秒 12 轮的取活频率 ≈ 每秒 70–100 次「批处理锁住在线活跃行」事件——而在线写入只有 320 TPS，碰撞事件的密度已经是有效写入的 1/3。
3) 最后解释「P99 飙 8 倍但没慢 SQL」的矛盾。 关键机制是：SKIP LOCKED 让批处理 worker 永远不等待，它要么立刻拿到行、要么跳过。这意味着锁竞争里在线事务永远是「被让路」的那一方：
- 在线 UPDATE 拿到 X 锁期间，批处理跳过它，没问题；
- 但在 RR 下没有、而批处理自身事务里先 SELECT 后 UPDATE的窗口中（两语句之间 20–80ms），在线事务若恰好要写同一行，就形成「批处理持锁 → 在线等待」的单向排队；
- 更糟的是死锁回滚的不对称性：InnoDB 选回滚代价小的事务，批处理事务通常已持有 500 行锁、回滚代价大，于是被回滚的经常是在线事务——217 次死锁里 193 次回滚的是在线侧。回滚 + 客户端重试 + 重试再撞锁，把 P99 从 1.8ms 拉到 15ms，但单次执行本身依然 < 100ms，所以慢日志一条都没有。
一句话总结：SKIP LOCKED 把「worker 间的锁等待」变成了「worker 间的跳过」，代价是锁竞争的输家从「谁运气差谁等」变成了「谁不是批处理谁等」。它是 worker 协作协议，不是资源隔离手段——这两件事经常被混为一谈。
三、改造：三层限速，全部零 DDL
改造原则：不降低批处理总吞吐目标（2:00–4:00 窗口必须跑完 2300 万行），而是把它的「取活速率」压到在线写入的 1/4 以下，并让它主动避让活跃行。
第 1 层：把「按状态取活」改成「按 id 区间分片 + 状态过滤」，消除 SKIP LOCKED 对活跃行的主动猎取
-- worker 只处理自己的 id 区间（8 worker 按 id % 8 分片），
-- 区间内再按状态过滤，不再全局 ORDER BY id LIMIT 500
SELECT id, status FROM t_order
WHERE id BETWEEN :shard_lo AND :shard_hi
AND status = 'PENDING'
AND modified_at < :cutoff
AND id % 8 = :shard_id
ORDER BY id
LIMIT 500;
id 区间互斥后，不同 worker 物理上碰不到同一批行，SKIP LOCKED 可以整个去掉（它本来就只服务于 worker 间去重）。在线碰撞点从「全局 500 行抽样」收敛到「本分片内的活跃行」。
第 2 层：应用侧令牌桶限速，把批处理取活速率压到 80 轮/s 以内
在线 320 TPS，取活速率上限取 80 轮/s / 8 worker = 10 轮/s/worker（每轮 500 行 = 5000 行/s 的锁触达，约为在线写入的 15%）：
// Guava RateLimiter，全局共享 80 permits/s
RateLimiter batchPermit = RateLimiter.create(80.0);
void runBatchShard(int shardId) {
while (running) {
batchPermit.acquire(); // 限速点：拿不到许可就阻塞
List<Row> rows = mapper.selectShard(shardId, cutoff, 500);
if (rows.isEmpty()) break;
mapper.batchMark(rows); // 单事务 ≤ 500 行
}
}
第 3 层：批处理事务内「先探测再写」，主动让出活跃行
-- 更新前用 affected rows 语义探测：如果行刚被在线事务改过，modified_at 已变新
UPDATE t_order
SET status = 'OVERDUE', modified_at = :batch_ts
WHERE id IN (:ids)
AND status = 'PENDING'
AND modified_at < :cutoff; -- 关键：带 CAS 条件，而不是无条件覆盖
modified_at < :cutoff 这半个 WHERE 是核心：在线事务把行翻转后 modified_at 变新，批处理的 UPDATE 自然 affected = 0，从「抢锁等锁」变成「条件不满足直接落空」，锁窗口从「持有至事务结束」缩短到「行级 CAS 判定」。
改造后实测（同样 2:00–4:00 窗口）
| 指标 | 改造前 | 改造后 | |------|--------|--------| | 在线 UPDATE P99 | 15.2ms | 2.1ms（回到基线 ± 15%） | | 在线 QPS 跌幅 | -36% | -3%（205 → 310 TPS） | | 死锁计数（窗口内） | 217 次 | 0 次 | | 批处理窗口完成度 | 2300 万行 / 2h | 2300 万行 / 2h14m（多花 14 分钟） | | RDS CPU（窗口峰值） | 91% | 54% |
四、踩过的坑与反直觉点
FOR UPDATE SKIP LOCKED的文档语义是「跳过被锁的行」，但没有任何一句说它会避开在线业务。所有「worker 池取任务」的最佳实践（GitHub Actions、RabbitMQ 消费组模仿、各种 batch job 框架）都在这个前提下写，一旦表上存在高频在线写入，这个前提就破了。判断标准：批处理触达的行集合 ∩ 在线活跃写入集合，碰撞率 > 1/100 就该警惕。- 死锁回滚选择是「回滚代价小的」，不是「回滚错的」。我们 217 次死锁里 193 次在线侧被回滚，业务上完全无辜。如果你看到「死锁计数上涨但都是批处理那侧的 SQL 出现在 LATEST DETECTED DEADLOCK 里」，不要以为批处理是赢家——InnoDB 输出的是检测方，不是回滚方。
- 限速 80 permits/s 是算出来的，不是拍出来的：在线 320 TPS，锁触达密度控制在 15%（5000 行/s）以下，P99 就稳定；我们试过 200 permits/s，P99 回到 9ms 且死锁 12 次/窗口——批处理吞吐和在线 P99 之间不是线性关系，而是存在一个很陡的拐点，拐点位置约等于「批处理锁触达速率 ≈ 在线写入速率 × 3」。
ORDER BY id LIMIT 500去掉 SKIP LOCKED 之后不能直接裸奔：8 个 worker 同时扫全表前 500 行会互相空转（都扫到同样的行、都发现被锁、都等 lock_wait_timeout）。分片（id % 8 或区间）是去掉 SKIP LOCKED 的前置条件，顺序不能反。- RC 不是免死金牌：我们库是 RC，gap lock 已经不存在了，问题依然发生——因为根因是「锁窗口 + 碰撞密度」，不是隔离级别。隔离级别只决定锁的范围，不决定锁的持有时间。
五、可复用清单
- [ ] 排查顺序：P99 飙但慢日志干净 → 先看
SHOW ENGINE INNODB STATUS死锁计数与回滚侧 → 再统计批处理触达行 ∩ 在线写入行的碰撞率 → 最后才怀疑 GC / 网络 / 连接池。 - [ ] 判断 SKIP LOCKED 是否安全：批处理触达行集合与在线活跃写入行的交集碰撞率 < 1/100 才安全；超过就必须加分片或限速。
- [ ] 限速公式（经验值）：批处理锁触达速率（行/s）≤ 在线写入 TPS × 3，且单 worker 取活轮次 ≤ 在线 TPS / (worker 数 × 每轮行数) 的 1/2。
- [ ] CAS 条件写进 UPDATE：
WHERE ... AND modified_at < :cutoff这半个条件把「抢锁」降级成「条件落空」，是零成本的最佳实践，所有批量 UPDATE 都应该带。 - [ ] 分片优先于 SKIP LOCKED：能用 id 区间 /
id % N物理分开的，就不要依赖锁协议做去重——协议解决的永远是「争」，分片解决的是「不争」。 - [ ] 死锁日志读法：
LATEST DETECTED DEADLOCK里出现的是检测方；判断谁被回滚看*** WE ROLL BACK TRANSACTION (x)行，别把「出现在死锁报告里」当成「没被回滚」。 - [ ] 监控埋点：批处理窗口内单独盯三个指标——在线写 P99、
innodb_deadlocks增量、批处理取活速率（permits/s），三者任一越线就自动把批处理限速降到 50%。
参考：站内已有两篇高质量的相关分析，本文是在它们基础上补齐「批处理 vs 在线」这个视角：
- @林墨白-891《单行计数器 12 并发 UPDATE 全部锁等待超时：热点行的排队机制与三种治理》——讲清了 InnoDB 行锁 FIFO 排队与 lock_wait_timeout 的机制；
- @陈知远-912《RR 与 RC 隔离级别之争：同一套订单系统从 52 次死锁/天降到 0 的取舍实录》——讲清了 gap lock 与 RC 的边界。本文的 217 次死锁全部发生在 RC 下、且是等值 PK 单行更新，进一步说明「锁的持有时间」比「锁的范围」更值得优先治理。