Re: [sqlite] Default isolation_level for sqlite3.connect?
> 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 On Behalf Of Peng Yu Sent: Friday, January 31, 2020 9:11 AM To: SQLite mailing list 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 >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 >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 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
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 >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 >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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Default isolation_level for sqlite3.connect?
Friday, January 31, 2020, 1:14:26 AM, Peng Yu 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 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 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Default isolation_level for sqlite3.connect?
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 On >Behalf Of Peng Yu >Sent: Thursday, 30 January, 2020 18:14 >To: SQLite mailing list >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 transact
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
Re: [sqlite] Default isolation_level for sqlite3.connect?
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 On
>Behalf Of Peng Yu
>Sent: Thursday, 30 January, 2020 09:55
>To: SQLite mailing list
>Subject: Re: [sqlite] Default isolation_level for sqlite3.connect?
>
>On 1/30/20, Keith Medcalf 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
>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
Re: [sqlite] Default isolation_level for sqlite3.connect?
On 1/30/20, Keith Medcalf 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Default isolation_level for sqlite3.connect?
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 On
>Behalf Of Peng Yu
>Sent: Thursday, 30 January, 2020 08:59
>To: SQLite mailing list
>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
>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
Re: [sqlite] Default isolation_level for sqlite3.connect?
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 On Behalf Of Peng Yu Sent: Thursday, January 30, 2020 11:29 AM To: SQLite mailing list 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 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
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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Default isolation_level for sqlite3.connect?
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 On Behalf Of Peng Yu Sent: Thursday, January 30, 2020 10:59 AM To: SQLite mailing list 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 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
