Stuck with psycopg2 + multithreading!

[cameron (Cameron Simpson)]

If you see the psycopg2
documentation,
it says that

Warning
By default even a simple SELECT will start a transaction: in
long-running programs, if no further action is taken, the session will
remain “idle in transaction”, an undesirable condition for several
reasons (locks are held by the session, tables bloat…). For long lived
scripts, either make sure to terminate a transaction as soon as
possible or use an autocommit connection.

Ah, ok, good.

So I have not started it explicitly. I have only set autocommit to False so that it doesn’t treat every line as a transaction, instead everything till I manually say conn.commit()

Sounds good.

@cameron

The other thing which occurs to be is that you’re using the same
connection in all the threads. I’ve have taken that to imply that there
can only be one cursor in play at a time, effectively serialising the
SQL in book_seat(). Maybe stick some global counters in
boot_seat()'s with closing(...) clause to verify that.

I have tried printing the cursor object in every iteration, and I got different results, so I think we can rule out the existence of single cursor!

<cursor object at 0x000001E4B407E0A0; closed: 0>
<cursor object at 0x000001E4B407DFC0; closed: 0>
[...]

This just says you’ve got several cursor instances. You get a new one
every time you make one. But I believe they’ll be using the same
connection and that they are serialised, so only one cursor is active at
a time.

WRT to making multiple connections, just make another the same way you
made the first one:

 conn = psycopg2.connect(your_dsn)

You could do that per-thread:

 with psycopg2.connect(your_dsn) as conn:
     with conn.cursor() as curs:
         ... do stuff ...

to get parallel db access.

Cheers,
Cameron Simpson cs@cskk.id.au