postgresql Postgres 中的原子更新 .. SELECT

Atomic UPDATE .. SELECT in Postgres

提问by kolosy

I'm building a queuing mechanism of sorts. There are rows of data that need processing, and a status flag. I'm using an update .. returningclause to manage it:

我正在构建各种排队机制。有几行数据需要处理，还有一个状态标志。我正在使用一个update .. returning条款来管理它：

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

Is the nested select part the same lock as the update, or do I have a race condition here? If so, does the inner select need to be a select for update?

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

采纳答案by kgrittn

While Erwin's suggestion is possibly the simplestway to get correct behavior (so long as you retry your transaction if you get an exception with SQLSTATEof 40001), queuing applications by their nature tend to work better with requests blocking for a chance to take their turn at the queue than with the PostgreSQL implementation of SERIALIZABLEtransactions, which allows higher concurrency and is somewhat more "optimistic" about the chances of collision.

虽然 Erwin 的建议可能是获得正确行为的最简单方法（只要您在遇到SQLSTATE40001的异常时重试您的事务），就其性质而言，排队应用程序往往会更好地工作，请求阻塞以便有机会轮到他们队列与SERIALIZABLE事务的 PostgreSQL 实现相比，它允许更高的并发性，并且对冲突的可能性更加“乐观”。

The example query in the question, as it stands, in the default READ COMMITTEDtransaction isolation level would allow two (or more) concurrent connections to both "claim" the same row from the queue. What will happen is this:

问题中的示例查询，在默认READ COMMITTED事务隔离级别中，将允许两个（或更多）并发连接从队列中“声明”同一行。将会发生的事情是这样的：

• T1 starts and gets as far as locking the row in the UPDATEphase.
• T2 overlaps T1 in execution time and attempts to update that row. It blocks pending the COMMITor ROLLBACKof T1.
• T1 commits, having successfully "claimed" the row.
• T2 tries to update the row, finds that T1 already has, looks for the new version of the row, finds that it still satisfies the selection criteria (which is just that idmatches), and also "claims" the row.

• T1 开始并尽可能锁定UPDATE阶段中的行。
• T2 在执行时间上与 T1 重叠并尝试更新该行。它阻塞等待T1的COMMIT或ROLLBACK。
• T1 提交，成功“声明”了该行。
• T2 尝试更新该行，发现 T1 已经拥有该行，查找该行的新版本，发现它仍然满足选择标准（恰好id匹配），并且还“声明”了该行。

It can be modified to work correctly (if you are using a version of PostgreSQL which allows the FOR UPDATEclause in a subquery). Just add FOR UPDATEto the end of the subquery which selects the id, and this will happen:

可以修改它以使其正常工作（如果您使用的 PostgreSQL 版本允许FOR UPDATE子查询中的子句）。只需添加FOR UPDATE到选择 id 的子查询的末尾，就会发生这种情况：

• T1 starts and now locks the row before selectingthe id.
• T2 overlaps T1 in execution time and blocks while trying to select an id, pending the COMMITor ROLLBACKof T1.
• T1 commits, having successfully "claimed" the row.
• By the time T2 is able to readthe row to see the id, it sees that it has been claimed, so it finds the next available id.

• T1 启动，现在在选择id之前锁定该行。
• T2 在执行时间上与 T1 重叠，并在尝试选择 id 时阻塞，等待T1的COMMIT或ROLLBACK。
• T1 提交，成功“声明”了该行。
• 当 T2 能够读取行以查看 id 时，它会看到它已被声明，因此它会找到下一个可用的 id。

At the REPEATABLE READor SERIALIZABLEtransaction isolation level, the write conflict would throw an error, which you could catch and determine was a serialization failure based on the SQLSTATE, and retry.

在REPEATABLE READ或SERIALIZABLE事务隔离级别，写入冲突会抛出错误，您可以根据 SQLSTATE 捕获并确定该错误是序列化失败，然后重试。

If you generally want SERIALIZABLE transactions but you want to avoid retries in the queuing area, you might be able to accomplish that by using an advisory lock.

如果您通常需要 SERIALIZABLE 事务，但又想避免在排队区域中重试，则可以通过使用咨询锁来实现。

回答by Erwin Brandstetter

If you are the only user, the query should be fine. In particular, there is no race condition or deadlock within the query itself (between the outer query and the subquery). I quote the manual here:

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

However, a transaction never conflicts with itself.

但是，事务永远不会与自身发生冲突。

For concurrent use, the matter may be more complicated. You would be on the safe side with SERIALIZABLEtransaction mode:

对于并发使用，事情可能会更复杂。使用SERIALIZABLE事务模式会更安全：

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

You need to prepare for serialization failures and retry your query in such a case.

在这种情况下，您需要为序列化失败做好准备并重试查询。

But I am not entirely sure if this isn't overkill. I'll ask @kgrittn to stop by .. he is theexpert with concurrency and serializable transactions ..

但我不完全确定这是否有点矫枉过正。我会请@kgrittn 过来看看.. 他是并发性和可序列化事务方面的专家..

And he did. :)

他做到了。:)

---

---

Best of both worlds

两全其美

Run the query in default transaction mode READ COMMITTED.

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

For Postgres 9.5 or later use FOR UPDATE SKIP LOCKED. See:

对于 Postgres 9.5 或更高版本，请使用FOR UPDATE SKIP LOCKED. 看：

• Postgres UPDATE … LIMIT 1

• Postgres 更新……限制 1

For older versions recheck the condition computed IS NULLexplicitly in the outer UPDATE:

对于旧版本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;

As @kgrittn's advised in the comment to his answer, this query could come up empty, without having done anything, in the (unlikely) case it got intertwined with a concurrent transaction.

正如@kgrittn 在对他的回答的评论中所建议的那样，在（不太可能的）情况下，这个查询可能会出现空的，没有做任何事情，它与并发事务交织在一起。

Therefore, it would work much like the first variant in transaction mode SERIALIZABLE, you would have to retry - just without the performance penalty.

因此，它的工作方式与事务模式中的第一个变体非常相似SERIALIZABLE，您必须重试 - 只是没有性能损失。

The only problem: While the conflict is very unlikely because the window of opportunity is just so tiny, it can happen under heavy load. You could not tell for sure whether there are finally no more rows left.

唯一的问题是：虽然发生冲突的可能性很小，因为机会之窗太小了，但它可能会在重负荷下发生。您无法确定是否最终没有更多行了。

If that does not matter (like in your case), you are done here.
If it does, to be absolutely sure, start one more query with explicit lockingafter you get an empty result. If this comes up empty, you are done. If not, continue.
In plpgsqlit could look like this:

如果这无关紧要（就像您的情况一样），那么您就在这里完成了。
如果确实如此，请绝对确定，在获得空结果后，再开始一个显式锁定的查询。如果这是空的，你就完成了。如果没有，请继续。
在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;

That should give you the best of both worlds: performance andreliability.

这应该为您提供两全其美的好处：性能和可靠性。