Re: transaction abort with multiple threads

On Wed, May 19, 2010 at 12:05 PM, Joachim Worringen
<[email protected]> wrote:
> On 05/19/2010 12:44 PM, Joachim Worringen wrote:
>>
>> None of the threads calls set_isolation_level() (thus, default
>> transaction control is in place).
>
> Actually, the error does not show up when I call
> db.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT) before creating the
> threads.
>
> But as I said, I fear this has negative performance impacts, and just think
> it shouldn't be necessary (from my understanding).

If things are as you described, it is a bug anyway.

The only place where the SET TRANSACTION ISOLATION LEVEL is sent is in
the pq_begin_locked() function. It should send the command only if no
transaction is in progress. There seem to be the potential for a race
condition there, but the function is called holding the connection
lock, so it should be not possible. May we have broken the locking
code?

Do you call commit()/rollback() in your threads or just cur.execute()?
If you don't, the only race condition possible seems if two threads
call the first command concurrently and two begin are sent (which is
only possible if we have broken the lock). In this case a workaround
would be to force the transaction start outside the threaded part
(e.g. executing a "select 1" on a cursor before starting the thread).
You could make this check to test what is the more efficient way to
run your code.

If you could run psycopg in debug mode (compiled with PSYCOPG_DEBUG in
the setup.cfg flags and running with the PSYCOPG_DEBUG env var), the
debug log in case of crash would be very useful.

-- Daniele