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

On Wed, Sep 23, 2009 at 7:58 PM, Richard Davies
<[email protected]> wrote:
> Hi!
>
> I have a simple multi-threaded web application, and have logs which indicate
> that it is locking up inside psycopg about once a week. I'd appreciate
> thoughts on if this issue is known or how I can best help debug it if not.
>
>
> My application is written in Python 2.5.2 using the standard library's
> BaseHTTPServer.HTTPServer with the SocketServer.ThreadingMixIn (i.e. each
> HTTP request runs in a separate thread). I am running Psycopg 2.0.11 against
> PostgreSQL 8.1.4 on Linux.
>
> The database access is simple - I create a single connection centrally with:
>
>  import psycopg2 as dbapi
>  assert dbapi.apilevel == '2.0', 'Unexpected DB-API version'
>  assert dbapi.threadsafety == 2, 'Threads must be able to share connections'
>  db = dbapi.connect(database=config.DATABASE_NAME)
>  db.set_isolation_level(0)
>
> Whenever a thread needs to do an SQL query, it creates a cursor, does the
> query and destroys it. For example:
>
>  cursor = db.cursor()
>  cursor.execute('select ...')
>  x = cursor.rowcount
>  cursor.close()

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.


> These are typically single selects or updates against tables with ~100 rows
> and ~5 columns.
>
>
> 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?
>
> 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?
>
> Thanks in advance,

Is it possible that some other thread is performing a cursor.execute()
call and blocking the thread you are watching?

James.