在PostgreSQL中处理竞争条件 [英] Handling race conditions in PostgreSQL
问题描述
我有几个工作人员，每个工作人员都拥有自己与PostgreSQL的连接.工人们用不同的桌子来操纵.
工作人员处理来自系统外部的并行请求.被访问的表之一是用户表.当收到一些信息时，我首先需要确保表中有该用户的记录.如果没有记录，我希望首先创建一个记录.
我正在使用以下成语:
如果[用户不存在]，则[创建用户] [用户不存在] 的代码为:
从myschema.users中选择ID，其中userId ='xyz' 然后我测试是否返回任何行.
[创建用户] 的(简体)代码是:
INSERT INTO myschema.users VALUES('xyz') 当我的系统处理与同一用户有关的不同信息的并行流时，我经常会收到PostgreSQL错误:
密钥(id)=(xyz)已存在 发生这种情况是因为 SELECT 命令不返回任何行，然后另一个工作人员创建了该用户，而我的任何工作人员都试图这样做，从而导致示例性的并发错误.
根据PostgreSQL文档，默认情况下，每当我隐式启动事务时，只要我不提交该表，该表就会被锁定.我没有使用自动提交，而是仅以块形式提交事务，例如在整个 if-else 块之后.
的确，我可以直接将 if-else 东西放到SQL中，但是它通常不能解决我的锁定问题.我以为赢家会全力以赴"范例将起作用，并且第一个设法执行 SELECT 命令的工作人员将拥有这些锁，直到它调用 COMMIT 为止.
我在SO上阅读了许多不同的主题，但是我仍然不确定什么是正确的解决方案.我应该使用表的显式锁定，因为隐式锁定不起作用?如何确保只有一个工人同时拥有一张桌子?
您必须关心事务隔离级别.应当将其设置为" SERIALIZABLE ".
原因是 Phantom Reads -事务不会锁定整个表，而只会锁定已被事务读取的行.
因此，如果另一个事务插入新数据，则它们尚未被锁定，并且会出现错误.
Serializable通过阻止所有其他事务直到完成这一过程来避免这种情况.
您可以通过以下方式进行
SET事务隔离级别可序列化； 文档: http://www.postgresql.org/docs/9.1/static/transaction-iso.html
如果您想了解更多有关此主题的信息，我可以向您推荐以下视频: http://www.youtube.com/watch?v = zz-Xbqp0g0A
I have several workers, each holding its own connection to PostgreSQL. The workers manipulate with different tables.
The workers handle parallel requests from outside the system. One of the tables being accessed is the table of users. When some information comes, I first need to ensure there is a record for the user in the table. If there is no record, I wish to create one at first.
I'm using the following idiom:
if [user does not exist] then [create user]
The code of [user does not exist] is:
SELECT id FROM myschema.users WHERE userId='xyz'
and I test whether any row is returned.
The (simplified) code of [create user] is:
INSERT INTO myschema.users VALUES ('xyz')
When my system handles parallel streams of different information concerning the same user, I often get PostgreSQL error:
Key (id)=(xyz) already exists
It happens because the SELECT command returns no rows, then another worker creates the user, any my worker attempts to do the same, resulting in exemplary concurrency error.
According to PostgreSQL documentation, by default, whenever I implicitly start a transaction, the table becomes locked for as long as I don't commit it. I'm not using autocommit and I only commit the transaction in blocks, e.g. after the whole if-else block.
Indeed, I could put the if-else stuff into SQL directly, but it does not solve my problem of locking in general. I was supposing that "the winner takes it all" paradigm will work, and that the first worker which manages to execute the SELECT command will own the locks until it calls COMMIT.
I've read many different topics here at SO, but I'm still not sure what the right solution is. Should I use explicit locking of tables, because the implicit locking does not work? How can I ensure that only single worker owns a table at time?
You have to care about the transaction isolation level. It should be set to "SERIALIZABLE".
The reason are Phantom Reads - The transaction doesn't lock the whole table, but only the rows which have already been read by the transaction.
So, if another transaction inserts new data, they haven't been locked yet, and the error appears.
Serializable avoids this, by blocking all other transactions, until this one finished.
You can do this via
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
The documentations: http://www.postgresql.org/docs/9.1/static/transaction-iso.html
If you want to learn more about this topic, I can highly recommend you this video: http://www.youtube.com/watch?v=zz-Xbqp0g0A
这篇关于在PostgreSQL中处理竞争条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
