原子更新。在Postgres的选择(Atomic UPDATE .. SELECT in Postg

我建立了各种各样的排队机制。有需要处理的数据行和状态标志。我使用的是update .. returning条款进行管理：

UPDATE stuff
SET computed = 'working'
WHERE id = (SELECT id from STUFF WHERE computed IS NULL LIMIT 1)
RETURNING *

是嵌套选择部分相同的锁的更新，还是我在这里有一个竞争条件？如果是这样，内部的选择需要一个select for update ？

Answer 1:

尽管欧文的建议可能是（因为你重试交易，如果你得到一个例外，这么长的时间得到正确的行为，最简单的方法SQLSTATE的40001），排队就其性质应用往往有阻塞的机会，采取轮到他们要求更好的工作在队列比PostgreSQL的实施SERIALIZABLE交易，它允许更高的并发性，是更“乐观”碰撞的机会。

在讨论的示例查询，因为它代表，在默认的READ COMMITTED事务隔离级别将允许二者“权利要求”从队列中同一行的两个（或更多个）的并行连接。会发生什么事是这样的：

T1开始，就得到在锁定行UPDATE阶段。
T2在执行时间重叠T1和尝试更新该行。它块未决的COMMIT或ROLLBACK T1的。
T1承诺，在成功地“声称”行。
T2尝试更新该行，发现T1已经有了，找该行的新版本，发现它仍然满足选择基准（这仅仅是id匹配），也“报销”的行。

它可以进行修改，以正常工作（如果你使用的是版本的PostgreSQL允许FOR UPDATE子句中的子查询）。只需添加FOR UPDATE到选择ID子查询的结束，会出现这种情况：

T1开始，现在选择 ID之前锁定的行。
T2而试图选择一个id，待重叠在执行时间和块T1 COMMIT或ROLLBACK T1的。
T1承诺，在成功地“声称”行。
通过时间T2能够读取该行看到ID，它看到，它一直声称，所以寻找下一个可用的ID。

在REPEATABLE READ或SERIALIZABLE事务隔离级别，写冲突将抛出一个错误，你可以捕获并确定是基于SQLSTATE串行化故障，然后重试。

如果您一般要SERIALIZABLE事务，但要避免在排队区试，您可能能够通过使用来实现这一目标的咨询锁。

Answer 2:

如果您是唯一的用户，查询应该罚款。特别地，存在查询本身（外部查询和子查询之间）内没有争用条件或死锁。我引用手册这里：

然而，一个事务决不会和自身冲突。

对于同时使用 ，这个问题可能更复杂。你会在安全方面与SERIALIZABLE交易模式：

BEGIN ISOLATION LEVEL SERIALIZABLE;
UPDATE stuff
SET    computed = 'working'
WHERE  id = (SELECT id FROM stuff WHERE computed IS NULL LIMIT 1)
RETURNING * 
COMMIT;

您需要为串行化故障准备，在这种情况下重试查询。

但我不能完全肯定这是不是矫枉过正。我会问@kgrittn路过。他是并发和序列化交易专家 ..

他做到了。 :)

两全其美

运行在默认的事务模式查询READ COMMITTED 。

对于Postgres的9.5或更高版本使用FOR UPDATE SKIP LOCKED 。看到：

Postgres的更新中... LIMIT 1

对于旧版本的复检条件computed IS NULL明确外UPDATE ：

UPDATE stuff
SET    computed = 'working'
WHERE  id = (SELECT id FROM stuff WHERE computed IS NULL LIMIT 1)
AND   computed IS NULL;

正如@ kgrittn在给他的回答评论建议，这种查询可以想出空，而不必做任何事情，它得到了与并发事务交织（不太可能）的情况下。

因此，它的工作就像在交易模式下的第一个变种SERIALIZABLE ，你将不得不重试-只是没有性能损失。

唯一的问题：虽然冲突是非常不可能的，因为机会之窗就是如此的渺小，它可以在重负载下发生。你不能说肯定是否有留下最后没有更多的行。

如果这也不要紧（如你的情况），你在这里做了。
如果是的话，是绝对肯定的是 ，先从一个多个查询明确锁定你会得到一个空的结果后。如果出现空的，你做。如果没有，继续。
在PLPGSQL它可能是这样的：

LOOP
   UPDATE stuff
   SET    computed = 'working'
   WHERE  id = (SELECT id FROM stuff WHERE computed IS NULL
                LIMIT 1 FOR UPDATE SKIP LOCKED);  -- pg 9.5+
   -- WHERE  id = (SELECT id FROM stuff WHERE computed IS NULL LIMIT 1)
   -- AND    computed IS NULL; -- pg 9.4-

   CONTINUE WHEN FOUND;  -- continue outside loop, may be a nested loop

   UPDATE stuff
   SET    computed = 'working'
   WHERE  id = (SELECT id FROM stuff WHERE computed IS NULL
                LIMIT 1 FOR UPDATE);

   EXIT WHEN NOT FOUND;  -- exit function (end)
END LOOP;

这应该给你两全其美：性能和可靠性。

文章来源: Atomic UPDATE .. SELECT in Postgres