Default isolation_level for sqlite3.connect?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
13 messages Options
Reply | Threaded
Open this post in threaded view
|

Default isolation_level for sqlite3.connect?

Peng Yu
Hi,

I don't see what is the default isolation_level here. Is it None? Thanks.

https://docs.python.org/3/library/sqlite3.html#module-functions-and-constants
sqlite3.connect(database[, timeout, detect_types, isolation_level,
check_same_thread, factory, cached_statements, uri])¶

--
Regards,
Peng
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Default isolation_level for sqlite3.connect?

David Raymond
https://docs.python.org/3.8/library/sqlite3.html#sqlite3-controlling-transactions

"You can control which kind of BEGIN statements sqlite3 implicitly executes via the isolation_level parameter to the connect() call, or via the isolation_level property of connections. If you specify no isolation_level, a plain BEGIN is used, which is equivalent to specifying DEFERRED. Other possible values are IMMEDIATE and EXCLUSIVE."

https://www.sqlite.org/lang_transaction.html
So basically if you explicitly specify one it will issue either a "begin deferred;", "begin immediate;", or "begin exclusive;",
and if you don't specify one it issues a plain "begin;"


-----Original Message-----
From: sqlite-users <[hidden email]> On Behalf Of Peng Yu
Sent: Thursday, January 30, 2020 10:59 AM
To: SQLite mailing list <[hidden email]>
Subject: [sqlite] Default isolation_level for sqlite3.connect?

Hi,

I don't see what is the default isolation_level here. Is it None? Thanks.

https://docs.python.org/3/library/sqlite3.html#module-functions-and-constants
sqlite3.connect(database[, timeout, detect_types, isolation_level,
check_same_thread, factory, cached_statements, uri])¶

--
Regards,
Peng
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Default isolation_level for sqlite3.connect?

Peng Yu
> and if you don't specify one it issues a plain "begin;"

So that is basically isolation_level = None? Thanks.

--
Regards,
Peng
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Default isolation_level for sqlite3.connect?

David Raymond
Incorrect.

"Not specifying one" is
sqlite3.connect(fi)
And the connection will still start implicit transactions for you. (with "begin;")

"Setting it to None" is
sqlite3.connect(fi, isolation_level = None)
Which will turn off all implicit transactions, put it in autocommit mode, and you have to do all transaction management yourself.


-----Original Message-----
From: sqlite-users <[hidden email]> On Behalf Of Peng Yu
Sent: Thursday, January 30, 2020 11:29 AM
To: SQLite mailing list <[hidden email]>
Subject: Re: [sqlite] Default isolation_level for sqlite3.connect?

> and if you don't specify one it issues a plain "begin;"

So that is basically isolation_level = None? Thanks.

--
Regards,
Peng
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Default isolation_level for sqlite3.connect?

Keith Medcalf
In reply to this post by Peng Yu

The isolation_level specifies the default suffix to put after 'BEGIN' when beginning a transaction.  Inside the library the following is used when the magic wants to start a transaction:

if isolation_level is not None:
   .execute('BEGIN %s' % isolation_level)

This is so that you can set isolation_level to 'IMMEDIATE' so that all magically initiated transactions are BEGIN IMMEDIATE rather than the default BEGIN DEFERRED

--
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 <[hidden email]> On
>Behalf Of Peng Yu
>Sent: Thursday, 30 January, 2020 08:59
>To: SQLite mailing list <[hidden email]>
>Subject: [sqlite] Default isolation_level for sqlite3.connect?
>
>Hi,
>
>I don't see what is the default isolation_level here. Is it None? Thanks.
>
>https://docs.python.org/3/library/sqlite3.html#module-functions-and-
>constants
>sqlite3.connect(database[, timeout, detect_types, isolation_level,
>check_same_thread, factory, cached_statements, uri])¶
>
>--
>Regards,
>Peng
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Default isolation_level for sqlite3.connect?

Peng Yu
On 1/30/20, Keith Medcalf <[hidden email]> wrote:

