TL;DR: adding new
autocommit attribute to sqlite3 connection objects for controlling PEP 249 compliant transaction behaviour.
The sqlite3 extension module has a longstanding bug that relate to its implicit transaction handling; implicit transactions are only opened for DML statements. According to PEP 249 and @malemburg, this is wrong:
The DB-API wants drivers to have connections default to transactional behavior and implicitly start transactions when opening the connection as well as when ending one (via .commit() or .rollback()) - provided the backend does support transactions.
Excerpts from PEP 249
Note that if the database supports an auto-commit feature, this must be initially off. An interface method may be provided to turn it back on.
In case a database does provide transactions this method [rollback()] causes the database to roll back to the start of any pending transaction. Closing a connection without committing the changes first will cause an implicit rollback to be performed.
Changing the implicit transaction behaviour is close to impossible without breaking existing applications, so in order to resolve this, we must introduce a new (PEP 249 compliant) way of controlling transaction behaviour in the sqlite3 extension module.
Two years ago, a suggestion surfaced, based on previous discussions: gh-83638: “Add an autocommit property to sqlite3.Connection with a PEP 249 compliant manual commit mode and migrate”
MAL chimed in and proposed a concrete solution, based on @maggyero’s OP:
Details of MAL's suggestion
I guess what could be done is to add a connection.autocommit,
defaulting to None, meaning “use the pre-3.10 behavior”.
If this is set to False, the module could then implement the
correct way of handling transactions, which means:
a) start a new transaction when the connection is opened
b) start a new transaction after .commit() and .rollback()
c) don’t start new transactions anywhere else
d) run an implicit .rollback() when the connection closes
The code could even check for “BEGIN”, “ROLLBACK” and “COMMIT”
text in the .execute() and issues a warning when connection.autocommit
is set to True or False.
When set to True, the module would set the SQLite autocommit
flag and also issues warnings for the txn statements. .rollback()
would issue an exception and .commit() pass silently.
Recently, the discussion was revived by Mike Bayer of SQLAlchemy and me. I created this topic to try to raise more awareness on the proposed change.
Oh, I forgot to mention the bikeshedding. We need a name for the constant that selects pre-3.12 transaction control. Currently, there is only one suggestion:
We can also bikeshed regarding the name of the proposed new attribute. So far, the argument for naming it “autocommit”, is that that name has gradually become a standard amongst PEP 249-compliant database interfaces. There is an argument against naming it “autocommit”, in that SQLite has its own “autocommit concept”.
IMO, we can solve the potential confusion by writing clear docs; the name “autocommit” stays.
UPDATE: with the proposed update to PEP-249, there is no need to change the name of the proposed attribute.
Indeed, connection.autocommit has become a standard among database modules.
I’ll kick off the process to add this to PEP 249 as a standard
Just to confirm, this is the usual meaning of Connection.autocommit (and a draft of what I’ll kick off the process with):
Attribute to query and set the autocommit mode of the connection.
True if the connection is operating in autocommit (non-transactional) mode. Returns
False is the connection is operating in manual commit (transactional) mode.
Setting the attribute to
False adjusts the connection’s mode accordingly.
The details around changing the value are database dependent, e.g. changing from
False will usually start a new transaction, while changing from
True will usually cause the current transaction to be committed. However, it is also possible that a database raises an error in case there are pending changes to be committed. To prevent the latter case, it is best to explicitly commit or rollback the current transaction before switching on autocommit mode on the connection.
It may make sense to have the DB API modules enforce the behavior described as “usual” behavior.
Judging from the responses, I’ll continue with the proposed change in the following separate steps:
Mark gh-54133 and (one part of) gh-81040 as superseded by gh-83638
Add the new PEP-249 compliant
isolation_level as deprecated as of Python 3.12
The more I think about it, the more I’m leaning towards making the “usual” behavior mandatory. It should be easy to implement for database drivers and reduces possible confusion around change semantics.
+1, reducing potential confusion is always a good idea