sqlalchemy.exc.ResourceClosedError: This transaction is closed

Question

Facing the following error in a Pyramid Application while using SQLAlchemy and Zope Transaction Manager.

This is how I am creating a scoped session:

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.schema import MetaData

from sqlalchemy.orm import (
    scoped_session,
    sessionmaker,
    )
from zope.sqlalchemy import ZopeTransactionExtension

metadata = MetaData(naming_convention=NAMING_CONVENTION)
Base = declarative_base(metadata=metadata)
DBSession = scoped_session(sessionmaker(extension=ZopeTransactionExtension(keep_session=True)))

I am syncing my session creation and removal with my requests in the following way:

@view_config(route_name='social_get_individual_assets', renderer='json', effective_principals=2, permission='edit')
def social_get_individual_assets(requestJson):
            session = DBSession()
            try:
                body = requestJson.request.json_body
                search_term = body['text']
                horizontal = body['horizontal']
                vertical = body['vertical']
                log.info("social get individual assets request: %s", str(search_term).encode(encoding='utf_8'))
                json_data = get_individual_assets(session, search_term, horizontal, vertical)
                log.info("social get assets response: %s", str(search_term).encode(encoding='utf_8'))
                transaction.commit()
                DBSession.remove()
                return json_data
            except Exception as e:
                session.rollback()
                log.exception(e)
                raise e

For some reason, I keep running into this error all the time:

2017-12-06 13:32:07,965 ERROR [invideoapp.views.default:465] An operation previously failed, with traceback:

  File "/usr/lib64/python3.5/threading.py", line 882, in _bootstrap
    self._bootstrap_inner()
  File "/usr/lib64/python3.5/threading.py", line 914, in _bootstrap_inner
    self.run()
  File "/usr/lib64/python3.5/threading.py", line 862, in run
    self._target(*self._args, **self._kwargs)
  File "/usr/local/lib/python3.5/site-packages/waitress/task.py", line 78, in handler_thread
    task.service()
  File "/usr/local/lib/python3.5/site-packages/waitress/channel.py", line 338, in service
    task.service()
  File "/usr/local/lib/python3.5/site-packages/waitress/task.py", line 169, in service
    self.execute()
  File "/usr/local/lib/python3.5/site-packages/waitress/task.py", line 399, in execute
    app_iter = self.channel.server.application(env, start_response)
  File "/usr/local/lib/python3.5/site-packages/pyramid/router.py", line 270, in __call__
    response = self.execution_policy(environ, self)
  File "/usr/local/lib/python3.5/site-packages/pyramid_retry/__init__.py", line 114, in retry_policy
    response = router.invoke_request(request)
  File "/usr/local/lib/python3.5/site-packages/pyramid/router.py", line 249, in invoke_request
    response = handle_request(request)
  File "/usr/local/lib/python3.5/site-packages/pyramid_tm/__init__.py", line 136, in tm_tween
    response = handler(request)
  File "/usr/local/lib/python3.5/site-packages/pyramid/tweens.py", line 39, in excview_tween
    response = handler(request)
  File "/usr/local/lib/python3.5/site-packages/pyramid/router.py", line 156, in handle_request
    view_name
  File "/usr/local/lib/python3.5/site-packages/pyramid/view.py", line 642, in _call_view
    response = view_callable(context, request)
  File "/usr/local/lib/python3.5/site-packages/pyramid/viewderivers.py", line 439, in rendered_view
    result = view(context, request)
  File "/usr/local/lib/python3.5/site-packages/pyramid/viewderivers.py", line 148, in _requestonly_view
    response = view(request)
  File "/home/ttv/invideoapp/invideoapp/views/default.py", line 148, in upload_image
    transaction.commit()
  File "/usr/local/lib/python3.5/site-packages/transaction/_manager.py", line 131, in commit
    return self.get().commit()
  File "/usr/local/lib/python3.5/site-packages/transaction/_transaction.py", line 308, in commit
    t, v, tb = self._saveAndGetCommitishError()
  File "/usr/local/lib/python3.5/site-packages/transaction/_transaction.py", line 301, in commit
    self._commitResources()
  File "/usr/local/lib/python3.5/site-packages/transaction/_transaction.py", line 446, in _commitResources
    reraise(t, v, tb)
  File "/usr/local/lib/python3.5/site-packages/transaction/_compat.py", line 54, in reraise
    raise value
  File "/usr/local/lib/python3.5/site-packages/transaction/_transaction.py", line 423, in _commitResources
    rm.tpc_vote(self)
  File "/usr/local/lib/python3.5/site-packages/zope/sqlalchemy/datamanager.py", line 109, in tpc_vote
    self.tx.commit()
  File "/usr/local/lib64/python3.5/site-packages/sqlalchemy/orm/session.py", line 459, in commit
    self._assert_active(prepared_ok=True)
  File "/usr/local/lib64/python3.5/site-packages/sqlalchemy/orm/session.py", line 285, in _assert_active
    raise sa_exc.ResourceClosedError(closed_msg)
