connection.rollback() undoes effect of an insert executed with autocommit = True

[srprogrammer]

Another simple SA dialect compliance suite test case which fails is tests/test_suite.py::AutocommitTest_mssql+ctds_14_0_3015_40::()::test_autocommit_on

It reduces itself to somethng like (in ctds terms conn is one of its DB-API-level Connection's):

conn.autocommit = True
trans = conn.begin()
conn.cursor().execute("INSERT ...")
trans.rollback()
# Here existence of the row inserted two lines above is tested. It should exist

It fails because the inserted row does not exist. If I comment out the rollback() then things do work as expected.

But rollback() (and FWIW begin()) shouldn't have effect on the row inserted by execute() as it happened under the "protection" of autocommit = True right?

[joshuahlang]

I'd have to look into what SQLAlchemy expects here, but basically the autocommit mode in ctds just relies on IMPLICIT_TRANSACTIONS. When SQLAlchemy issues an explict BEGIN TRANSACTION via the .begin() call it is circumventing the autocommit behavior by wrapping a transaction around the autocommit-ed INSERT statement. pymssql implements autocommit differently, and in fact implements .commit() and .rollback() differently by always having an open explicit transaction when autocommit is disabled (i.e. calls COMMIT TRAN; BEGIN TRAN; as seen here). There's probably some way to configure SQLAlchemy to handle this behavior, though I am not terribly familiar with SQLAlchemy (this library was primarily to help with ctds adoption).

[srprogrammer]

A post related, albeit for Postgres but possibly useful nonetheless, I keep coming back to read it from time to time: http://oddbird.net/2014/06/14/sqlalchemy-postgres-autocommit/

HTH