Psycopg2 concurrency issue
Reported by Psycopg website | February 22nd, 2012 @ 09:38 PM
Submitted by: Phani
I am trying to do share a psycopg2 connection between multiple threads. As was mentioned in the docs, I am doing that by creating new cursor objects from the shared connection, whenever I use it in a new thread.
while True: conn.commit()
cur = conn.cursor() thread.start_new_thread(delete,(conn,)) i = 1 while True: cur.execute("INSERT INTO mas(taru,s) values (2,%s)",(i,)) print i i = i +1 conn.commit()
After running, I get output like,
Traceback (most recent call last):
File "postgres_test_send.py", line 44, in
cur.execute("INSERT INTO mas(taru,s) values (2,%s)",(i,)) psycopg2.InternalError: SET TRANSACTION ISOLATION LEVEL must be called before any query
Comments and changes to this ticket
- State changed from new to hold
Strange race condition. However, more recent psycopg versions do without "set transaction isolation level".
What you report is strange, because these versions used to execute a "begin; set isolevel ..." into the same command, I don't know how it would be possible to sneak a commit between the two.
I'd be interested in knowing what would be the behaviour in the recent psycopg version. However I take your example as a pathological example, not really a reasonable behaviour: if you want every insert to be committed you should run the connection in autocommit mode, which will avoid begin, commit and set isolevel. Conversely if you wanted all the insert to be committed together the cnn.commit should be executed after the worker threads have joined.
Closing this ticket as we don't plan to keep on supporting version 2.2.2. Feel free to re-open it if you notice a wrong behaviour in 2.4.4. Thank you.
- State changed from hold to resolved
I've looked into the problem. The issue has been mostly fixed in 2.4.2: it has gone mostly because SET ISOLATION LEVEL is not used anymore. However, a concurrency problem is still there in 2.4.4: commit() checks for the status outside the critical section, so it can decide to send a commit even if there is no need.
The problem is fixed. I've added a script similar to the test you proposed into the test suite. Thank you very much.
Create your profile
Help contribute to this project by taking a few moments to create your personal profile. Create your profile »
Psycopg is the most used PostgreSQL adapter for the Python programming language. At the core it fully implements the Python DB API 2.0 specifications. Several extensions allow access to many of the features offered by PostgreSQL.