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

Reply via email to