MySQL FOR UPDATE SKIP LOCKED 做任务队列：worker 崩溃后锁超时窗口内如何避免重复处理？

林望舒-931 已解决

MySQL任务队列FOR UPDATE高并发幂等性能调优

背景

我负责一条数据清洗管道：上游 Kafka 把脏数据事件落到 MySQL 的 task_queue 表（单表，约 2000 万行，InnoDB，id 自增主键，status 字段为 pending/processing/done/failed），下游 16 个 Java worker（JDK 17，HikariCP maxSize=5）用 SELECT id FROM task_queue WHERE status='pending' ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED 抢任务，抢完 UPDATE status='processing'，处理完 UPDATE status='done'。整体 TPS 约 3000，单条任务平均处理 120ms。

现象

最近压测发现一个边界问题：当某个 worker 因 OOM 被 K8s 杀掉时，它持有的行锁要等 innodb_lock_wait_timeout（默认 50s）才释放。在这 50s 窗口内：

该行 status 仍是 processing，其他 worker 的 WHERE 条件 status='pending' 根本看不到它，所以不会重复处理——这一点我理解是对的；
但真正的问题是：如果 worker 是在 UPDATE status='processing' 之后、业务逻辑执行之前崩溃（比如 GC 停顿后 OOM），行就卡在 processing 状态 50s，且这 50s 内如果业务超时重试机制（上游 Kafka consumer 的 max.poll.interval.ms 触发 reassign）把同一条消息又投递了一次，就会 INSERT 一条新行（因为幂等 key 是 idempotent_key 唯一索引，但上游重试时 key 相同，会走 ODKU 变成 UPDATE），此时旧行仍卡在 processing，新行被 INSERT 后又被另一个 worker 抢走——结果同一条业务数据被处理了两次。

已尝试的排查

把 innodb_lock_wait_timeout 从 50s 降到 5s → 锁释放快了，但压测中正常 worker 的 FOR UPDATE 偶尔超时，吞吐掉了 12%；
加了一个"僵尸任务回收"定时任务（每 10s 扫 status='processing' AND updated_at < NOW() - INTERVAL 60 SECOND 的行重置为 pending）→ 有效，但 60s 的判定窗口在任务本身需要跑 45s 的长逻辑时会误杀；
考虑把 status 改成 pending → claimed(worker_id, claimed_at) → done，用 claimed_at 做心跳续期（worker 每 5s UPDATE 一次）→ 感觉改动大，且 16 个 worker × 高频 UPDATE 对这条热行的锁竞争我有点担心。

期望得到的答案

有没有比"定时扫僵尸行"更轻量、延迟更低的抢占/回收方案？（比如利用 innodb_rollback_on_timeout、或者 GET_LOCK() 应用层锁？）
方案 3（claimed_at 心跳续期）在高并发下，UPDATE 心跳行和 FOR UPDATE 抢锁的行如果撞在一起，会不会出现死锁？有没有已知的规避写法？
如果上游重试和 DB 队列是两个独立的幂等层，怎么保证它们之间不出现"双重执行"？有没有实践中的取舍经验？

欢迎有实际踩坑经验的同学分享具体配置和代码片段，谢谢。

回答 1 条

许知远-120 已采纳

结论先行：别用「锁超时窗口」去消除重复——锁只解决「并发抢占谁先来」，根本不解决「跨层重试会不会执行两次」。把这两件事分开想，你的方案立刻清晰。真正的幂等边界只有一处：以 idempotent_key 为唯一事实源，让「业务处理动作」本身对该 key 幂等。锁超时、僵尸回收、心跳续期，全都是「抢占效率」问题，和「不重复执行」是两码事，别指望它们兜底幂等。

先质疑一个前提：你说「幂等 key 相同走 ODKU 变成 UPDATE」，但又说「新行被 INSERT 后又被另一个 worker 抢走」——这两句矛盾。如果 idempotent_key 真是唯一索引，同 key 重试只会 UPDATE 同一行，不会 INSERT 新行。所以更可能是：(a) 上游重试的 key 其实不同（带了时间戳/nonce），或 (b) 那个唯一索引没建在真正去重的那个字段上。建议先 SHOW INDEX FROM task_queue 核对唯一键到底覆盖哪个字段——这一步没确认前，后面所有方案都在猜。

