sql - 更新 Postgres 的竞争条件(读取已提交)

Question

我正在尝试编写一个查询，仅当用户没有打开两个以上的有效 claim 时，才将“ claim ”表中的一行更新为有效状态。因此，对于数据完整性而言，用户从不在任何给定时间有两个以上的有效声明是非常重要的。

我在并发环境中运行此查询，因此两个进程可能同时执行此查询。我还在默认的 Read Committed 隔离级别下运行它。

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

同样，对于此查询，性能远不如数据完整性重要。

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'))

Answer 1

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

参见相关:Do database transactions prevent race conditions .

表锁

最简单的选择是:

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

...但这是一个相当重量级的解决方案。

用户对象的行级锁

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

SELECT 1 FROM user WHERE user_id = whatever FOR UPDATE

在同一事务中，在运行您的UPDATE 之前。这样，您将对用户持有独占行锁，而其他 SELECT ... FOR UPDATE 语句将阻塞您的锁。此锁还将阻止 UPDATE 和 user 的删除；如果没有 FOR UPDATE 或 FOR SHARE 子句，它将不会阻止用户的普通 SELECT。

参见 explicit locking in the PostgreSQL manual .

SERIALIZABLE隔离

另一种方法是使用SERIALIZABLE 隔离； PostgreSQL 9.2 及更新版本具有事务依赖性检测，在您上面给出的示例中，这会导致除一个冲突事务外的所有事务因序列化失败而中止。因此，您的应用必须记住它在启动事务时尝试执行的操作，并且能够捕获错误、检测它们是序列化失败，并在序列化失败后重试。

参见 transaction isolation in the PostgreSQL manual .

咨询锁

有时没有合适的候选对象来进行行锁定，并且出于某种原因或其他可序列化隔离不会解决问题或由于其他原因不可用。您的情况并非如此，这只是为了提供一般信息。

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