Share django transaction across threads

Question

I've a problem where one API implemented in a django (3.2) web app running with gunicorn (gevent) has to fetch different prices from multiple APIs and store those prices in the database (Postgres 13) before returning to the client. I'd like to put the inserts in the same transaction, so if something unexpected happens, nothing will be inserted.

I am now going forward by first calling all apis, each one inside a green thread (gevent) and after all of them return, I bulk insert the results.

But turns out I got really curious if I it is possible for different threads ( green or not) to share the same transaction. I saw that psycopg2 can execute in a non blocking way. The issue now is everytime I start thread in django the new thread is inside a new transaction. I will dig into the django db backend source to understand what is happening, but maybe someone can clear this out.

Tldr; is possible to different threads execute queries inside the same transaction?

Answer 1

You definitely do not want to attempt to share a single transaction/postgres connection between multiple threads without some locking mechanism to make sure they don't interleave activity on the connection in some nasty way that causes errors.

Instead, a simpler and safer solution is to start your green threads from the main request thread and then gevent.join([<green thread1>, <green thread2>...]) all of them from that same main request thread. Each green thread would go get the data from the API and just return it as the exit of each thread.

Then have the main request thread go through each exited green thread object (greenlet) and get the return value for each via Greenlet.get(). Then do the inserts on the main request thread using its normal transaction/connection.

UPDATE

If you want to get even more sophisticated to achieve better performance, you could use a Pool and have each greenlet put its result on a Queue that's read from the main thread. That way you start saving results to the database as they become available rather than waiting until they all complete.