如何在PostgreSQL中利用子查询实现高并发下的行锁定

for update在子查询中完全无效，postgresql明确禁止且报错；锁仅由外层select或update显式声明生效，正确做法是用update...from或where in结合for update skip locked实现原子加锁更新。

如何在postgresql中利用子查询实现高并发下的行锁定_使用for update嵌套

FOR UPDATE 不能直接嵌套在子查询中生效——这是最常被误用的点。子查询里的 FOR UPDATE 会被忽略，锁只作用于外层主查询的行，且仅当外层语句显式声明锁定时才成立。

`FOR UPDATE` 在子查询中完全无效

PostgreSQL 明确禁止在子查询（包括 WHERE、FROM 或 SELECT 子句中的子查询）里使用 FOR UPDATE、FOR SHARE 等锁定子句。
一旦出现，会报错：

ERROR:  FOR UPDATE is not allowed in subqueries

这不是版本限制，而是语法硬性约束（9.5 至 18 均如此）
即使子查询返回单行，(SELECT id FROM tasks WHERE status = 'pending' LIMIT 1) FOR UPDATE 这种写法也非法
IN、=、EXISTS 等子查询中都不可带锁

所以，想“先查 ID 再锁行”，必须拆成两步或改用原子方式。

正确做法：用 `SELECT ... FOR UPDATE SKIP LOCKED` 替代子查询加锁

高并发任务分发场景下，典型需求是“取一条未处理、未被他人锁定的任务并立即加锁”。错误做法是：

-- ❌ 错误：子查询里加锁不被允许
UPDATE tasks SET status = 'processing'
WHERE id = (SELECT id FROM tasks WHERE status = 'pending' LIMIT 1 FOR UPDATE);

正确路径是一步完成筛选 + 加锁 + 可选更新：

直接用 SELECT ... FOR UPDATE SKIP LOCKED 获取待处理行
在应用层拿到 ID 后，再执行 UPDATE（需注意中间状态窗口）
或更稳妥地，用 UPDATE ... FROM + SKIP LOCKED（PostgreSQL 9.5+ 支持）

例如：

-- ✅ 原子获取并标记为 processing（推荐）
UPDATE tasks
SET status = 'processing', worker_id = 'w-123'
WHERE id IN (
  SELECT id FROM tasks
  WHERE status = 'pending'
  ORDER BY created_at
  LIMIT 1
  FOR UPDATE SKIP LOCKED
);

外层 UPDATE 会继承子查询中 FOR UPDATE SKIP LOCKED 的锁定行为
SKIP LOCKED 保证不会卡住，多个 worker 并发执行该语句能各自拿到不同行
注意：子查询必须出现在 UPDATE ... FROM 或 WHERE id IN (subquery) 这类支持锁定传播的上下文中

`SKIP LOCKED` 不起作用的常见原因

没开事务：FOR UPDATE SKIP LOCKED 必须在 BEGIN 事务块内才有效，自动提交模式下锁立刻释放
WHERE 条件没走索引：全表扫描时，PostgreSQL 仍会逐行检查锁状态，但性能差、易锁多行；务必确保 status 或组合字段有索引
用了 ORDER BY ... LIMIT 但排序字段无索引：可能导致非预期行被选中，或跳过本应可得的行
和 NOWAIT 混用：FOR UPDATE SKIP LOCKED NOWAIT 是冗余的，SKIP LOCKED 本身已是非阻塞，NOWAIT 会被忽略或报错

子查询仍可配合锁定，但只用于条件计算，不参与加锁

子查询可以安全用于生成过滤条件，只要它不带锁。例如：

-- ✅ 合法：子查询只提供值，锁由外层承担
SELECT * FROM tasks
WHERE status = 'pending'
  AND priority > (SELECT avg(priority) FROM tasks WHERE status = 'done')
ORDER BY priority DESC
LIMIT 1
FOR UPDATE SKIP LOCKED;