postgresql – Postgres UPDATE … LIMIT 1

UPDATE server_info SET status = 'active' 
WHERE status = 'standby' [[LIMIT 1???]] 
RETURNING server_ip;

没有并发写入

在CTE中实现选择并在UPDATE的FROM子句中连接到它.

WITH cte AS (
   SELECT server_ip          -- pk column or any (set of) unique column(s)
   FROM   server_info
   WHERE  status = 'standby'
   LIMIT  1                  -- arbitrary pick (cheapest)
   )
UPDATE server_info s
SET    status = 'active' 
FROM   cte
WHERE  s.server_ip = cte.server_ip
RETURNING server_ip;

我最初在这里有一个普通的子查询,但是可以回避某些查询计划的LIMIT,因为Feike指出：

The planner may choose to generate a plan that executes a nested loop over the LIMITing subquery,causing more UPDATEs than LIMIT,e.g.:

06001

07002

The way to fix the above was to wrap the LIMIT subquery in its own CTE,as the CTE is materialized it will not return different results on different iterations of the nested loop.

或者使用LIMIT 1的简单情况使用低相关子查询.更简单,更快：

UPDATE server_info
SET    status = 'active' 
WHERE  server_ip = (
         SELECT server_ip
         FROM   server_info
         WHERE  status = 'standby'
         LIMIT  1
         )
RETURNING server_ip;

具有并发性

在并发写入加载下,添加FOR UPDATE SKIP LOCKED以锁定行以避免竞争条件. Postgres 9.5中添加了SKIP LOCKED,旧版本见下文. The manual:

With SKIP LOCKED,any selected rows that cannot be immediately locked
are skipped. Skipping locked rows provides an inconsistent view of the
data,so this is not suitable for general purpose work,but can be
used to avoid lock contention with multiple consumers accessing a
queue-like table.

UPDATE server_info
SET    status = 'active' 
WHERE  server_ip = (
         SELECT server_ip
         FROM   server_info
         WHERE  status = 'standby'
         LIMIT  1
         FOR UPDATE SKIP LOCKED
         )
RETURNING server_ip;

如果没有符合条件的解锁行,则此查询中没有任何反应,并且您得到一个空结果.对于不加批判的操作,这意味着你已经完成了.

但是,对于关键操作,确保进行最终检查：

SELECT NOT EXISTS (
   SELECT 1
   FROM   server_info
   WHERE  status = 'standby'
   );

如果Wile不返回true,则仍在处理一行或多行,并且仍可以回滚事务.稍等一下,然后循环两个步骤：UPDATE直到你没有返回行,SELECT …直到你得到TRUE.

有关：

> Atomic UPDATE .. SELECT in Postgres

在PostgreSQL 9.4或更早版本中没有SKIP LOCKED

UPDATE server_info
SET    status = 'active' 
WHERE  server_ip = (
         SELECT server_ip
         FROM   server_info
         WHERE  status = 'standby'
         LIMIT  1
         FOR UPDATE
         )
RETURNING server_ip;

尝试锁定同一行的并发事务将被阻止,直到第一个释放其锁定.

如果第一个被回滚,则下一个事务将获取锁定并正常进行;队列中的其他人继续等待.

如果第一次提交,则重新评估WHERE条件,如果它不再为TRUE(状态已更改),则CTE(有点令人惊讶地)不返回任何行.什么都没发生.当所有事务都想要更新同一行时,这是所需的行为.
但不是每个事务都想要更新下一行.而且由于我们只想更新任意(或随机)行,所以没有必要等待.

我们可以在advisory locks的帮助下解除这种情况：

UPDATE server_info
SET    status = 'active' 
WHERE  server_ip = (
         SELECT server_ip
         FROM   server_info
         WHERE  status = 'standby'
         AND pg_try_advisory_xact_lock(id)
         LIMIT  1
         FOR    UPDATE
         )
RETURNING server_ip;

这样,未更新的下一行将被更新.每个事务都有一个新的行可以使用.我从Czech Postgres Wiki获得了这个技巧的帮助.

id是任何唯一的bigint列(或任何具有隐式转换的类型,如int4或int2).

如果同时对数据库中的多个表使用建议锁,请使用pg_try_advisory_xact_lock(tableoid :: int,id)消除歧义 – 此处的id是唯一的整数.
由于tableoid是一个很大的数量,它理论上可以溢出整数.如果你足够偏执,请使用(tableoid :: bigint％2147483648):: int – 为真正的偏执者留下理论上的“哈希冲突”……

此外,Postgres可以任意顺序测试WHERE条件.它可以测试pg_try_advisory_xact_lock()并在status =’standby’之前获取锁定,这可能导致对不相关的行进行额外的建议锁定,其中status =’standby’不为true.关于SO的相关问题：

> Postgres pg_try_advisory_lock blocks all records

通常,您可以忽略这一点.为了保证只有符合条件的行被锁定,您可以将谓词嵌套在上面的CTE中,或者将子查询嵌套到OFFSET 0 hack (prevents inlining)中.示例：

> Put pg_try_advisory_xact_lock() in a nested subquery?

或者(顺序扫描更便宜)将条件嵌套在CASE语句中,如：

WHERE  CASE WHEN status = 'standby' THEN pg_try_advisory_xact_lock(id) END

然而,CASE技巧也会使Postgres不使用状态索引.如果这样的索引可用,则不需要额外的嵌套开始：只有符合条件的行才会在索引扫描中被锁定.

由于您无法确定每次调用都使用了索引,因此您可以：

WHERE  status = 'standby'
AND    CASE WHEN status = 'standby' THEN pg_try_advisory_xact_lock(id) END

CASE在逻辑上是多余的,但它服务于所讨论的目的.

如果该命令是长事务的一部分,请考虑可以(并且必须)手动释放的会话级锁.因此,您可以在完成锁定行后立即解锁：pg_try_advisory_lock() and pg_advisory_unlock().The manual:

Once acquired at session level,an advisory lock is held until
explicitly released or the session ends.

有关：

> Optimizing concurrent updates in Postgres