Multi-threaded use of SQLAlchemy

Question

I want to make a Database Application Programming Interface written in Python and using SQLAlchemy (or any other database connectors if it is told that using SQLAlchemy for this kind of task is not the good way to go). The setup is a MySQL server running on Linux or BSD and a the Python software running on a Linux or BSD machine (Either foreign or local).

Basically what I want to do is spawn a new thread for each connections and the protocol would be custom and quite simple, although for each requests I would like to open a new transaction (or session as I have read) and then I need to commit the session. The problem I am facing right now is that there is high probability that another sessions happen at the same time from another connection.

My question here is what should I do to handle this situation?

• Should I use a lock so only a single session can run at the same time?
• Are sessions actually thread-safe and I am wrong about thinking that they are not?
• Is there a better way to handle this situation?
• Is threading the way not-to-go?

Answer 1

Session objects are not thread-safe, but are thread-local. From the docs:

"The Session object is entirely designed to be used in a non-concurrent fashion, which in terms of multithreading means "only in one thread at a time" .. some process needs to be in place such that mutltiple calls across many threads don’t actually get a handle to the same session. We call this notion thread local storage."

If you don't want to do the work of managing threads and sessions yourself, SQLAlchemy has the ScopedSession object to take care of this for you:

The ScopedSession object by default uses threading.local() as storage, so that a single Session is maintained for all who call upon the ScopedSession registry, but only within the scope of a single thread. Callers who call upon the registry in a different thread get a Session instance that is local to that other thread.

Using this technique, the ScopedSession provides a quick and relatively simple way of providing a single, global object in an application that is safe to be called upon from multiple threads.

See the examples in Contextual/Thread-local Sessions for setting up your own thread-safe sessions:

# set up a scoped_session
from sqlalchemy.orm import scoped_session
from sqlalchemy.orm import sessionmaker

session_factory = sessionmaker(bind=some_engine)
Session = scoped_session(session_factory)

# now all calls to Session() will create a thread-local session
some_session = Session()

# you can now use some_session to run multiple queries, etc.
# remember to close it when you're finished!
Session.remove()