Exception during closing sessions if other thread just closed connection in parallel

[Dariusz Dzialak]

I've got system with many threads, in case of shutdown and even got some trouble with joining other thread I want to close all connections and it may happen that I will receive an error:

    self._session_factory.close_all()
  File "/var/lib/jenkins/shiningpanda/jobs/5156f59f/virtualenvs/d41d8cd9/lib/python3.5/site-packages/sqlalchemy/orm/session.py", line 57, in close_all
    sess.close()
  File "/var/lib/jenkins/shiningpanda/jobs/5156f59f/virtualenvs/d41d8cd9/lib/python3.5/site-packages/sqlalchemy/orm/session.py", line 1125, in close
    self._close_impl(invalidate=False)
  File "/var/lib/jenkins/shiningpanda/jobs/5156f59f/virtualenvs/d41d8cd9/lib/python3.5/site-packages/sqlalchemy/orm/session.py", line 1164, in _close_impl
    transaction.close(invalidate)
  File "/var/lib/jenkins/shiningpanda/jobs/5156f59f/virtualenvs/d41d8cd9/lib/python3.5/site-packages/sqlalchemy/orm/session.py", line 542, in close
    connection.close()
  File "/var/lib/jenkins/shiningpanda/jobs/5156f59f/virtualenvs/d41d8cd9/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 867, in close
    del self.__connection
_Connection__connection

The other thread that was ongoing and logged an exception (that seems to be accurate/correct):

Traceback (most recent call last):
  File "/var/lib/jenkins/shiningpanda/jobs/5156f59f/virtualenvs/d41d8cd9/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 346, in connection
    return self.__connection
AttributeError: 'Connection' object has no attribute '_Connection__connection'

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/var/lib/jenkins/shiningpanda/jobs/5156f59f/virtualenvs/d41d8cd9/lib/python3.5/site-packages/sqlalchemy/pool.py", line 687, in _finalize_fairy
    fairy._reset(pool)
  File "/var/lib/jenkins/shiningpanda/jobs/5156f59f/virtualenvs/d41d8cd9/lib/python3.5/site-packages/sqlalchemy/pool.py", line 827, in _reset
    self._reset_agent.rollback()
  File "/var/lib/jenkins/shiningpanda/jobs/5156f59f/virtualenvs/d41d8cd9/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1615, in rollback
    self._do_rollback()
  File "/var/lib/jenkins/shiningpanda/jobs/5156f59f/virtualenvs/d41d8cd9/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1653, in _do_rollback
    self.connection._rollback_impl()
  File "/var/lib/jenkins/shiningpanda/jobs/5156f59f/virtualenvs/d41d8cd9/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 706, in _rollback_impl
    self.connection._reset_agent is self.__transaction:
  File "/var/lib/jenkins/shiningpanda/jobs/5156f59f/virtualenvs/d41d8cd9/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 351, in connection
    self._handle_dbapi_exception(e, None, None, None, None)
  File "/var/lib/jenkins/shiningpanda/jobs/5156f59f/virtualenvs/d41d8cd9/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1396, in _handle_dbapi_exception
    util.reraise(*exc_info)
  File "/var/lib/jenkins/shiningpanda/jobs/5156f59f/virtualenvs/d41d8cd9/lib/python3.5/site-packages/sqlalchemy/util/compat.py", line 186, in reraise
    raise value
  File "/var/lib/jenkins/shiningpanda/jobs/5156f59f/virtualenvs/d41d8cd9/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 349, in connection
    return self._revalidate_connection()
  File "/var/lib/jenkins/shiningpanda/jobs/5156f59f/virtualenvs/d41d8cd9/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 429, in _revalidate_connection
    raise exc.ResourceClosedError("This Connection is closed")
sqlalchemy.exc.ResourceClosedError: This Connection is closed

I think that the first one exception should not happen. Is it possible to guarantee that all connections would be closed?

[Mike Bayer]

if you're doing non-threadsafe access to a Connection you should assume chaos will ensue, the Connection is advertised as not thread safe. That one scenario happens to catch the exception in the context of another exception catcher that looks nicer does not change the fact that the original exception here is that of a Connection which has had its internal state yanked away unexpectedly. There's lots of ways to "guarantee all connections are closed" but it depends very much on specifics, e.g. is this a test suite? is this a server of some kind with multiple processes? etc. The discussion of this should be done on the mailing list, feel free to pick it up there, thanks.

[Dariusz Dzialak]

OK. Thanks for reply. In my case that happened in tests (circumstances are quite complicated so case is very rare and unlikely to happen in production, anyway still possible to have unclean shutdown). We have very much tests and many cases with shutting down service and until now it happened only few times. I will: - stop/kill other threads before quiting (and a bit longer timeout to join threads) - attempt to retry five times to close all connection before reraising AttributeError exception In my case that should be enough.

Thanks&Regards Darek