Postgres 更新...限制 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;

我最初在这里有一个普通的子查询，但是正如飞克指出的那样，它可以回避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) 仍可能导致序列化错误。看：

• SELECT … FOR UPDATE SKIP LOCKED 锁定在 REPEATABLE READ 事务中

在并发写入负载下，添加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 FROM server_info
   WHERE  status = 'standby'
   );

SELECT也看到锁定的行。即使不返回true，一或多行仍未完成，事务仍可能回滚。（或者同时添加了新行。）等一下，然后循环两个步骤：（UPDATE直到你没有得到任何行回来；SELECT...）直到你得到true.

有关的：

• Postgres 中的原子更新 .. SELECT

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

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（status已更改），则 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那里得到了这个技巧的帮助。

id是任何唯一的bigint列（或任何具有隐式转换的类型，如int4or int2）。

_{如果建议锁同时用于数据库中的多个表，请消除歧义pg_try_advisory_xact_lock(tableoid::int, id)-id在此处是唯一的integer。
既然tableoid是bigint量，理论上可以溢出integer。如果您足够偏执，请(tableoid::bigint % 2147483648)::int改用-为真正的偏执狂留下理论上的“哈希冲突”...}

此外，Postgres 可以自由地WHERE以任何顺序测试条件。它可以在之前测试 pg_try_advisory_xact_lock()并获取一个锁，这可能会导致在不相关的行上产生额外的咨询锁，但事实并非如此。关于 SO 的相关问题： status = 'standby'status = 'standby'

• Postgres pg_try_advisory_lock 阻塞所有记录

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

• 将 pg_try_advisory_xact_lock() 放在嵌套子查询中？

或者（对于顺序扫描更便宜）将条件嵌套在CASE如下语句中：

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

然而，这个CASE技巧也会阻止 Postgres 在status. 如果这样的索引可用，您就不需要额外的嵌套：只有符合条件的行将被锁定在索引扫描中。

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

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

这CASE在逻辑上是多余的，但它服务于讨论的目的。

如果命令是长事务的一部分，请考虑可以（并且必须）手动释放的会话级锁。因此，您可以在完成锁定行后立即解锁：pg_try_advisory_lock()和pg_advisory_unlock(). 手册：

一旦在会话级别获得，建议锁定将一直保持，直到显式释放或会话结束。

有关的：

• 在 Postgres 中优化并发更新