postgresql 当与Postgres 14中锁定的更新跳过一起使用时，不遵守限制1

0kjbasz6 于 2023-03-17 发布在 PostgreSQL

关注(0)|答案(1)|浏览(114)

我使用Postgres实现了一个队列，在这个队列中，我通过更新一行从队列表中获取消息，然后将其返回并删除。
我的查询如下所示：

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
)
RETURNING *

在大多数情况下，这可以正常工作，我只返回一行，并且一行的状态更新为leased。但是，在某些情况下，当对表进行并发写操作时，我会返回多行，通常是表中所有挂起的行，它们的状态也会更新为leased。
我试过用pg_try_advisory_xact_lock(id)和FOR UPDATE SKIP LOCKED组合添加一个锁，但似乎没有什么帮助，如果我删除FOR UPDATE SKIP LOCKED，我不再得到多个行，但这样做对并发租用消息是不安全的。
我错过了什么？

postgresql

来源：https://stackoverflow.com/questions/75755863/limit-1-not-respected-when-used-with-for-update-skip-locked-in-postgres-14

1条答案

按热度按时间

jgwigjjp1#

WITH-query（公用表表达式/ CTE）应该可以工作，方法如下

WITH _queue_ids 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'
WHERE id = ANY(SELECT id FROM _queue_ids)
RETURNING *

当查询计划器从https://dba.stackexchange.com/a/69497创建一个在limit-subquery上具有嵌套循环的计划时，您的问题似乎出现了：
计划器可以选择生成一个在LIMITing子查询上执行嵌套循环的计划，从而导致UPDATE多于LIMIT，例如：
Update on buganalysis [...] rows=5

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

解决上述问题的方法是将LIMIT子查询 Package 在它自己的CTE中，因为CTE是具体化的，它不会在嵌套循环的不同迭代中返回不同的结果。

赞(0）回复(0）举报 2023-03-17

我来回答

postgresql 当与Postgres 14中锁定的更新跳过一起使用时，不遵守限制1

1条答案

相关问题

热门标签

最新问答