更新竞赛条件Postgres的位置（已读）

Question

我正在尝试编写一个查询，仅当用户打开的活动声明不超过两个时，才将“声明”表中的行更新为活动状态。因此，它是用于数据的完整性非常重要的是，用户 永远
有两个以上的主动要求在任何给定的时间打开。

我在并发环境中运行此查询，因此两个进程可能同时执行此查询。我也在默认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个声明，并且都将继续更新其目标声明以使它们积极的。

餐桌锁

最简单的选择是：

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

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

用户对象上的行级锁定

假设您有一张user声明所有者的表格，则应改为：

SELECT 1 FROM user WHERE user_id = whatever FOR UPDATE

在同一笔交易中，在运行您的之前UPDATE。这样，您将在用户上持有排他的行锁，而其他SELECT ... FOR UPDATE语句将在您的锁上阻塞。此锁还将阻止UPDATEs和删除user；如果没有or子句，它将 不会 阻止SELECT用户的plain
s 。FOR UPDATE``FOR SHARE

请参见PostgreSQL手册中的显式锁定。

SERIALIZABLE 隔离

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

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

咨询锁

有时，没有好的候选对象需要进行行锁定，并且由于某种原因或其他可序列化的隔离措施无法解决该问题或由于其他原因而无法使用。事实并非如此，这只是一般信息。

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