Re: transaction abort with multiple threads

On Thu, May 20, 2010 at 8:13 PM, Joachim Worringen
<[email protected]> wrote:
> On 05/19/2010 01:55 PM, Joachim Worringen wrote:
>>>
>>> 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.
>>
>> I can do this, maybe today.
>
> Well, I first had to fix synchronization in my code to make it really
> threadsafe (correct, that is) - this is now done, and guess what: this
> exception does not longer show up. Seems I have reduced concurrency enough
> to avoid it.
>
> This is of course not really satisfying, as Postgresql and psycopg should
> not care about whether I do stupid things with my threads or not. I'll try
> to go back to the original version and reproduce it.

Yes, albeit your code obviously benefits from being thread-safe :)
badly written client code should not cause psycopg to lose its
consistency. If you manage to crash it again and provide a debug trace
it would be really helpful.

> In the meantime, I observed that although two threads concurrently perform
> their independent operations (inserting lots of data into different
> temporary tables), execution does not speed up although the machine has
> plenty of resources available. Is it possible that I would have to use
> different *connections*, not only different *cursors*, to really get a
> speedup?

I think it's likely: threads insisting on the same connection are
effectively serialized: not only the connection ensures (well, it
should) that queries are sent one at time, there is also a single
backend process to answer your queries. For every connection you spawn
there will be a different backend process to execute the queries and
the possibility to run concurrently if the locks they acquire allow
it: check the Postgres documentation about the locking strategies and
possibly take a look at the pg_locks table to check your processes are
not grabbing more strict locks than the minimum required. Short
transactions also help, because terminating a transaction is only way
to release the locks acquired during its lifetime.

-- Daniele