Postgres UPDATE…LIMIT 1

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

#1 楼

没有并发写访问权限

对CTE（公用表表达式）中的选择进行材料化，并在FROM的UPDATE子句中将其加入。

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 s.server_ip;

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


计划者可以选择生成执行嵌套循环的计划在LIMITing子查询上产生的UPDATEs比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子查询包装在其自己的CTE中，因为CTE实现了，所以在嵌套循环的不同迭代中不会返回不同的结果。


或使用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;

具有并发写入访问权限

假定所有这些的默认隔离级别READ COMMITTED。更严格的隔离级别（REPEATABLE READ和SERIALIZABLE）可能仍会导致序列化错误。请参阅：


选择…，以便在可重复读取事务中锁定更新跳过

在并发写入负载下，添加FOR UPDATE SKIP LOCKED以锁定行以避免竞争情况。 SKIP LOCKED已在Postgres 9.5中添加，有关旧版本，请参见下文。手册：


使用SKIP LOCKED，将跳过所有不能立即锁定的选定行
。跳过锁定的行会提供
数据的不一致视图，因此这不适用于一般用途，但可以用于
避免由于多个使用者访问类似队列的表而导致锁争用。

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;

如果没有任何限定的未锁定行，则该查询中什么也没有发生（没有行被更新），您将得到一个空结果。对于不重要的操作，这意味着您已经完成。

但是，并发事务可能具有锁定的行，但随后没有完成更新（ROLLBACK或其他原因）。为确保进行最终检查，请执行以下操作：

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

SELECT也看到锁定的行。不会返回true的错误消息，仍在处理一或多个行，并且仍可以回滚事务。 （或者同时添加了新行。）稍等一下，然后循环执行两个步骤：（ UPDATE直到没有行退回； SELECT ...）直到得到true。

相关：


Postgres中的原子更新.. SELECT

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（有点令人惊讶）不返回任何行。没发生什么事。当所有事务都想要更新同一行时，这是期望的行为。
但是当每个事务都想要更新下一行时，这不是期望的行为。而且由于我们只想更新任意（或随机）行，所以根本没有等待。

我们可以借助咨询锁来解除阻塞：

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;

这样，尚未锁定的下一行将被更新。每笔交易都有一个新的行可以处理。我从捷克Postgres Wiki获得了此技巧的帮助。

status是任何唯一的id列（或任何具有诸如bigint或int4的隐式强制转换的类型）。

如果咨询锁定同时用于数据库中的多个表，请与int2进行歧义-pg_try_advisory_xact_lock(tableoid::int, id)在这里是唯一的id。
由于integer是tableoid的数量，因此理论上它可以溢出bigint。如果您有足够的偏执狂，请改用integer-为真正的偏执狂留下理论上的“哈希冲突” ...

此外，Postgres可以自由测试(tableoid::bigint % 2147483648)::int的条件。它可以测试WHERE并在pg_try_advisory_xact_lock()之前获取一个锁，这可能导致对不相关的行附加建议性锁，其中status = 'standby'不正确。关于SO的相关问题：


Postgres pg_try_advisory_lock阻止所有记录

通常，您可以忽略此记录。为确保仅锁定合格行，您可以将谓词嵌套在上述CTE中，或者使用status = 'standby' hack嵌套子查询（防止内联）。示例：


将pg_try_advisory_xact_lock（）放入嵌套的子查询中？

（顺序扫描的便宜者）将条件嵌套在OFFSET 0语句中，例如：

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

但是，CASE技巧也会阻止Postgres在CASE上使用索引。如果有这样的索引，则您不需要额外的嵌套：只有合格的行才会在索引扫描中被锁定。

由于不能确定每次调用都使用索引，您可以：

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

status在逻辑上是多余的，但是它可以满足所讨论的目的。

如果命令是命令的一部分长时间的事务，请考虑可以（必须）手动释放的会话级锁。因此，一旦完成锁定行：CASE和pg_try_advisory_lock()，便可以立即解锁。手册：


一旦在会话级别获取，将保持咨询锁，直到明确释放
或会话结束。


相关：


优化Postgres中的并发更新