Postgres中的原子UPDATE..SELECT

Question

Postgres中的原子UPDATE..SELECT

multithreadingpostgresqlconcurrencyrace-conditiontransaction-isolation

64

我正在构建一种排队机制。有需要处理的数据行和一个状态标志。我使用一个 update .. returning 子句来进行管理：

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

嵌套的 select 部分与更新使用相同的锁定吗？否则，这里是否存在竞争条件？如果是这样，内部 select 是否需要进行 select for update 操作？

- kolosy

3

如果您正在尝试在SQL中构建消息队列，并且这是任何可接受的大量任务，那么我假设您最终要删除已完成作业的行。这些将破坏您的索引，因此请确保在清理完成的任务时进行VACUUM分析，否则性能会变得很慢。您可能希望考虑使用实际的消息队列（如RabbitMQ、ZeroMQ、ActiveMQ等）。 - nairbv

2个回答

31

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

然而，一个事务从来不会与自己发生冲突。

对于并发使用，情况可能更加复杂。您可以使用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 UPDATE … LIMIT 1

对于旧版本，请在外部UPDATE中明确重新检查条件computed IS NULL：

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;

这应该给您最好的两全其美：性能和可靠性。

- Erwin Brandstetter

4

在PostgreSQL 9.1及以后的版本中，使用“可序列化（SERIALIZABLE）”事务确实可以产生正确的行为，但对其实现的新反馈表明，排队应用程序是一种“最坏情况”场景。虽然该技术（称为可序列化快照隔离或SSI）在大多数工作负载下比使用阻塞锁要快得多，但我有一个报告显示，对于某个特定的排队应用程序，性能下降了20％，而报告者（一个重要的PostgreSQL贡献者）能够故意制造更糟糕的情况。因此，您可以尝试使用它，但您可能会发现显式锁定效果更好。 - kgrittn

1

我不敢在没有实际基准测试的情况下对性能做出任何声明，但我预计仅从第二个块中的“UPDATE”开始会有略微更好的性能（可能很小以至于难以测量）。对于连接重新锁定已经锁定的行，有一些优化措施，除非您处于需要重试的情况，否则我认为它不会阻塞。 - kgrittn

在我的特定情况下，这并不是必要的。当进程到达队列的末尾（就它而言）时，它会休眠10分钟然后再次尝试。如果记录落入下一个窗口，那也不是问题。感谢编译。 - kolosy

2

@kolosy：对于这种情况，简单版本中外部查询中重复使用“AND computed IS NULL”应该是最优解。 - Erwin Brandstetter

2

对于9.5及更高版本，只需要使用FOR UPDATE SKIP LOCKED吗？还是段落的其余部分仍然相关？"对于旧版本"块的结尾并不完全清楚。 - undefined

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- kgrittn · Accepted Answer

虽然Erwin的建议可能是获得正确行为最简单的方法（只要您在遇到SQLSTATE为40001的异常时重试事务），但由于队列应用程序的性质，它们倾向于更好地使用请求阻塞来等待机会排队而不是使用PostgreSQL实现的SERIALIZABLE事务，后者允许更高的并发性并且对冲突的机会更加“乐观”。

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

T1启动并锁定UPDATE阶段的行。
T2与T1在执行时间上重叠，并尝试更新该行。它会被阻塞，直到T1的COMMIT或ROLLBACK。
T1提交，已成功“声明”该行。
T2尝试更新该行，发现T1已经更新了该行，并查找该行的新版本，发现它仍然满足选择标准（只是id匹配），因此也“声明”了该行。

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

T1开始并在选择id之前锁定该行。
T2与T1在执行时间上重叠并在尝试选择id时被阻塞，直到T1的COMMIT或ROLLBACK。
T1提交，已成功“声明”该行。
当T2能够读取该行并查看其ID时，它发现该ID已被占用，因此会寻找下一个可用的ID。

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

如果您通常需要SERIALIZABLE事务，但希望在排队区域避免重试，您可能可以通过使用advisory lock来实现。