alt Hacker News> hides more fine-grained transaction control
In what way does having autocommit=False hide more fine-grained transaction control?
autocommit=False gives full control to the programmer to do whatever they want.
> hides more fine-grained transaction control
In what way does having autocommit=False hide more fine-grained transaction control?
autocommit=False gives full control to the programmer to do whatever they want.
From the link in my previous post[0]:
> False: Select PEP 249-compliant transaction behaviour, implying that sqlite3 ensures a transaction is always open.
This means you don't get to control the isolation level of the transaction, because [1]:
> sqlite3 uses BEGIN DEFERRED statements when opening transactions.
If you want to use `IMMEDIATE` or `EXCLUSIVE` isolation level[2] for your sqlite transaction using the new flag, you have to set `autocommit=True` to be able to open the transaction yourself with `.execute("BEGIN IMMEDIATE")`.
However, with `autocommit=True`, the connection's `.commit()` and `.rollback()` methods will *silently do nothing* and you have to execute the respective raw SQL yourself to commit or abort your manually-opened transaction. This also concerns the context-manager behavior of the connection object, which will not commit or abort manual transactions on context exit in this case.
So, the autocommit flag becomes a little complicated and foot-gunny if you want more precise control over when exactly other readers or writers should get blocked by sqlite.
[0] https://docs.python.org/3/library/sqlite3.html#sqlite3.Conne...
[1] https://docs.python.org/3/library/sqlite3.html#sqlite3-trans...
[2] https://sqlite.org/lang_transaction.html#deferred_immediate_...