PyMySQL execute_stack skips commit when autocommit is disabled

[cofin]

Summary

While keeping litestar-queues stale recovery on StatementStack, the PyMySQL adapter required an explicit driver.commit() after driver.execute_stack(stack). Without that post-stack commit, stack writes were not reliably visible to later sessions.

This looks adapter-specific: DuckDB works when execute_stack() owns the transaction, and fails if the caller manually begins before stack execution. PyMySQL appears to report an active transaction whenever autocommit is disabled, which can make SQLSpec treat the stack as running inside a caller-owned transaction and skip its internal commit.

Observed behavior

The queue backend flow is:

1. Select stale rows.
2. Build a StatementStack of task state updates.
3. Roll back any implicit read transaction before stack execution.
4. Call await driver.execute_stack(stack).

For PyMySQL only, litestar-queues now has to do:

await driver.execute_stack(stack)
if adapter_name == "pymysql":
    await driver.commit()

The workaround landed in cofin/litestar-queues PR #2 because the MySQL/PyMySQL stale-recovery contract did not behave correctly without the explicit post-stack commit.

Expected behavior

If execute_stack() is responsible for the stack write transaction, PyMySQL should commit the stack writes consistently with other adapters when there is no real caller-managed transaction. If PyMySQL intentionally requires caller-managed commits here, the transaction ownership contract should be documented and made detectable in a way that does not confuse autocommit-off connections with explicit caller-owned transactions.

Suspected cause

PyMySQL/autocommit-off transaction state may be causing SQLSpec to decide that a transaction is already active, so the stack runner executes statements but skips the commit path.

References

• litestar-queues workaround commit: cofin/litestar-queues@7122f46
• Follow-up PyMySQL session rollback stabilization: cofin/litestar-queues@e674de3
• PR context: feat: ship the v1 queue runtime and backends cofin/litestar-queues#2

Local validation after the workaround included the mysql-pymysql stale recovery contract and the full src/tests integration run on Python 3.12.