Friday, January 31, 2020, 1:14:26 AM, Peng Yu <pengyu...@gmail.com> 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
