How to correctly perform multithreading with psycopg2 ?

[silverbullet1]

Hi all, not trying to create duplicate content here, so I will just link my already opened question in python discussion forum wherein I need some help with psycopg2!
Please help - https://discuss.python.org/t/stuck-with-psycopg2-multithreading/39918
Thanks 🥲

[dvarrazzo]

@silverbullet1 can you please copy your question here?

The use of multiple threads of the same connection is safe, but you must understand what psycopg is doing with the Postgres session. Threads sharing the same connection are also sharing the same session, so they are all operating in the same transaction, because postgres can only create one transaction per session. You can imagine several cats taking turns to type psql command on the same keyboard: psycopg makes sure that only one cat at time can use the keyboard and that two commands don't mix up (ending up typing SELE-UPDATE-CT or taking one the result of another) but, because the connection is responsible for the session and the transaction, the first cat's BEGIN will open a transaction that will be shared by all the other cats, even if each one is using a distinct cursor.

With this in mind, the semantics of what you are doing is defined by Postgres, not by psycopg. The documentation you want to read is the one about explicit locking. By the look of it, you might want to use SELECT ... FOR UPDATE SKIP LOCKED, but the documentation says with SKIP LOCKED, any selected rows that cannot be immediately locked are skipped: it means that a transaction taking a lock can SELECT ... SKIP LOCKED the same row it has already locked. A quick test in psql (of which you can make a few, to prototype what will happen in psycopg once you have the cats-with-a-keyboard model in mind) proves it:

=# create table testlock (id serial primary key, data text);
=# insert into testlock (data) values ('one'), ('two');
=# begin;
=*# select data from testlock order by id limit 1 for update skip locked;
one
=*# select data from testlock order by id limit 1 for update skip locked;
one

but, if you run a similar query in another console while the first is still active:

=# begin;
=*# select data from testlock order by id limit 1 for update skip locked;
two

if you run it in a third concurrent session you will see no row returned.

I think that for this use case you can't share a session and that you should use a connection pool instead. The serialized effect you are seeing is likely because two concurrent threads will try to reserve the same row: the second thread will block on the first thread's lock and will have to wait for it to finish to proceed (you can test the same trying to run a similar pattern of the example above but without SKIP LOCKED: the second thread will lock until the first commits).

If you end up using similar code for real, it would also be wise to give statements a timeout to avoid that an unforeseen combination of actions deadlocks and takes up all the server's resorces:

=# begin;
=*# set local statement_timeout to 1000;
=*# select data from testlock order by id limit 1 for update;
[... 1 s passed]
ERROR:  canceling statement due to statement timeout
CONTEXT:  while locking tuple (0,1) in relation "testlock"
=!# 

I suggest you play with psycopg 3, because it has a more explicit with conn.transaction() block (also see transaction docs) and a better connection pool which will queue your cats if you have more cats than connections, instead of failing immediately.

[silverbullet1]

Thank you for the detailed answer, it makes sense now. I did not know about the session coming into picture. That was some learning :) so psycopg2 does not allow to open different sessions on the DB with the same connection ?

[dvarrazzo]

No, a connection is precisely the wrapper for a db session.