Python DB API - commit() v. execute("commit transaction")?

[Neil Cerutti]

On 2017-06-02, Dennis Lee Bieber <wlfraed at ix.netcom.com> wrote:
>
> 	A bit of a long free-association rambling...
>
> On Fri, 2 Jun 2017 12:07:45 +0000 (UTC), Neil Cerutti
> <neilc at 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