Executing a Transaction using SQLAlchemy in a multithreaded environment

Question

I'm working on an application which uses Flask, SQLAlchemy and PostgreSQL. I've to write a transaction that executes multiple queries on the database.

def exec_query_1():
    with db.engine.connect() as connection:
        connection.execute(#some-query)

def exec_query_2():
    with db.engine.connect() as connection:
        connection.execute(#some-query)

def exec_query_3():
    with db.engine.connect() as connection:
        connection.execute(#some-query)

def execute_transaction():
    with db.engine.connect() as connection:
        with connection.begin() as transaction:
            exec_query_1()
            exec_query_2()
            exec_query_3()

Given that the application is multithreaded, will this code work as expected? If yes, how? If no, what would be the right approach to make it work?

Ilja Everilä · Accepted Answer · 2018-10-17 18:09:45Z

5

The code will not work as expected, even in a single thread. The connections opened in the functions are separate¹ from the connection used in execute_transaction() and have their own transactions. You should arrange your code so that the functions receive the connection with the ongoing transaction as an argument:

def exec_query_1(connection):
    connection.execute(#some-query)

def exec_query_2(connection):
    connection.execute(#some-query)

def exec_query_3(connection):
    connection.execute(#some-query)

def execute_transaction():
    with db.engine.connect() as connection:
        with connection.begin() as transaction:
            exec_query_1(connection)
            exec_query_2(connection)
            exec_query_3(connection)

Remember that connections are not thread-safe, so don't share them between threads. "When do I construct a Session, when do I commit it, and when do I close it?" is a good read, altough about Session.

¹ May depend on pool configuration.

answered Oct 17, 2018 at 18:09

Ilja Everilä

53.4k9 gold badges138 silver badges142 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

onlyphantom Over a year ago

Why do you need the transaction alias in with connection.begin() as transaction? Upvoted the answer because of the brilliant explanation but can't seem to wrap my head around where transaction is used. Learning sql so pardon the ignorance

Ilja Everilä Over a year ago

It's not needed, prolly just wrote it like that out of habit. with connection.begin(): would work just as well.

David Over a year ago

I'm also learning sqlalchemy and Python3 (as I go, just enough to do a quick database server). In the above example, when does the "commit" occur? In particular, if one of those calls to execute a query raises an exception, will that be sufficient to rollback the transaction or do you need to catch that exception and manually rollback?

T.H. Over a year ago

COMMIT automatically happens on returning the transaction at the exit of with connection.begin() block

Collectives™ on Stack Overflow

Executing a Transaction using SQLAlchemy in a multithreaded environment

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related