PostgreSQL SKIP LOCKED 如何避免阻塞等待的批量更新

SKIP LOCKED 只在 select … for UPDATE 或 FOR NO KEY UPDATE 的子查询中生效，需用 WITH 或子查询包裹并显式加锁，外层 UPDATE 不加锁；避免无索引过滤、缺失 ORDER BY 导致重复争抢，且无法跳过未提交的隐式锁行。

SKIP LOCKED 在 UPDATE 语句中怎么写才生效

SKIP LOCKED 只在 SELECT ... FOR UPDATE 或 SELECT ... FOR NO KEY UPDATE 的子查询中起作用，不能直接写在 UPDATE 顶层。常见错误是以为可以这样写：UPDATE t SET status = 'done' WHERE id IN (SELECT id FROM t WHERE status = 'pending' LIMIT 10 FOR UPDATE SKIP LOCKED) —— 这其实没问题，但关键在于：外层 UPDATE 本身不加锁，真正加锁和跳过的是子查询里的 SELECT。

实操建议：

• 必须用子查询包裹，且子查询需显式带 FOR UPDATE 或 FOR NO KEY UPDATE + SKIP LOCKED
• 避免在子查询里用非索引字段过滤，否则可能全表扫描并锁大量无关行
• 如果业务允许“非严格 FIFO”，优先用 FOR NO KEY UPDATE（锁粒度更小，不阻塞其他 UPDATE 同一行的非键列操作）

为什么加了 SKIP LOCKED 还会卡住

典型现象是并发执行时某次查询返回空结果，或延迟明显升高。根本原因不是 SKIP LOCKED 失效，而是它只跳过“已被其他事务锁定的行”，不跳过“正在被其他事务修改但尚未提交、且未显式加锁”的行 —— 比如另一个事务刚执行了 UPDATE t SET status = 'processing' WHERE id = 123 但还没 COMMIT，此时你的 SELECT ... FOR UPDATE SKIP LOCKED 仍会等待该行的行级锁释放。

排查与应对：

• 用 pg_stat_activity 查看是否有长事务或未提交的 UPDATE 卡在中间
• 确保所有涉及同一批数据的写操作都统一走带 SKIP LOCKED 的路径，避免混用普通 UPDATE
• 设置合理的 lock_timeout（如 SET lock_timeout = '500ms'），让等待失败可被捕获而非无限挂起

批量更新时 LIMIT 和 ORDER BY 的取舍

用 LIMIT 控制每次取多少行很自然，但若没配 ORDER BY，postgresql 不保证顺序，不同事务可能反复抢同一组 ID，导致某些行长期得不到处理。而加 ORDER BY（尤其非索引列）又可能拖慢查询、引发排序临时文件。

推荐做法：

• 优先在过滤条件字段上建索引（如 status + created_at），再用 ORDER BY created_at LIMIT 10，这样能走索引扫描
• 避免 ORDER BY random() 或函数表达式，它们无法利用索引且强制排序
• 如果业务允许“近似公平”，可用 WHERE id > $last_id ORDER BY id LIMIT 10 做游标分页，比全局排序更轻量

并发安全的完整模板示例

以下是一个生产环境验证过的最小可行模式，用于从待处理队列表中安全取一批任务：

WITH candidates AS (   SELECT id FROM tasks   WHERE status = 'pending'   ORDER BY priority DESC, id   LIMIT 20   FOR NO KEY UPDATE SKIP LOCKED ) UPDATE tasks SET status = 'processing', worker_id = 'w-abc123', updated_at = now() WHERE id IN (SELECT id FROM candidates) RETURNING id, payload;

注意点：

• FOR NO KEY UPDATE 比 FOR UPDATE 更宽松，适合仅改状态类字段
• RETURNING 必须有，否则应用拿不到实际被更新的行，无法继续后续逻辑
• 不要依赖 UPDATE ... RETURNING 的返回顺序做业务判断——它不保证和子查询 ORDER BY 一致

最易被忽略的是：SKIP LOCKED 解决不了长事务阻塞，也解决不了索引缺失导致的锁升级（比如锁整页甚至整个分区）。它只是并发调度的工具，不是万能锁优化器。