使用FOR UPDATE SKIP LOCKED时如何确保仅锁定单行？

问题场景 #

原始UPDATE语句如下：

UPDATE queue_messages
SET status = 'leased'
WHERE id = ANY(
    SELECT id FROM queue_messages
    WHERE status = 'pending'
    ORDER BY id ASC
    LIMIT 1
    FOR UPDATE SKIP LOCKED
)

通过EXPLAIN查看执行计划，发现子查询在执行LIMIT 1之前就锁定了行：

->  Nested Loop
         ->  Seq Scan on queue_messages
         ->  Subquery Scan on sub [...] loops=11
               ->  Limit [...] rows=2
                     ->  LockRows
                           ->  Sort
                                 ->  Seq Scan on queue_messages

疑问：这是否意味着有不止一行被锁定？如何确保只锁定一行？

尝试了以下写法，但执行计划中没有出现LockRows：

WITH _queue_ids AS (
    SELECT id FROM queue_messages
    WHERE status = 'pending'
    ORDER BY id ASC
    LIMIT 1
) UPDATE queue_messages
SET status = 'leased'
WHERE id = ANY(SELECT id FROM _queue_ids FOR UPDATE SKIP LOCKED)

解答 #

1. 是否有不止一行被锁定？ #

是的。原始写法中，FOR UPDATE SKIP LOCKED放在ANY的子查询里，PostgreSQL的执行逻辑是先对所有status='pending'的行做排序，然后对整个排序后的结果集执行LockRows操作，之后再应用LIMIT 1返回结果。这意味着排序后的所有行都会被尝试锁定，只是最终只返回1行，其他被锁定的行因SKIP LOCKED被跳过，但锁定动作已经发生，存在多余锁定。

2. 为何尝试的WITH写法没有LockRows？ #

因为你把FOR UPDATE SKIP LOCKED放在了引用CTE的子查询中，而CTE本身的查询没有加锁逻辑。此时锁的时机完全错误，根本没对目标行产生锁定效果，所以执行计划中看不到LockRows。

3. 确保只锁定一行的正确写法 #

有两种可靠方式，核心是让FOR UPDATE SKIP LOCKED直接和LIMIT 1、排序逻辑绑定，在锁定阶段就只处理目标行：

方式一：带锁定逻辑的CTE

WITH locked_row AS (
    SELECT id
    FROM queue_messages
    WHERE status = 'pending'
    ORDER BY id ASC
    LIMIT 1
    FOR UPDATE SKIP LOCKED
)
UPDATE queue_messages
SET status = 'leased'
FROM locked_row
WHERE queue_messages.id = locked_row.id;

方式二：UPDATE结合锁定子查询

UPDATE queue_messages
SET status = 'leased'
FROM (
    SELECT id
    FROM queue_messages
    WHERE status = 'pending'
    ORDER BY id ASC
    LIMIT 1
    FOR UPDATE SKIP LOCKED
) AS locked_row
WHERE queue_messages.id = locked_row.id;

这两种写法的逻辑是：先在子查询/CTE中完成“筛选符合条件的行→排序→锁定1行→跳过已锁行”的操作，再基于锁定的ID执行UPDATE。PostgreSQL会优化这个流程，锁定第一行后就停止锁定后续行，最终只会锁定目标的1行。

内容的提问来源于stack exchange，提问作者Ugo Ducharme