try not to call .rollback() in ctx manager in committed state

[zzzeek]

the trace linked at #7286 shows the problem

in session.commit(), if session.close() raises an exception (connection return to pool failed because of mysql can't .rollback()), the context manager will do a rollback(), which then hits the assertion at

sqlalchemy/lib/sqlalchemy/orm/session.py

Line 852 in 55e0497

self._assert_active(prepared_ok=True, rollback_ok=True)

i can't think of a safe way to not actually call rollback() here so I think making a more clear exception is the best we can do. propose messaging change as follows:

diff --git a/lib/sqlalchemy/orm/session.py b/lib/sqlalchemy/orm/session.py
index 8212a111d5..3bbd40bb54 100644
--- a/lib/sqlalchemy/orm/session.py
+++ b/lib/sqlalchemy/orm/session.py
@@ -583,13 +583,26 @@ class SessionTransaction(TransactionalContext):
         prepared_ok=False,
         rollback_ok=False,
         deactive_ok=False,
+        is_rollback=False,
         closed_msg="This transaction is closed",
     ):
         if self._state is COMMITTED:
-            raise sa_exc.InvalidRequestError(
-                "This session is in 'committed' state; no further "
-                "SQL can be emitted within this transaction."
-            )
+            if is_rollback:
+                raise sa_exc.InvalidRequestError(
+                    "This session is in 'committed' state, indicating the "
+                    "SQL transaction has committed successfully, however the "
+                    "connectivity resources have not yet been successfully "
+                    "released; rollback() cannot proceed.  If this error is "
+                    "occurring within a context manager against begin() "
+                    "that calls rollback() automatically, examine the "
+                    "exception chain to determine the cause of the failure "
+                    "to release resources."
+                )
+            else:
+                raise sa_exc.InvalidRequestError(
+                    "This session is in 'committed' state; no further "
+                    "SQL can be emitted within this transaction."
+                )
         elif self._state is PREPARED:
             if not prepared_ok:
                 raise sa_exc.InvalidRequestError(
@@ -849,7 +862,7 @@ class SessionTransaction(TransactionalContext):
         return self._parent
 
     def rollback(self, _capture_exception=False, _to_root=False):
-        self._assert_active(prepared_ok=True, rollback_ok=True)
+        self._assert_active(prepared_ok=True, rollback_ok=True, is_rollback=True)
 
         stx = self.session._transaction
         if stx is not self:

to test this, need to mock out connection.close() such that it will throw an exception.

[sqla-tester]

Mike Bayer has proposed a fix for this issue in the main branch:

contextmanager skips rollback if trans says to skip it https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/3343

[sqla-tester]

Mike Bayer has proposed a fix for this issue in the rel_1_4 branch:

contextmanager skips rollback if trans says to skip it https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/3344