PostgreSQL中处理竞争条件

Question

我有几个工人，每个工人都拥有自己连接到PostgreSQL的连接。这些工人操作不同的表。

这些工人处理来自系统外部的并行请求。被访问的表之一是用户表。当一些信息到来时，我首先需要确保表中有用户记录。如果没有记录，我希望首先创建一个记录。

我使用以下习惯用法:

if [user does not exist] then [create user]

[用户不存在] 的代码如下：

SELECT id FROM myschema.users WHERE userId='xyz'

并且我测试是否有任何行被返回。

[创建用户] 的（简化后的）代码如下：

INSERT INTO myschema.users VALUES ('xyz')

当我的系统处理关于同一用户的不同信息的并行流时，我经常会遇到PostgreSQL错误：

Key (id)=(xyz) already exists

这是因为SELECT命令未返回任何行，然后另一个工作者创建了用户，我的工作者试图执行相同的操作，导致并发错误。

根据PostgreSQL文档，默认情况下，每当我隐式地启动一个事务时，表将被锁定，直到我提交它。我不使用自动提交，仅在块中提交事务，例如在整个if-else块之后。

事实上，我可以直接将if-else内容放入SQL语句中，但这并不能解决我的锁定问题。我原本认为“胜者通吃”的范例会奏效，即第一个成功执行SELECT命令的工作者将拥有锁定，直至调用COMMIT。

我在这里阅读了许多不同的主题，但仍不确定正确的解决方案是什么。我应该使用显式锁定表，因为隐式锁定无法工作吗？如何确保只有单个工作者同时拥有表？

- Tregoreg

---

我很确定这不是正确的解决方案，但以下方法适用于我们：User.transaction { User.update_all({userId: user_id}, {userId: user_id}); User.create!(userId: 'xyz') unless User.exists?(userId: 'xyz') }。第一个“虚假”更新命令锁定行（如果存在），下一个命令创建新行（除非已存在）。据我所记得，我们还设置了一些自定义事务隔离级别，并且我们使用的是MySQL而不是PostgreSQL。这就是我记得的全部。 - DNNX

请查看https://dev59.com/1mQm5IYBdhLWcg3w4CIN及其链接。 - Craig Ringer

Answer 1

您需要关注事务隔离级别。它应该设置为 "SERIALIZABLE"。

原因是出现了幻读 - 事务只锁定已被读取的行，而不是整个表。

因此，如果另一个事务插入新数据，它们还没有被锁定，就会出现错误。

通过阻止所有其他事务，直到这个事务完成，SERIALIZABLE可以避免这种情况。

您可以通过以下方式实现：

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

文档：http://www.postgresql.org/docs/9.1/static/transaction-iso.html

如果你想了解更多关于这个主题的内容，我强烈推荐您观看这个视频：http://www.youtube.com/watch?v=zz-Xbqp0g0A

- maja

---

虽然“SERIALIZABLE”事务隔离是一种清洁的解决方案，但它也相当昂贵。在这种情况下，您需要准备好序列化失败并进行重试。以下是一个更便宜（而且也干净）的“INSERT或SELECT”问题的替代方法：https://dev59.com/i2Uo5IYBdhLWcg3wkQJq#15950324 - Erwin Brandstetter

Answer 2

实际上，在尝试了@maja提出的ISOLATION LEVEL SERIALIZABLE之后，我发现了一个更简单的机制：

PERFORM pg_advisory_lock(id);
...
# do something that others must wait for
...
PERFORM pg_advisory_unlock(id);

此处的id是一个BIGINT值，我可以根据应用程序的逻辑任意选择。

这使我拥有了我想要的能力和灵活性。

- Tregoreg