sqlalchemy.exc.ResourceClosedError: This transaction is closed
Traceback (most recent call last):
  File "/home/ttv/invideoapp/invideoapp/views/default.py", line 451, in update_master_json
    user = getUser(session,user_id)
  File "/home/ttv/invideoapp/invideoapp/invideomodules/auth_processing.py", line 12, in getUser
    raise e
  File "/home/ttv/invideoapp/invideoapp/invideomodules/auth_processing.py", line 8, in getUser
    query = session.query(User).filter(User.user_id == userid).first()
  File "/usr/local/lib64/python3.5/site-packages/sqlalchemy/orm/query.py", line 2755, in first
    ret = list(self[0:1])
  File "/usr/local/lib64/python3.5/site-packages/sqlalchemy/orm/query.py", line 2547, in __getitem__
    return list(res)
  File "/usr/local/lib64/python3.5/site-packages/sqlalchemy/orm/query.py", line 2855, in __iter__
    return self._execute_and_instances(context)
  File "/usr/local/lib64/python3.5/site-packages/sqlalchemy/orm/query.py", line 2876, in _execute_and_instances
    close_with_result=True)
  File "/usr/local/lib64/python3.5/site-packages/sqlalchemy/orm/query.py", line 2885, in _get_bind_args
    **kw
  File "/usr/local/lib64/python3.5/site-packages/sqlalchemy/orm/query.py", line 2867, in _connection_from_session
    conn = self.session.connection(**kw)
  File "/usr/local/lib64/python3.5/site-packages/sqlalchemy/orm/session.py", line 966, in connection
    execution_options=execution_options)
  File "/usr/local/lib64/python3.5/site-packages/sqlalchemy/orm/session.py", line 971, in _connection_for_bind
    engine, execution_options)
  File "/usr/local/lib64/python3.5/site-packages/sqlalchemy/orm/session.py", line 417, in _connection_for_bind
    self.session.dispatch.after_begin(self.session, self, conn)
  File "/usr/local/lib64/python3.5/site-packages/sqlalchemy/event/attr.py", line 256, in __call__
    fn(*args, **kw)
  File "/usr/local/lib/python3.5/site-packages/zope/sqlalchemy/datamanager.py", line 237, in after_begin
    join_transaction(session, self.initial_state, self.transaction_manager, self.keep_session)
  File "/usr/local/lib/python3.5/site-packages/zope/sqlalchemy/datamanager.py", line 211, in join_transaction
    DataManager(session, initial_state, transaction_manager, keep_session=keep_session)
  File "/usr/local/lib/python3.5/site-packages/zope/sqlalchemy/datamanager.py", line 73, in __init__
    transaction_manager.get().join(self)
  File "/usr/local/lib/python3.5/site-packages/transaction/_transaction.py", line 179, in join
    self._prior_operation_failed() # doesn't return
  File "/usr/local/lib/python3.5/site-packages/transaction/_transaction.py", line 173, in _prior_operation_failed
    self._failure_traceback.getvalue())
