sql - 在 PostgreSQL 中处理竞争条件

Question

我有几个工作人员，每个工作人员都拥有自己与 PostgreSQL 的连接。 worker 操作不同的表。

工作人员处理来自系统外部的并行请求。正在访问的表之一是用户表。当一些信息来的时候，我首先要确保表中有用户的记录。如果没有记录，我想先创建一个。

我正在使用以下成语:

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

[用户不存在]的代码是:

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

然后我测试是否返回任何行。

[create user] 的(简化)代码是:

INSERT INTO myschema.users VALUES ('xyz')

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

Key (id)=(xyz) already exists

发生这种情况是因为 SELECT 命令不返回任何行，然后另一个工作人员创建用户，而我的任何工作人员都尝试执行相同的操作，从而导致典型的并发错误。

根据 PostgreSQL 文档，默认情况下，每当我隐式启动事务时，只要我不提交，表就会被锁定。我没有使用自动提交，我只提交 block 中的事务，例如在整个 if-else block 之后。

的确，我可以将if-else 的东西直接放到SQL 中，但它并不能解决我一般的锁定问题。我假设“赢者通吃”范例将起作用，并且第一个设法执行 SELECT 命令的工作人员将拥有锁，直到它调用 COMMIT。

我在 SO 阅读了许多不同的主题，但我仍然不确定正确的解决方案是什么。我应该使用表的显式锁定，因为隐式锁定不起作用吗？如何确保在同一时间只有一个工作人员拥有一张 table ？

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