At any given instant in time a connection can either (a) have a transaction in progress or (b) have no transaction in progress. An SQL statement cannot be executed EXCEPT inside of a transaction. "autocommit" means that the SQLite3 database engine (not the sqlite3 wrapper) will start the necessary transaction when a statement commences executing and commit (end the transaction) automatically when the statement finishes execution.
You may issue one of the various BEGIN or SAVEPOINT commands to the SQLite3 database engine (not the sqlite3 wrapper) to START a transaction. If a transaction is in process when a statement begins execution then that statement will not start a transaction, nor will the statements completion autocommit the transaction. You must do this yourself with the COMMIT / ROLLBACK / RELEASE commands. The isolation_level setting of the sqlite3 wrapper for a connection tells the sqlite3 wrapper (not the SQLite3 engine) whether or not it should execute commands to BEGIN transactions magically or not, and what type of magical BEGIN commands to emit. Setting the isolation_level to None disables the crystal ball in the sqlite3 wrapper so that it *never* issues BEGIN commands of any type (by itself) and only executes the commands you tell it to execute when you tell it to execute them, placing you in control of the transaction and autocommit mode of the SQLite3 library and not the sqlite3 wrapper. The current version of pysqlite2 (2.8.2 and later) uses the following method to determine whether to automagically start a transaction: 1) The isolation_level is not None 2) The statement is not a DDL statement (the definition of DDL being that the first token on the line is "create", "drop", or "reindex") 3) The underlying SQLite3 engine connection is in autocommit mode (not in a transaction) 4) The underlying SQLite3 engine indicates the statement is not readonly. If all the above conditions are met then the sqlite3 wrapper issues the applicable BEGIN command before executing the requested SQL statement. This means that some DDL statements (eg ALTER) are treated as DML not DDL. However, the version pysqlite2 (sqlite3) distributed with even the most recent 3.8.1 version of Python still uses the old (2.6.0 version of pysqlite2) unreliable crystal ball which uses the following: 1) The isolation_level is not None 2) The statement is a DML statement (first token is "insert", "update", "delete", "replace") 3) The underlying SQLite3 connection is in autocommit mode. If all the above conditions are met then the sqlite3 wrapper issues the applicable BEGIN command before executing the requested SQL statement. This means that it is possible for some statements (example WITH ... INSERT) to not be recognized as performing updates and therefore not starting a transaction. In all cases you can still issue the various BEGIN and SAVEPOINT commands manually. Since you cannot always be sure when the sqlite3/pysqlite2 wrapper will automagically start transactions (especially since the magic can change at any time from version to version), you are better off setting isolation_level=None and doing transaction management yourself. -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> On >Behalf Of Peng Yu >Sent: Thursday, 30 January, 2020 18:14 >To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> >Subject: Re: [sqlite] Default isolation_level for sqlite3.connect? > >I still have a hard time to understand what the difference is >according to the python manual. It keeps saying see somewhere else in >the python manual. But I don't see where it explains the differences >between the differences comprehensively and clearly. > >https://www.sqlite.org/lang_transaction.html > >On the above page, I only see > >- BEGIN >- BEGIN DEFERRED >- BEGIN IMMEDIATE >- BEGIN EXCLUSIVE > >, where BEGIN is just BEGIN DEFERRED. > >But what is isolation_level = None? > >https://docs.python.org/3.8/library/sqlite3.html#sqlite3-controlling- >transactions > >"The underlying sqlite3 library operates in autocommit mode by >default, but the Python sqlite3 module by default does not." > >According to the above, since isolation_level="" is the default in >python, so it is not autocommit and it is BEGIN. > >But what is autocommit mode? The following doesn't provide a >definition. Is there a definition on sqlite.com website? > >https://www.sqlite.org/c3ref/get_autocommit.html > >> The default is an empty string (ie, ''). It can take the value None, >'', >> 'DEFERRED', 'IMMEDIATE', 'EXCLUSIVE' and the value (if not None) is >appended >> to the BEGIN when a magical transaction is started, and if None, then >you >> are indicating that you will be using manual transaction control >> >> isolation_level command >> None None >> '' (default) BEGIN >> 'DEFERRED' BEGIN DEFERRED >> 'IMMEDIATE' BEGIN IMMEDIATE >> 'EXCLUSIVE' BEGIN EXCLUSIVE >> >> You can set the isolation_level in the connect call (the default is '') >or >> read/change it with the .isolation_level property of a connection >object. > >-- >Regards, >Peng >_______________________________________________ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users