Re: Lock-ups with multi-threading and psycopg 2.0.11

On Thu, Sep 24, 2009 at 12:01 AM, Richard Davies
<[email protected]> wrote:
>> > The database access is simple - I create a single connection centrally with:
> ...
>> > Whenever a thread needs to do an SQL query, it creates a cursor, does the
>> > query and destroys it. For example:
> ...
>> Note that if you're using the same connection for each thread, you
>> won't get much parallelism.  While one thread is executing a query,
>> all other threads will block if they try to do the same.  If you want
>> the threads to be able to issue queries in parallel, they will have to
>> use separate database connections.
>
> OK, that's interesting and not something which I'd realized. So I will
> probably change the code to separate connections for each thread.

When you consider that the normal usage pattern is to make use of
transactions, and transactions are managed at the connection level, it
makes sense.  The order that the queries get executed inside a
transaction matters, so having a system that gives non-deterministic
ordering would be a problem.

Now while you've disabled transaction support, the connection still
exhbits this behaviour.


>> > The section which typically locks up has the following code:
>> >
>> > ?logging.debug('1')
>> > ?cursor = db.cursor()
>> > ?logging.debug('2')
>> > ?cursor.execute('select "type", "resource", "usage", "user" from "resources"')
>> > ?logging.debug('3')
>> > ?rs = cursor.fetchall()
>> > ?logging.debug('4')
>> > ?cursor.execute('select "type", "resource", "key", "value" from "resource_properties"')
>> > ?logging.debug('5')
>> > ?ks = cursor.fetchall()
>> > ?logging.debug('6')
>> > ?cursor.close()
>> > ?logging.debug('7')
>> >
>> > In the logs, I see '1' and '2', but not '3' or anything later.
>> >
>> > Given that the lock-up is isolated to inside the cursor.execute(), it seems
>> > that it must be in psycopg? If that's the case, can you suggest a solution
>> > or how I can debug this further? For example, how (and where?) should I add
>> > trace inside the psycopg code to pinpoint this further?
>>
>> Is it possible that some other thread is performing a cursor.execute()
>> call and blocking the thread you are watching?
>
> It is definitely possible that I'm calling cursor.execute() twice in parallel
> for different cursors from different threads on the same connection.
>
> All of the SQL queries are very simple and should return immediately - they
> are selects or updates on a table with ~100 rows and ~5 columns.
>
> Is it meant to be thread-safe to execute cursor.execute() twice in parallel
> on different cursors of the same connection? Or could I have hit some
> deadlock/livelock in psycopg?

Quite possibly.  Psycopg performs locking of each connection so that
only one cursor can execute a statement at a time.  It is possible
that you've triggered a deadlock.  If you can provide a simple test
case that reproduces it, that would be quite helpful.


>> > I suspect that the problem may be having too many simultaneous open cursors.
>> > Is there a way that I can log how many of these there are? Where is the
>> > limit set, and can I raise it?
>
> Is this potentially a problem? It's quite plausible that I have ~100 cursors
> on the same single connection at present.
>
> I couldn't find any postgresql configuration or documentation relating to
> numbers of supported cursors - only max_connections.

Not likely.  The only resource extra cursors really take up is memory
on the application side to store the result set (this is assuming you
aren't using psycopg2's named cursor extension, in which case you'd be
allocating server side resources too).

James.