Re: transaction abort with multiple threads

On Wed, May 19, 2010 at 9:52 AM, Joachim Worringen
<[email protected]> wrote:
> Greetings,
>
> I'm using psycopg2 2.2.1 with Postgresql 8.4. Within my Python application,
> I'm creating threads via threading.Thread(), passing the same connection to
> each of them. Each threads creates its own cursor then.
>
> Exception in thread Thread-11:
> Traceback (most recent call last):
>  File "/usr/lib64/python2.6/threading.py", line 525, in __bootstrap_inner
>    self.run()
>  File "/usr/lib64/python2.6/threading.py", line 477, in run
>    self.__target(*self.__args, **self.__kwargs)
>  File "/home/joachim/bin/pb_operators.py", line 911, in store_data
>    self._aggregate_runs(db, output_table)
>  File "/home/joachim/bin/pb_operators.py", line 506, in _aggregate_runs
>    "#* DEBUG: creating new data set (paramters):" )
>  File "/home/joachim/bin/pb_common.py", line 659, in sqlexe
>    crs.execute(cmd, args)
> InternalError: SET TRANSACTION ISOLATION LEVEL must be called before any
> query
>
> The queries of all other threads are aborted at this point, which is
> expected.
>
> Do I need to set autocommit-mode in this case, and if yes, will this affect
> performance (each thread may read and insert millions of elements)?

What is in your "cmd" (regarding to transaction control?) Transaction
control is per session, so you shouldn't need sending such commands in
your threads: you can configure the session beforehand and pass it to
the thread that will use it to create their own cursors and use them
just to make the grunt work.

You can set autocommit mode on the connection [1]: this will affect
all the cursors created from it. It should affect performance in a
positive way: you send the transaction control commands just once and
they will affect the whole session (i.e. all the connection's
cursors). In the whole session no transaction is created.

If this doesn't solve your problem, please post an excerpt of the
commands you send.

Regards,

-- Daniele

[1] http://initd.org/psycopg/docs/usage.html#transactions-control