Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Multi-threaded use of SQLAlchemy

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?
like image 592
maaudet Avatar asked Jun 09 '11 18:06

maaudet


People also ask

Is SQLAlchemy thread safe?

Every pool implementation in SQLAlchemy is thread safe, including the default QueuePool . This means that 2 threads requesting a connection simultaneously will checkout 2 different connections. By extension, an engine will also be thread-safe.

What is the use of SQLAlchemy?

SQLAlchemy is a library that facilitates the communication between Python programs and databases. Most of the times, this library is used as an Object Relational Mapper (ORM) tool that translates Python classes to tables on relational databases and automatically converts function calls to SQL statements.

Should I use SQLAlchemy core or ORM?

If you want to view your data in a more schema-centric view (as used in SQL), use Core. If you have data for which business objects are not needed, use Core. If you view your data as business objects, use ORM. If you are building a quick prototype, use ORM.


1 Answers

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()
like image 174
culix Avatar answered Oct 11 '22 19:10

culix