Marc-Andre Lemburg <m...@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 <rep...@bugs.python.org> <https://bugs.python.org/issue39457> _______________________________________ _______________________________________________ Python-bugs-list mailing list Unsubscribe: https://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com