>
> The isolation_level specifies the default suffix to put after 'BEGIN' when
> beginning a transaction.  Inside the library the following is used when the
> magic wants to start a transaction:
>
> if isolation_level is not None:
>    .execute('BEGIN %s' % isolation_level)
>
> This is so that you can set isolation_level to 'IMMEDIATE' so that all
> magically initiated transactions are BEGIN IMMEDIATE rather than the default
> BEGIN DEFERRED

Are you saying the default isolation_level is equivalent to IMMEDIATE?
Or you are not answering my specific question of what is the default
of isolation_level? I think the default paremeter value of
isolation_level of the function sqlite3.connect() is just None. It is
not the case?

--
Regards,
Peng
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Default isolation_level for sqlite3.connect?

Keith Medcalf

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.

>>> import sqlite3
>>> db = sqlite3.connect(':memory:')
>>> db.isolation_level
''
>>> db.isolation_level = None
>>> db.isolation_level
>>>
>>> db = sqlite3.connect(':memory:', isolation_level=None)
>>> db.isolation_level
>>>

--
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 <[hidden email]> On
>Behalf Of Peng Yu
>Sent: Thursday, 30 January, 2020 09:55
>To: SQLite mailing list <[hidden email]>
>Subject: Re: [sqlite] Default isolation_level for sqlite3.connect?
>
>On 1/30/20, Keith Medcalf <[hidden email]> wrote:
>>
>> The isolation_level specifies the default suffix to put after 'BEGIN'
>when
>> beginning a transaction.  Inside the library the following is used when
>the
>> magic wants to start a transaction:
>>
>> if isolation_level is not None:
>>    .execute('BEGIN %s' % isolation_level)
>>
>> This is so that you can set isolation_level to 'IMMEDIATE' so that all
>> magically initiated transactions are BEGIN IMMEDIATE rather than the
>default
>> BEGIN DEFERRED
>
>Are you saying the default isolation_level is equivalent to IMMEDIATE?
>Or you are not answering my specific question of what is the default
>of isolation_level? I think the default paremeter value of
>isolation_level of the function sqlite3.connect() is just None. It is
>not the case?
>
>--
>Regards,
>Peng
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Default isolation_level for sqlite3.connect?

Peng Yu
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Default isolation_level for sqlite3.connect?

Keith Medcalf

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 <[hidden email]> On
>Behalf Of Peng Yu
>Sent: Thursday, 30 January, 2020 18:14
>To: SQLite mailing list <[hidden email]>
>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
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Re: Default isolation_level for sqlite3.connect?

Hick Gunter
In reply to this post by Peng Yu
"Autocommit" means that each SQL Statement executes in it's own transaction. Just as if you were to execute "begin; <statement>; commit;" each time.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Peng Yu
Gesendet: Freitag, 31. Januar 2020 02:14
An: SQLite mailing list <[hidden email]>
Betreff: [EXTERNAL] 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Default isolation_level for sqlite3.connect?

Graham Holden
In reply to this post by Peng Yu
Friday, January 31, 2020, 1:14:26 AM, Peng Yu <[hidden email]> wrote:

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

Let me offer another answer to go alongside Keith's excellent
response.


CORE SQLITE3 ENGINE

(Ignoring Python; as used from "C" or the command-line shell).

This operates in "auto-commit" mode. When a statement is executed that
is not already inside an EXPLICIT (=user-created) transaction, then it
is wrapped inside an IMPLICIT (=engine-created) transaction. Therefore:

   INSERT INTO TABLE_A ...
   BEGIN <whatever>
       INSERT INTO TABLE_B ...
       DELETE FROM TABLE_C ...
   COMMIT
   SELECT ... FROM TABLE_D

will essentially be turned into:

   BEGIN
       INSERT INTO TABLE_A ...
   COMMIT
   BEGIN <whatever>
       INSERT INTO TABLE_B ...
       DELETE FROM TABLE_C ...
   COMMIT
   BEGIN
       SELECT ... FROM TABLE_D
   COMMIT