transaction.interfaces.TransactionFailedError: An operation previously failed, with traceback:

  File "/usr/lib64/python3.5/threading.py", line 882, in _bootstrap
    self._bootstrap_inner()
  File "/usr/lib64/python3.5/threading.py", line 914, in _bootstrap_inner
    self.run()
  File "/usr/lib64/python3.5/threading.py", line 862, in run
    self._target(*self._args, **self._kwargs)
  File "/usr/local/lib/python3.5/site-packages/waitress/task.py", line 78, in handler_thread
    task.service()
  File "/usr/local/lib/python3.5/site-packages/waitress/channel.py", line 338, in service
    task.service()
  File "/usr/local/lib/python3.5/site-packages/waitress/task.py", line 169, in service
    self.execute()
  File "/usr/local/lib/python3.5/site-packages/waitress/task.py", line 399, in execute
    app_iter = self.channel.server.application(env, start_response)
  File "/usr/local/lib/python3.5/site-packages/pyramid/router.py", line 270, in __call__
    response = self.execution_policy(environ, self)
  File "/usr/local/lib/python3.5/site-packages/pyramid_retry/__init__.py", line 114, in retry_policy
    response = router.invoke_request(request)
  File "/usr/local/lib/python3.5/site-packages/pyramid/router.py", line 249, in invoke_request
    response = handle_request(request)
  File "/usr/local/lib/python3.5/site-packages/pyramid_tm/__init__.py", line 136, in tm_tween
    response = handler(request)
  File "/usr/local/lib/python3.5/site-packages/pyramid/tweens.py", line 39, in excview_tween
    response = handler(request)
  File "/usr/local/lib/python3.5/site-packages/pyramid/router.py", line 156, in handle_request
    view_name
  File "/usr/local/lib/python3.5/site-packages/pyramid/view.py", line 642, in _call_view
    response = view_callable(context, request)
  File "/usr/local/lib/python3.5/site-packages/pyramid/viewderivers.py", line 439, in rendered_view
    result = view(context, request)
  File "/usr/local/lib/python3.5/site-packages/pyramid/viewderivers.py", line 148, in _requestonly_view
    response = view(request)
  File "/home/ttv/invideoapp/invideoapp/views/default.py", line 148, in upload_image
    transaction.commit()
  File "/usr/local/lib/python3.5/site-packages/transaction/_manager.py", line 131, in commit
    return self.get().commit()
  File "/usr/local/lib/python3.5/site-packages/transaction/_transaction.py", line 308, in commit
    t, v, tb = self._saveAndGetCommitishError()
  File "/usr/local/lib/python3.5/site-packages/transaction/_transaction.py", line 301, in commit
    self._commitResources()
  File "/usr/local/lib/python3.5/site-packages/transaction/_transaction.py", line 446, in _commitResources
    reraise(t, v, tb)
  File "/usr/local/lib/python3.5/site-packages/transaction/_compat.py", line 54, in reraise
    raise value
  File "/usr/local/lib/python3.5/site-packages/transaction/_transaction.py", line 423, in _commitResources
    rm.tpc_vote(self)
  File "/usr/local/lib/python3.5/site-packages/zope/sqlalchemy/datamanager.py", line 109, in tpc_vote
    self.tx.commit()
  File "/usr/local/lib64/python3.5/site-packages/sqlalchemy/orm/session.py", line 459, in commit
    self._assert_active(prepared_ok=True)
  File "/usr/local/lib64/python3.5/site-packages/sqlalchemy/orm/session.py", line 285, in _assert_active
    raise sa_exc.ResourceClosedError(closed_msg)
sqlalchemy.exc.ResourceClosedError: This transaction is closed

Once I get this error, none of my Database connection stuff works. Only way out is to restart the entire application to make things work. Any idea why this is happening?

Answer 1

I had a similar problem, and I found using DBSession.begin_nested() solved the issue. So something like:

@view_config(route_name='social_get_individual_assets', renderer='json', effective_principals=2, permission='edit')
def social_get_individual_assets(requestJson):
    try:
        DBSession.begin_nested()
        body = requestJson.request.json_body
        search_term = body['text']
        horizontal = body['horizontal']
        vertical = body['vertical']
        log.info("social get individual assets request: %s", str(search_term).encode(encoding='utf_8'))
        json_data = get_individual_assets(session, search_term, horizontal, vertical)
        log.info("social get assets response: %s", str(search_term).encode(encoding='utf_8'))
        return json_data
    except Exception as e:
        DBSession.rollback()
        log.exception(e)
        raise e