when autocommit isolation mode used, rollback at transaction level is not resulting in rollback of continuum transactions

[chicco785]

as described in the title, when on an engine connection we configure connection.execution_options(isolation_level="AUTOCOMMIT") in case of transaction rollback, continuum related transactions are not removed from the database.

[marksteward]

Does this only affect SQLAlchemy 1.4 or above in future mode? Can you share some code to reproduce it?

[chicco785]

I am currently using SQLAlchemy 2.0 and continuum 1.4 version, i will try to provide you a minimal snipped of code to demo it (i need to extract it from my code)

[marksteward]

Also which DBMS are you using? I don't really understand what rollback for autocommit would even mean.

[chicco785]

i am testing it with 3 databases:

• SQLite
• MySQL
• PostgreSQL

All exhibit the same issue, so it's not related to a specific dbms. You can enable autocommit isolation level (for psql and sqlite, for mysql is a bit more tricky) as follows:

engine = create_engine("postgresql+pg8000://postgres:postgres@localhost:5432/test")
connection = engine.connect()
connection.execution_options(isolation_level="AUTOCOMMIT")
transaction = connection.begin()
session = Session(bind=connection)

then, if instead of committing the transaction, you roll it back:

session.close()
# roll back the broader transaction
transaction.rollback()
# put back the connection to the connection pool
connection.close()

while changes to my tables are correctly rolled back, transactions table created by continuum is still populated with transactions that actually never occurred, since we rolled them back.

[marksteward]

connection.begin should be a no-op under isolation_level="AUTOCOMMIT". You should see warnings in the logs to say so.

[marksteward]

I'm guessing you don't actually need autocommit semantics, but if you do, have a look at the native versioning plugin.

[chicco785]

thx