where the auto-generated BEGINs are the equivalent of BEGIN DEFERRED
(the SQLite engine's default if you just use BEGIN).

To me, this behaviour is straightforward, predictable, and (mostly)
does what you (as a programmer) wants (but see the notes at the end).


PYTHON WRAPPER

Driven by what I assume were the best intentions, the Python wrapper
will, by default, try to be "helpful" and decide when IT will wrap
your SQL statements in BEGIN...COMMIT statements. How, and when it
does this is controlled by the ISOLATION_LEVEL.

If isolation_level is explicitly set to "None", then the Python
wrapper does nothing, and you get the "normal" behaviour of the core
SQLite3 engine as described above. As Keith recommends, and from what
I've seen on this mailing list, this is highly recommended when using
the Python wrapper. It makes everything done in Python work the same
way as in C or the command-line shell, with no unexpected surprises.

If you DON'T use isolation_level=None, then the Python wrapper will
enter what Keith calls "magic mode". It will SOMETIMES add its own
BEGIN ... COMMIT statements around your Python calls to SQLite.

By default (if you don't specify isolation_level) these added calls
will be simply BEGIN (which, the core SQLite engine treats as BEGIN
DEFERRED). Otherwise, you can explicitly specify an isolation_level
of DEFERRED (the same as leaving it blank), IMMEDIATE or EXCLUSIVE.

As Keith notes, the problem with letting Python insert these BEGIN
statements is that (a) different versions of the Python wrapper do
things in different ways, and (b) at least some of those ways "get
it wrong" (e.g. by not understanding things like WITH ... INSERT).

Overall, it seems far better to disable the Python wrapper's "magic"
mode (by using isolation_level=None) and have a far more predictable
environment where YOU manage transactions yourself.


FINAL NOTES

I said above that the auto-commit behaviour of the SQLite engine
"mostly" does what you want. One alternate behaviour would be to
insist that EVERY statement was inside an EXPLICIT transaction (and
raise an error if not). Most of the time, this would be somewhat
annoying (especially when using the command-line tool), but would
force the user/programmer to consciously think about when to use
transactions. For example, if you have:

    SELECT data1 FROM ...
    SELECT data2 FROM ...

then because each SELECT will be automatically wrapped inside their
own BEGIN ... COMMIT, it is entirely possible (="guaranteed to happen
the first time it is deployed in production") that some other process
will alter the data between the two SELECTs and you end up with
inconsistent values for "data1" and "data2". The answer, of course, is
to do:

    BEGIN [something]
        SELECT data1 FROM ...
        SELECT data2 FROM ...
    COMMIT

which guarantees consistency.

From my (moderate) use of SQL, (almost) always using explicit
transactions (rather than relying on the auto-commit behaviour of the
core) falls in to the same sort of list of  "good practices" as
explicitly declaring the columns in a SELECT statement (as opposed to
using SELECT *) and (depending on the programming language) always
using braces in an IF statement, even if there's only one statement
to conditionally execute. They're not essential, but tend to guard
against silly mistakes.


Regards,
Graham Holden


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Default isolation_level for sqlite3.connect?

Peng Yu
The following is still very confusing.

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

"""
autocommit mode means that statements that modify the database take
effect immediately."

...

The Python sqlite3 module by default issues a BEGIN statement
implicitly before a Data Modification Language (DML) statement (i.e.
INSERT/UPDATE/DELETE/REPLACE).
"""

Is there a complete list that defines what are "the statements that
modify the database"? What is the difference between "the statements
that modify the database" and "Data Modification Language (DML)
statement"? Are they the same?

Whether or not they are the same or not, providing a complete list of
such statements for each case should be far less confusing.

BTW, who is maintaining the python doc? Is it somebody from the
sqlite3 community or the python community? I hope that someone will go
over the document and resolve all the inconsistencies and excessive
"referring to other places references".

> This operates in "auto-commit" mode. When a statement is executed that
> is not already inside an EXPLICIT (=user-created) transaction, then it
> is wrapped inside an IMPLICIT (=engine-created) transaction. Therefore:
>
>    INSERT INTO TABLE_A ...
>    BEGIN <whatever>
>        INSERT INTO TABLE_B ...
>        DELETE FROM TABLE_C ...
>    COMMIT
>    SELECT ... FROM TABLE_D
>
> will essentially be turned into:
>
>    BEGIN
>        INSERT INTO TABLE_A ...
>    COMMIT
>    BEGIN <whatever>
>        INSERT INTO TABLE_B ...
>        DELETE FROM TABLE_C ...
>    COMMIT
>    BEGIN
>        SELECT ... FROM TABLE_D
>    COMMIT
>
> where the auto-generated BEGINs are the equivalent of BEGIN DEFERRED
> (the SQLite engine's default if you just use BEGIN).

--
Regards,
Peng
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Default isolation_level for sqlite3.connect?

David Raymond
> Is there a complete list that defines what are "the statements that
> modify the database"? What is the difference between "the statements
> that modify the database" and "Data Modification Language (DML)
> statement"? Are they the same?

If you dig into their code you can attempt to find what it is actually doing, but the documentation is somewhat ambiguous. (Keith included his findings from their code in one of his responses)


> Whether or not they are the same or not, providing a complete list of
> such statements for each case should be far less confusing.

Fully agreed.


You're basically discovering for yourself firsthand why we recommend using autocommit/setting isolation_level = None when opening a connection in Python. It basically comes down to "ambiguity is frustrating and prone to error, so do it yourself explicitly to be clear and safe."


> BTW, who is maintaining the python doc? Is it somebody from the
> sqlite3 community or the python community? I hope that someone will go
> over the document and resolve all the inconsistencies and excessive
> "referring to other places references".

That's the Python folks, since it's their wrapper. You'll have to dig a little to find whichever is the correct mailing list to raise your concerns on over there.



-----Original Message-----
From: sqlite-users <[hidden email]> On Behalf Of Peng Yu
Sent: Friday, January 31, 2020 9:11 AM
To: SQLite mailing list <[hidden email]>
Subject: Re: [sqlite] Default isolation_level for sqlite3.connect?

The following is still very confusing.

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

"""
autocommit mode means that statements that modify the database take
effect immediately."

...

The Python sqlite3 module by default issues a BEGIN statement
implicitly before a Data Modification Language (DML) statement (i.e.
INSERT/UPDATE/DELETE/REPLACE).
"""

Is there a complete list that defines what are "the statements that
modify the database"? What is the difference between "the statements
that modify the database" and "Data Modification Language (DML)
statement"? Are they the same?

Whether or not they are the same or not, providing a complete list of
such statements for each case should be far less confusing.

BTW, who is maintaining the python doc? Is it somebody from the
sqlite3 community or the python community? I hope that someone will go
over the document and resolve all the inconsistencies and excessive
"referring to other places references".

> This operates in "auto-commit" mode. When a statement is executed that
> is not already inside an EXPLICIT (=user-created) transaction, then it
> is wrapped inside an IMPLICIT (=engine-created) transaction. Therefore:
>
>    INSERT INTO TABLE_A ...
>    BEGIN <whatever>
>        INSERT INTO TABLE_B ...
>        DELETE FROM TABLE_C ...
>    COMMIT
>    SELECT ... FROM TABLE_D
>
> will essentially be turned into:
>
>    BEGIN
>        INSERT INTO TABLE_A ...
>    COMMIT
>    BEGIN <whatever>
>        INSERT INTO TABLE_B ...
>        DELETE FROM TABLE_C ...
>    COMMIT
>    BEGIN
>        SELECT ... FROM TABLE_D
>    COMMIT
>
> where the auto-generated BEGINs are the equivalent of BEGIN DEFERRED
> (the SQLite engine's default if you just use BEGIN).

--
Regards,
Peng
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users