原子UPDATE ..在Postgres中选择 [英] Atomic UPDATE .. SELECT in Postgres

问题描述

我正在建立一个排序机制。有需要处理的数据行和状态标志。我使用更新..返回子句来管理它：

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 .. returning clause to manage it:

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?

推荐答案

虽然Erwin的建议可能是最简单的方式来获得正确的行为（只要你重试你的事务，如果你得到一个异常 SQLSTATE 40001），排队应用程序的性质往往更好与请求阻塞的机会轮到在队列比PostgreSQL实现 SERIALIZABLE 事务，这允许更高的并发性，并且对于碰撞的机会在某种程度上更加乐观。

While Erwin's suggestion is possibly the simplest way to get correct behavior (so long as you retry your transaction if you get an exception with SQLSTATE of 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 SERIALIZABLE transactions, which allows higher concurrency and is somewhat more "optimistic" about the chances of collision.

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

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

• T1开始并获取到锁定 UPDATE 阶段。


• T2在执行时间与T1重叠，并尝试更新该行。它阻止等待T1的 COMMIT 或 ROLLBACK 。


• T1提交，已成功声明该行。


• T2尝试更新行，发现T1已经拥有，查找行的新版本，发现它仍然满足选择条件 c> c>匹配），并且声明该行。

• T1 starts and gets as far as locking the row in the UPDATE phase.
• T2 overlaps T1 in execution time and attempts to update that row. It blocks pending the COMMIT or ROLLBACK of 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 id matches), and also "claims" the row.

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

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

$ b

• T1在执行时间与T1重叠，并在尝试选择ID时阻止，等待T1的 COMMIT 或 ROLLBACK 。


• T1提交， 声明该行。


• 到T2可以读取行以查看ID时，它会看到它已被声明所有权，下一个可用ID。

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

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

At the REPEATABLE READ or SERIALIZABLE transaction 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.

如果你通常想要SERIALIZABLE事务，但是你想避免在队列区域重试，你可以使用咨询锁。

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.

这篇关于原子UPDATE ..在Postgres中选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！