Are transactions in SQLAlchemy thread safe?

Question

I am developing a web app using SQLAlchemy's expression language, not its orm. I want to use multiple threads in my app, but I'm not sure about thread safety. I am using this section of the documentation to make a connection. I think this is thread safe because I reference a specific connection in each request. Is this thread safe?

Answer 1

The docs for connections and sessions say that neither is thread safe or intended to be shared between threads.

The Connection object is not thread-safe. While a Connection can be shared among threads using properly synchronized access, it is still possible that the underlying DBAPI connection may not support shared access between threads. Check the DBAPI documentation for details.

The Session is very much intended to be used in a non-concurrent fashion, which usually means in only one thread at a time.

The Session should be used in such a way that one instance exists for a single series of operations within a single transaction.

The bigger point is that you should not want to use the session with multiple concurrent threads.

There is no guarantee when using the same connection (and transaction context) in more than one thread that the behavior will be correct or consistent.

You should use one connection or session per thread. If you need guarantees about the data, you should set the isolation level for the engine or session. For web applications, SQLAlchemy suggests using one connection per request cycle.

This simple correspondence of web request and thread means that to associate a Session with a thread implies it is also associated with the web request running within that thread, and vice versa, provided that the Session is created only after the web request begins and torn down just before the web request ends.

Answer 2

I think you are confusing atomicity with isolation.

Atomicity is usually handled through transactions and concerns integrity.

Isolation is about concurrent read/write to a database table (thus thread safety). For example: if you want to increment an int field of a table's record, you will have to select the record's field, increment the value and update it. If multiple threads are doing this concurrently the result will depend on the order of the reads/writes.

http://docs.sqlalchemy.org/en/latest/core/engines.html?highlight=isolation#engine-creation-api