On 2017-06-02, Dennis Lee Bieber <wlfr...@ix.netcom.com> wrote:
>
> A bit of a long free-association rambling...
>
> On Fri, 2 Jun 2017 12:07:45 +0000 (UTC), Neil Cerutti
> <ne...@norwich.edu> declaimed the following:
>>You're probably not expected to interleave transaction control
>>commands from different levels of abstraction, e.g., only call
>>'commit' directly if you called 'begin' directly.
>
> .execute("begin")
> is likely not safe either.
>
> If the adapter has been set to "autocommit", it might issue an
> implicit "commit" after processing that execute -- wiping out
> the transaction one has explicitly started...
>
> If not in "autocommit", the adapter may (will) at some point
> issue an implicit "begin" -- resulting in an attempt to nest
> transactions within the one connection.
>
> My conclusion:
> If using a DB-API compliant adapter, explicitly issuing "begin" and
> "commit" via .execute() should be avoided if one expects to be portable
> (change the adapter from one DBMS to another).
> Learn the behavior of the adapter (does any SQL start a transaction, or
> only INSERT/UPDATE/DELETE/REPLACE -- the latter seems to be the
> current SQLite3 documented behavior, exclusive of both editions
> of the "Definitive Guide" which imply that an active
> transaction will be commited upon executing a SELECT [Python
> help file for module states that SELECT does /not/ commit]) so
> you understand when it should be IN or OUT of a transaction
> state. *
Good point!
> * Mixing various SQLite3 documentation (both the engine and Python's
> module) gives a confusing mix:
> The engine (per "Definite Guide") normally runs in autocommit -- and
> appears to only go into non-autocommit when a "begin" is issued.
> The module (per DB-API) runs in non-autocommit -- and issues an
> implicit "begin" on the first of those DML operations mentioned above.
> So... SELECT prior to any of the listed operations is effectively
> auto-commit, as are any DDL operations (with the addition that DDL will
> perform a commit IF the module believes a transaction is open).
You configure the BEGIN operation by setting isolation_level.
Setting it to IMMEDIATE (or EXCLUSIVE) avoids the deferral of
lock acquisition.
> Given the two -- turning on autocommit in the module may result
> in no implicit "begin"; and transaction control is totally up
> to the user .execute("begin|commit").
Agreed.
> But this behavior may not match up with /other/ adapters, in
> which turning ON autocommit in the adapter could just mean it
> does a sequence of begin/SQL/commit for every .execute(). (per
> documentation, not experience)
sqlite3 behavior in autocommit matches up except when I
explicitly muck things up with an explicit BEGIN.
Conclusion seems to be that sqlite3 has a mode that permits
explicit BEGIN/COMMIT, but you shouldn't do it *except* in that
mode, and it's not portable.
--
Neil Cerutti
--
https://mail.python.org/mailman/listinfo/python-list
