This project is archived and is in readonly mode.
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.
def delete(conn):
while True:
conn.commit()
def test(conn):
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,
1
2
... 98
99
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
-
Daniele Varrazzo February 22nd, 2012 @ 11:03 PM
- 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.
-
Daniele Varrazzo February 24th, 2012 @ 03:31 AM
- 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 ยป
<b>WARNING:</b> the informations in this tracker are archived. Please submit new tickets or comments to <a href="https://github.com/psycopg/psycopg2/issues">the new tracker</a>.
<br/>
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.
Daniele Varrazzo
Federico Di Gregorio
Psycopg website