Threaded Sessions expiring on SQLAlchemy?

Question

This is difficult to describe or show much code for, but I'll try. Essentially I have a multi-threaded desktop app that will frequently handle the adding/removing/changing of tables in threads. From what I read, I should use scoped_session and pass that around to the various threads to do the work (I think?). Here're some basic code examples:

class SQL():
    def __init__(self):        
        self.db = create_engine('mysql+mysqldb://thesqlserver')
        self.metadata = MetaData(self.db)
        self.SessionObj = scoped_session(sessionmaker(bind=self.db, autoflush=True))

db = SQL()
session = db.SessionObj()
someObj = Obj(val, val2)
session.add(someObj)
session.commit()

The above class is what I'm using as the general access of SQL stuff. After creating a new session, performing a query and update/add to it, upon the session.commit(), I get the following error:

Traceback (most recent call last):
  File "core\taskHandler.pyc", line 42, in run
  File "core\taskHandler.pyc", line 184, in addTasks
  File "core\sqlHandler.pyc", line 35, in commit
  File "sqlalchemy\orm\session.pyc", line 624, in rollback
  File "sqlalchemy\orm\session.pyc", line 338, in rollback
  File "sqlalchemy\orm\session.pyc", line 369, in _rollback_impl
  File "sqlalchemy\orm\session.pyc", line 239, in _restore_snapshot
  File "sqlalchemy\orm\state.pyc", line 252, in expire
AttributeError: 'NoneType' object has no attribute 'expire'

Then the next if another sql attempt goes through:

Traceback (most recent call last):
  File "core\taskHandler.pyc", line 44, in run
  File "core\taskHandler.pyc", line 196, in deleteTasks
  File "sqlalchemy\orm\query.pyc", line 2164, in scalar
  File "sqlalchemy\orm\query.pyc", line 2133, in one
  File "sqlalchemy\orm\query.pyc", line 2176, in __iter__
  File "sqlalchemy\orm\query.pyc", line 2189, in _execute_and_instances
  File "sqlalchemy\orm\query.pyc", line 2180, in _connection_from_session
  File "sqlalchemy\orm\session.pyc", line 729, in connection
  File "sqlalchemy\orm\session.pyc", line 733, in _connection_for_bind
  File "sqlalchemy\orm\session.pyc", line 249, in _connection_for_bind
  File "sqlalchemy\orm\session.pyc", line 177, in _assert_is_active
sqlalchemy.exc.InvalidRequestError: This Session's transaction has been rolled back by a nested rollback() call.  To begin a new transaction, issue Session.rollback() first.

That's about as much as I know and I think the best I can describe. Any ideas on what I'm supposed to be doing here? It's all mud to me. Thanks in advance!

Answer 1

The funny part is, you missed the most critical part of the answer you "ripped the code from", which is that there is a Python function in the middle, which is executing some abstract operation (it's labeled as func()). That code illustrates a transactional wrapper for a function, and in the above example you instead have an object method called commit() that isn't otherwise calling upon any additional operations with the Session.

Here you have kind of a session-holding object called SQL() that is not really adding any usefulness to your program and makes it needlessly complicated, and is probably also the source of the issue. Unless your application intends to connect to many different databases at different times, and use SQL() objects to represent that state, there's not much use in building a class called "SQL" that has an "engine" stuck onto it. Just stick the engine in a module somewhere, as well as your scoped_session().

The engine and scoped_session represent a pattern called the factory pattern - they are objects that create some other useful object, in this case scoped_session creates a Session, and the Engine is used internally by the Session to create a Connection with which to talk to the database. It doesn't make much sense to place the Session object as a sibling member along with Engine and scoped_session - you'd be carrying around either the factories (the Engine and scoped_session), or the object itself that they create (the Session), which all depends on what you're trying to do.

The Session itself, remember here we're talking about the thing the factories create (Session), not the factories themselves (Engine and scoped_session), is not in the least bit thread safe. It is something you usually create only local to a function - it shouldn't be global, and if you're in fact using a single SQL() object across threads that's probably the problem here. The actual error you're getting, I'm not really sure what that is and I could only have a better clue if I knew the exact version of SQLAlchemy in use here, though the randomness of the error suggests that you have some kind of threading issue where something is becoming None in one thread as another expects that same object to be present.

So what you need to establish in this program is when exactly a particular thread of execution begins, what it needs to do with the database as it proceeds, and then when it ends. When you can establish a consistent pattern for that, you would then link a single Session to this thread, which goes for the lifespan of that thread, and is never shared. All the objects which are produced by this session must also not be shared to other threads - they are extensions of the Session's state. If you have "worker threads" in use, those worker threads should load up their own data as needed, within their own Session. The Session represents a live database transaction and you generally want transactions local to a single thread.

As this is not a web application you might want to forego the usage of scoped_session, unless you do in fact have a place for a thread-local pattern to be used.