Flask db.session is not thread-safe (scheduled tasks)

[markofu]

After wondering about SQL Alchemeny sessions and transaction scopes, I wondered about the possibility of multiple transactions db.Model being run at the same time.

Documentation suggest that Flask handles this by only processing one web request at time (e.g., submitting config changes), which is safe.

But our additional scheduled tasks of collectors and auditors should not share a db session. A session should be limited in the scope it serves so that concurrent

Effectively, I think concurrent db.session.add() and subsequent commits and rollbacks might end up behaving unexpectedly, committing more than expected, or rolling back more than expected.

From: http://docs.sqlalchemy.org/en/latest/orm/session_basics.html
"The Session is very much intended to be used in a non-concurrent fashion, which usually means in only one thread at a time."

[psykzz]

Scoped sessions would solve this problem

# 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()

Or more specifically from the flask docs

from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy.ext.declarative import declarative_base

engine = create_engine('sqlite:////tmp/test.db', convert_unicode=True)
db_session = scoped_session(sessionmaker(autocommit=False,
                                         autoflush=False,
                                         bind=engine))
Base = declarative_base()
Base.query = db_session.query_property()

def init_db():
    # import all modules here that might define models so that
    # they will be registered properly on the metadata.  Otherwise
    # you will have to import them first before calling init_db()
    import yourapplication.models
    Base.metadata.create_all(bind=engine)

[Jriles]

I believe this is an ongoing issue due to some work I've been doing this summer, especially when it comes to multiple scheduled tasks talking to the db in production.