[issue39457] Add an autocommit property to sqlite3.Connection with a PEP 249 compliant manual commit mode and migrate

[Marc-Andre Lemburg]

Marc-Andre Lemburg <mal at egenix.com> added the comment:

On 05.01.2021 19:04, Géry wrote:
> 
> @lemburg
> 
>> I guess the SQLite driver does not start a new transaction for SELECTs, since these are usually read-only
> 
> Nor for DDL statements (CREATE, DROP).

Those are definitely changing the database and AFAIK SQLite
does support DDLs in transactions (including rolling them back
if needed).

Looking at the _sqlite code, the module does indeed only start
a transaction for INSERT, UPDATE, DELETE and REPLACE, with
"starting a transaction" meaning that it inserts a "BEGIN"
(or one of the txn isolation alternatives) before the statement:

https://github.com/python/cpython/blob/3.9/Modules/_sqlite/cursor.c#L489

This is also documented:

https://docs.python.org/3/library/sqlite3.html#controlling-transactions

I wonder why the module does not implement this properly, but I also
believe it's too late to change.

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.

>> For the same reason, removing the SELECT "optimization" may cause
>> a backwards incompatible change, which can be tricky to identify
>> and cause corruption of data (in this case, data not written to
>> the database, where it previously was written).
> 
> Since DQL statements (SELECT) are read-only, maybe we could keep the optimization and start transactions implicitly only for DDL statements (CREATE, DROP)?

See https://sqlite.org/c3ref/stmt_readonly.html.

SELECT are usually read-only, but not always. Since SQLite does
support UDFs (user defined functions), it is possible that a call
to such a function does change the database.

----------

_______________________________________
Python tracker <report at bugs.python.org>
<https://bugs.python.org/issue39457>
_______________________________________