This project is archived and is in readonly mode.

Psycopg2 concurrency issue

#103

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

Reported by Psycopg website · February 22nd, 2012 @ 09:38 PM

Activity

Daniele Varrazzo
- 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.

February 22nd, 2012 @ 11:03 PM
Daniele Varrazzo
- 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.

February 24th, 2012 @ 03:31 AM