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

James Henstridge wrote:
> > 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.

OK, I've managed to generate the simple test case, which is a big step
forward!

The failure is definitely version-specific.

It happens within 5 seconds on my production machines with Python 2.5.2,
PostgreSQL 8.1.4 and Psycopg2 2.0.11.

It does not happen on my desktop machine with Python 2.6.2, PostgreSQL 8.3.8
and Psycopg2 2.0.8.



I create a very simple and empty database with:

$ createdb test
$ psql test
# create table "test" ("key" text, "value" text);


I then run the following code:

import psycopg2 as dbapi
import threading

db = dbapi.connect(database='test')

class Tester(threading.Thread):
  def run(self):
    t = threading.currentThread()
    while True:
      cursor = db.cursor()
      print 'before %s' % t
      cursor.execute('select * from test')
      print 'after %s' % t
      x = cursor.fetchall()
      cursor.close()

for i in range(0,2):
  t = Tester()
  t.start()


This locks up within 5 seconds with both threads in the 'before' state.

In one example, strace python test.py ends with:

futex(0x9882700, FUTEX_WAKE, 1)         = 1
before <Tester(Thread-2, started)>
select(0, NULL, NULL, NULL, {0, 0})     = 0 (Timeout)
futex(0x9882700, FUTEX_WAIT, 0, NULL)   = 0
futex(0x9882700, FUTEX_WAKE, 1)         = 0
futex(0x9882700, FUTEX_WAKE, 1)         = 0
futex(0x9882700, FUTEX_WAKE, 1)         = 0
futex(0x98ef9b0, FUTEX_WAKE, 1)         = 0
futex(0x9882700, FUTEX_WAKE, 1)         = 1
after <Tester(Thread-1, started)>
before <Tester(Thread-1, started)>
futex(0x9882700, FUTEX_WAIT, 0, NULL)   = -1 EAGAIN (Resource temporarily
unavailable)
futex(0x98ef998, FUTEX_WAKE, 1)         = 0
futex(0x9882700, FUTEX_WAKE, 1)         = 1
after <Tester(Thread-1, started)>
before <Tester(Thread-1, started)>
futex(0x9882700, FUTEX_WAKE, 1)         = 0
after <Tester(Thread-1, started)>
before <Tester(Thread-1, started)>
futex(0x9882700, FUTEX_WAKE, 1)         = 0
after <Tester(Thread-1, started)>
futex(0x9882700, FUTEX_WAIT, 0, NULLbefore <Tester(Thread-1, started)>
)   = -1 EAGAIN (Resource temporarily unavailable)
futex(0x9882700, FUTEX_WAKE, 1after <Tester(Thread-1, started)>
before <Tester(Thread-1, started)>
)         = 0
after <Tester(Thread-1, started)>
futex(0x9882700, FUTEX_WAKE, 1before <Tester(Thread-1, started)>
)         = 0
futex(0x98826e8, FUTEX_WAKE, 1)         = 0
futex(0x9882700, FUTEX_WAKE, 1)         = 1
after <Tester(Thread-1, started)>
before <Tester(Thread-1, started)>
futex(0x98826c8, FUTEX_WAIT, 0, NULLafter <Tester(Thread-1, started)>
after <Tester(Thread-2, started)>
before <Tester(Thread-1, started)>
before <Tester(Thread-2, started)>


Is there anything else which I can send which will help to debug? I'm happy
to run any test versions of psycopg with extra logging if you can suggest
what I should put in?

Thanks very much,

Richard.