Introducing a PEP 249 compliant transaction behaviour to sqlite3

TL;DR: adding new autocommit attribute to sqlite3 connection objects for controlling PEP 249 compliant transaction behaviour.

Background

The sqlite3 extension module has a longstanding bug that relate to its implicit transaction handling[1]; implicit transactions are only opened for DML[2] 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.

Proposed solution

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.


  1. gh-54133: sqlite3 SELECT does not BEGIN a transaction, but should according to spec ↩︎

  2. INSERT, UPDATE, DELETE, and REPLACE statements are regarded as DML statements by the sqlite3 extension module ↩︎

4 Likes

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:

  • sqlite3.DEPRECATED_TRANSACTION_CONTROL

:art: :bike: :house:

1 Like

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
extension
.

1 Like

Great, thanks!

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.

Returns 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 True or False adjusts the connection’s mode accordingly.

The details around changing the value are database dependent, e.g. changing from True to False will usually start a new transaction, while changing from False to 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.

1 Like

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 autocommit attribute
  • Mark isolation_level as deprecated as of Python 3.12
  • Close gh-83638
1 Like

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.

2 Likes

+1, reducing potential confusion is always a good idea :slight_smile:

1 Like