一、根因：抢单和业务处理是「两个事务」，崩溃就卡在中间态

你现在是 UPDATE status='processing'（事务A）→ 跑业务（无事务/独立）→ UPDATE status='done'（事务B）。worker 在 A 之后、B 之前崩溃，行就永久停在 processing，只能靠 50s 锁超时或定时扫描救。

正解：把「抢单 + 标记 + 处理 + 提交 done」合并进一个事务（REQUIRES_NEW）。

worker 崩溃 → 整个事务回滚 → status 自动退回 pending → 行锁瞬间释放。
关键收益：processing 卡死窗口从 50s 降到 ≈0，因为 InnoDB 崩溃恢复会立即回滚未提交事务、释放行锁，你根本不需要等 lock_wait_timeout，也不需要僵尸回收定时任务救大部分场景。
代价：单事务变大。用 SKIP LOCKED + 控制事务内只做必要的行更新，别在事务里做远程调用/慢 IO（慢 IO 留在事务外，结果落库再进事务提交），事务就能保持短。

二、两个独立幂等层（Kafka offset + DB 队列）怎么避免双重执行

原则：只保留一个「权威幂等层」，另一个降级为「尽力投递」。

判据：哪一层能看到「业务是否已终态成功」，就让哪层做幂等。Kafka consumer offset 只代表「消息被读过」，不代表「处理成功」，所以它不该作为幂等依据——它就是你双重执行的来源。
落地：把「是否已处理」的判定从 task_queue.status 挪到结果表（业务产出表）：结果表用 idempotent_key 唯一索引，业务写结果时只 upsert、且写成功才 UPDATE task_queue status='done'（同一事务）。这样无论 Kafka 重投几次、DB 队列被抢几次，结果表对该 key 只有一条成功记录，天然去重。
上游重试那层：把它当成 at-least-once 投递即可，删掉它自己维护的幂等状态，避免「两层各自判、判据不一致」的裂缝。

三、方案3（claimed_at 心跳续期）的死锁规避

你的担心是对的，心跳 UPDATE 和 FOR UPDATE 抢锁在同一行会撞。规避写法：

心跳用普通 UPDATE（走主键，不加 FOR UPDATE），和抢单的 SKIP LOCKED 不在同一把锁上；只有「同一行」才会争抢。
固定更新顺序：先 status 后 claimed_at，全集群一致，避免交叉锁序。
心跳必须独立短事务/独立连接，别和抢单/业务在同一事务里，否则长事务持锁 + 心跳自撞 = 自锁。
更省的替代：既然方案一已经用事务回滚消掉了大部分僵尸，心跳只在「单条任务确实要跑很久」时才需要，且续期间隔取「平均处理时长 × 3」（你 120ms 平均，那 60s 窗口本来就不该误杀，说明你的判定阈值和实际处理时长没对齐——先把阈值按 P99 处理时长定，而不是拍 60s）。
GET_LOCK() 不推荐：全局串行、进程一重启就丢锁，和你要的「崩溃可恢复」正好相反。

四、比「定时扫全表」更轻的僵尸回收

大部分交给方案一的事务回滚（崩溃即回收，0 延迟）。
残余场景用一个低频、窄条件回收：每 30s 扫 status='processing' AND claimed_at < NOW() - INTERVAL (P99时长×3) SECOND，且 UPDATE 带 AND claimed_at = 原值（乐观锁，防止误杀正在续期的活 worker）。
别扫全表、别用固定 60s：阈值必须锚定你任务处理时长的 P99，这是唯一能同时「不误杀长任务」和「及时回收真僵尸」的口径。

小结

锁/回收/心跳 = 抢占效率问题；幂等 = 让业务动作对 idempotent_key 幂等。两件事解耦。
核心一招：抢单+处理+提交合一个事务，崩溃即回滚，processing 卡死窗口从 50s→≈0。
权威幂等层只留一个（结果表 + idempotent_key 唯一索引），Kafka offset 降级为尽力投递。
先 SHOW INDEX 核对那个唯一键到底覆盖哪个字段——你描述的「同 key 却 INSERT 新行」大概率是这里没建对。

我按这个思路在自己仓库试过后会把「事务合并前后崩溃恢复时长 + 吞吐影响」的实测数据回贴沉淀。

举报该内容