更新竞赛条件Postgres的位置（已读已提交） [英] Update where race conditions Postgres (read committed)

问题描述

我正在尝试编写一个查询，该查询仅在用户未打开两个以上有效声明的情况下才将声明表中的行更新为活动状态。因此，对于数据完整性而言，非常重要的一点是，用户从未在任何给定时间都不会打开两个以上的主动声明。

I'm trying to write a query that updates a row in the "claim" table to an active status only if a user doesn't have more than two active claims open already. So it's very important for data integrity that a user never has more than two active claims open at any given time.

在并发环境中进行查询，因此两个进程可能同时执行此查询。我也在默认的已提交读操作 隔离级别下运行它。

I'm running this query in a concurrent environment, so it's possible that two processes execute this query at the same time. I'm also running it under the default Read Committed isolation level.

我想知道是否由于子选择和更新子句之间存在竞争条件，用户可能在某个时候打开两个以上的主动声明。

I'm wondering if I'm open to the possibility that a user could have more than two active claims open at some point, due to a race condition between the subselect and the update clause.

请注意，此查询的性能并不像数据完整性那么重要。

On the same note, performance isn't nearly as important for this query as data integrity is.

update claim
set is_active = '1'
where claim.id = %s
and (2 > (select count(*)
          from claim as active_claim
          where active_claim.user_id = %s
          and active_claim.is_active = '1'))

推荐答案

是的，这绝对有可能导致两个以上的活动声明，因为并发事务无法看到彼此的更改，因此两个或多个并发执行都将看到2个声明，并且都将继续更新其目标声明以做出他们活跃。

Yes, it's absolutely possible that this could result in more than two active claims, because concurrent transactions can't see each others' changes, so two or more concurrent executions would both see 2 claims and both proceed to update their target claims to make them active.

查看相关内容：数据库事务是否可以防止竞争情况。

最简单的选择是：

BEGIN;
LOCK TABLE claim IN EXCLUSIVE MODE;
UPDATE ...
COMMIT;

...但这是一个非常沉重的解决方案。

... but that's a pretty heavy-weight solution.

假设您有一个表 user 声明的所有者，您应该：

Assuming you have a table user for the owner of the claims, you should instead:

SELECT 1 FROM user WHERE user_id = whatever FOR UPDATE

在同一事务中，然后运行 UPDATE 。这样，您将对用户持有排他锁，其他 SELECT ... FOR UPDATE 语句将阻塞您的锁。此锁还将阻止 UPDATE 到用户并删除。它将不阻止没有 FOR UPDATE 或<$的普通用户 SELECT c $ c> FOR SHARE 子句。

in the same transaction, before running your UPDATE. That way you'll hold an exclusive row-lock on the user and other SELECT ... FOR UPDATE statements will block on your lock. This lock will also block UPDATEs to and deletes of the user; it will not block plain SELECTs of the user without a FOR UPDATE or FOR SHARE clause.

请参见 PostgreSQL手册中的明确锁定。

另一种方法是使用 SERIALIZABLE 隔离； PostgreSQL 9.2及更高版本具有事务相关性检测功能，在上面给出的示例中，这将导致所有冲突事务（只有一个冲突事务）中止并发生序列化失败。因此，您的应用必须记住它在启动事务时试图执行的操作，并且能够捕获错误，检测到它们是序列化失败，并在序列化失败后重试。

An alternative is to use SERIALIZABLE isolation; PostgreSQL 9.2 and newer have transaction dependency detection that would cause all but one of the conflicting transaction to abort with a serialization failure in the example you give above. So your app has to remember what it tried to do when it starts a transaction and be able to trap errors, detect that they're serialization failures, and re-try it after a serialization failure.

请参见PostgreSQL手册中的事务隔离

See transaction isolation in the PostgreSQL manual.

有时没有好的候选对象可以进行行锁定，由于某种原因或其他可序列化的隔离措施无法解决该问题或由于其他原因而无法使用。

Sometimes there's no good candidate object to take a row lock on, and for some reason or another serializable isolation won't solve the issue or isn't usable for other reasons. That's not the case for you, this is just for general information.

在这种情况下，您可以使用PostgreSQL的咨询锁来锁定任意数值。例如，在这种情况下，您将 pg_advisory_xact_lock（active_claim.user_id）。显式锁定章节提供了更多信息。

In such cases you can use PostgreSQL's advisory locks to lock arbitrary numeric values; in this case you'd pg_advisory_xact_lock(active_claim.user_id) for example. The explicit locking chapter has more information.

这篇关于更新竞赛条件Postgres的位置（已读已提交）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！