"This session is in 'prepared' state; no further" error with SQLAlchemy using scoped_session in threaded mod_wsgi app

Question

I recently updated to SQLAlchemy 1.1, which I'm using under Django 1.10 (also recently updated from 1.6), and I keep getting sqlalchemy/mysql errors that This session is in 'prepared' state; no further SQL can be emitted within this transaction.

How do I debug this?

It's running in a single process, multi-threaded environment under mod_wsgi - and I'm not sure if I've properly configured SQLAlchemy's scoped_session.

I use a request container that is assigned to each incoming request, which sets up the session and cleans it up. (I'm assuming each request in Django is on it's own thread.)

# scoped_session as a global variable
# I constant errors if pool_size = 20 for some reason
Engine = create_engine(host, pool_recycle=600, pool_size=10, connect_args=options)
Session = scoped_session(sessionmaker(autoflush=True, bind=Engine))
RUNNING_DEVSERVER = (len(sys.argv) > 1 and sys.argv[1] == 'runserver') # Session.remove() fails in dev

# Created in my API, once per request (per thread)
class RequestContainer(object):
    def __init__(self, request, *args, **kwargs):
        self.s = Session()

    def safe_commit(self):
        try:
            self.s.commit()
        except:
            self.s.rollback()
            raise

    def __del__(self):
        if self.s:
            try:
                self.s.commit()
            except:
                self.s.rollback()
                raise

            if not RUNNING_DEVSERVER:
                Session.remove()
            self.s = None

And the prepared state error pops up in the code, usually in the same place, but not all the time, and sometimes in other places:

...
rs = request_container.s.query(MyTable)
...
if rs.count():
# Error log:
  File "/usr/local/lib/python2.7/dist-packages/SQLAlchemy-1.1.0b3-py2.7-linux-x86_64.egg/sqlalchemy/orm/query.py", line 3011, in count
    return self.from_self(col).scalar()
  File "/usr/local/lib/python2.7/dist-packages/SQLAlchemy-1.1.0b3-py2.7-linux-x86_64.egg/sqlalchemy/orm/query.py", line 2765, in scalar
    ret = self.one()
  File "/usr/local/lib/python2.7/dist-packages/SQLAlchemy-1.1.0b3-py2.7-linux-x86_64.egg/sqlalchemy/orm/query.py", line 2736, in one
    ret = self.one_or_none()
  File "/usr/local/lib/python2.7/dist-packages/SQLAlchemy-1.1.0b3-py2.7-linux-x86_64.egg/sqlalchemy/orm/query.py", line 2706, in one_or_none
    ret = list(self)
  File "/usr/local/lib/python2.7/dist-packages/SQLAlchemy-1.1.0b3-py2.7-linux-x86_64.egg/sqlalchemy/orm/query.py", line 2777, in __iter__
    return self._execute_and_instances(context)
  File "/usr/local/lib/python2.7/dist-packages/SQLAlchemy-1.1.0b3-py2.7-linux-x86_64.egg/sqlalchemy/orm/query.py", line 2798, in _execute_and_instances
    close_with_result=True)
  File "/usr/local/lib/python2.7/dist-packages/SQLAlchemy-1.1.0b3-py2.7-linux-x86_64.egg/sqlalchemy/orm/query.py", line 2807, in _get_bind_args
    **kw
  File "/usr/local/lib/python2.7/dist-packages/SQLAlchemy-1.1.0b3-py2.7-linux-x86_64.egg/sqlalchemy/orm/query.py", line 2789, in _connection_from_session
    conn = self.session.connection(**kw)
  File "/usr/local/lib/python2.7/dist-packages/SQLAlchemy-1.1.0b3-py2.7-linux-x86_64.egg/sqlalchemy/orm/session.py", line 903, in connection
    execution_options=execution_options)
  File "/usr/local/lib/python2.7/dist-packages/SQLAlchemy-1.1.0b3-py2.7-linux-x86_64.egg/sqlalchemy/orm/session.py", line 908, in _connection_for_bind
    engine, execution_options)
  File "/usr/local/lib/python2.7/dist-packages/SQLAlchemy-1.1.0b3-py2.7-linux-x86_64.egg/sqlalchemy/orm/session.py", line 319, in _connection_for_bind
    self._assert_active()
  File "/usr/local/lib/python2.7/dist-packages/SQLAlchemy-1.1.0b3-py2.7-linux-x86_64.egg/sqlalchemy/orm/session.py", line 201, in _assert_active
    "This session is in 'prepared' state; no further "
InvalidRequestError: This session is in 'prepared' state; no further SQL can be emitted within this transaction.

Answer 1

The RequestContainer was being accidentally assigned to a global API interface handler, causing one session to be mis-used among multiple threads, when it was intended to be created per thread.

Updated to show how how I assign a session to each thread, including tear-down to prevent database commit errors from hanging the session state:

class ThreadSessionRequest(object):
    def __init__(self, request, *args, **kwargs):
        self.s = Session()

        def __del__(self):
            if self.s:
                self.remove_session()

        def remove_session(self):
            if self.s:
                try:
                    self.safe_commit()
                finally:
                    Session.remove()
                    del self.s
                    self.s = None

        def safe_commit(self):
            if self.s:
                try:
                    self.s.commit()
                except:
                    self.s.rollback()
                    raise