Question

举例说:

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进行了核对,发现在应用LMIT 1之前,分局结果被锁定:

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

Does that mean there are more than one row locked? How can I make sure that I lock only one row?

我对此进行了尝试,但我没有在EXPLAIN计划中看到:

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)

Answer 1

LIMIT 1 in the subquery, it is no effect to summary that into an ANY/code>Building. 这使得邮政公司能够准备多种回报价值,从而导致一个更昂贵的查询计划。使用平原<代码>=:

UPDATE queue_messages
SET    status =  leased 
WHERE  id = (
   SELECT id
   FROM   queue_messages
   WHERE  status =  pending 
   -- ORDER  BY id   -- do you even need that?
   LIMIT  1
   FOR    UPDATE SKIP LOCKED
   );

LIMIT 1 and SKIP LOCKED indicate you are looking for the next free row. If you are going to process the table until no pending rows are left, and any next free row is good, omit ORDER BY id. Generates a simpler (and faster) query plan.

问询(有无) 页: 1 甚至您的原件! 手册:

If a LIMIT is used, locking stops once enough rows have been returned to satisfy the limit (but note that rows skipped over by OFFSET will get locked).

我用<代码>EXPLAIN ANALYZE(实际执行<代码>)对Sengres 16进行了测试。 UPDATE! 我总是看到一个单列的锁链和所讨论的任何问题,载于。类似:

 ->  LockRows  (cost=25.91..25.98 rows=6 width=10) (actual time=0.208..0.208 rows=1 loops=1)

rows=6 in this example is only the number of rows the planner estimates it has to visit, based on column statistics. rows=1 is the number of rows actually locked.

最后,我看不出邮政局在拆除无助的<条码>ANY后会计划使用<条码>。

BTW, your EXPLAIN output looks strikingly the same as the one discussed here:

Postgres UPDATE ... LIMIT 1

友情链接