PostgreSQL（读取提交）中如何更新存在竞态条件的数据？

Question

我正在尝试编写一个查询，仅当用户没有超过两个已打开的活动索赔时，将“claim”表中的一行更新为活动状态。因此，对于数据完整性来说，用户永远不能同时拥有超过两个活动索赔是非常重要的。

我在并发环境下运行此查询，因此可能会有两个进程同时执行此查询。我还在默认的读取提交隔离级别下运行它。

我想知道我是否存在这样的可能性：由于子选择和更新子句之间的竞争条件，用户可能在某个时候拥有超过两个活动索赔。

同样注意，对于此查询而言，性能远不及数据完整性重要。

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

- rectangletangle

---

做得好，你考虑到并发而不仅仅是假设“事务”会神奇地解决并发问题。这使你比大多数人都更有优势。（未来，请在你的问题中也包含你的PostgreSQL版本）。 - Craig Ringer

我正在使用9.3作为参考。 - rectangletangle

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语句将在你的锁上阻塞。此锁还将阻止对user的UPDATE和删除操作；它不会阻止没有FOR UPDATE或FOR SHARE子句的用户纯粹的SELECT。

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

SERIALIZABLE隔离级别

另一种选择是使用SERIALIZABLE隔离级别。PostgreSQL 9.2及更高版本具有事务依赖关系检测功能，该功能会导致除冲突事务外的所有事务都以序列化失败而中止，在您给出的示例中。因此，当应用程序启动一个事务时必须记住它尝试做什么，并能够捕获错误、检测到它们是序列化失败，并在序列化失败后重新尝试。

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

咨询锁

有时没有好的候选对象来锁定行，由于某种原因或其他原因，可串行化隔离无法解决问题或不能使用。这不适用于您，仅供一般信息。

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

- Craig Ringer

---

哇，这是一些非常实用的信息。就我的用例而言，在用户表上使用行级锁似乎是最合适的解决方案，可以防止同一用户向其记录中写入冲突信息。再次感谢，这真的解决了我的疑惑。 - rectangletangle

SERIALIZABLE 不完美的情况是什么？ - Tallboy

@Tallboy，一方面是DDL和系统目录操作，另一方面是用户手册中给出的示例。 - Craig Ringer