Postgres 中的原子更新 .. SELECT [英] Atomic UPDATE .. SELECT in Postgres

问题描述

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

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 * 

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

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 of 40001)，与 SERIALIZABLE 事务的 PostgreSQL 实现相比，队列应用程序的性质往往在请求阻塞时更好地工作，以便有机会在队列中轮流，这允许更高的并发性，并且对冲突的可能性更加乐观".

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 已经存在，查找该行的新版本，发现它仍然满足选择标准(即 id 匹配)，并且还声明"了这一行.

• 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:

• T1 启动并在 选择 id 之前锁定该行.
• T2 在执行时间上与 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.

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