Bad state after commit error when using sqlite lazy transaction workaround and sqlalchemy 1.4

[groner]

BUG/PROBLEM REPORT (OR OTHER COMMON ISSUE)

While upgrading a project from sqlalchemy 1.3 to 1.4, I ran into this problem that only occurs when using a sqlite database with this workaround for the pysqlite lazy transaction behavior.

Following a failed commit, the database connection needs to be rollback before it can be used again (keep_session is not being used).

When using a TransactionManager in explicit mode, attempts to start a new transaction generate an AlreadyInTransaction exception (even though we are using context managers to scope transactions). This happens with sqlite even without the lazy transaction workaround, but it does not happen with postgres.

With SQLAlchemy 1.4

	session.begin()	TransactionManager(explicit=False)	TransactionManager(explicit=True)
default pysqlite transaction behavior	✅	✅	AlreadyInTransaction
explicit sqlite transactions	✅	OperationalError	AlreadyInTransaction
postgresql	✅	✅	✅

With SQLAlchemy 1.3

	session.transaction	TransactionManager(explicit=False)	TransactionManager(explicit=True)
default pysqlite transaction behavior	✅	✅	AlreadyInTransaction
explicit sqlite transactions	✅	✅	AlreadyInTransaction
postgresql	✅	✅	✅

What I did:

Minimal test case at https://gist.github.com/8bee5b88dea060eaafb0402208771e65

try:
  with transaction.manager:
    db.execute(DEFERRED_ERROR_QUERY)
except sa.exc.IntegrityError:
  pass

with transaction.manager:
  db.execute(BENIGN_QUERY)

What I expect to happen:

The second transaction should succeed.

What actually happened:

The second transaction fails with

(sqlite3.OperationalError) cannot start a transaction within a transaction

or (when using an explicit mode TransactionManager)

AlreadyInTransaction

What version of Python and Zope/Addons I am using:

CPython 3.6 (but also verified with 3.10)
SQLAlchemy==1.4.41
transaction==3.0.1
zope.sqlalchemy==1.6

[groner]

I amended the gist so it can run with SQLAlchemy 1.3.

Using SQLAlchemy 1.3 the OperationalError does not occur, but the AlreadyInTransaction exception remains.