3 ms·

They're probably talking about the addition of the autocommit flag, which hides more fine-grained transaction control in favor of more uniform behavior across m

by dxdm 3mo ago

They're probably talking about the addition of the autocommit flag, which hides more fine-grained transaction control in favor of more uniform behavior across multiple databases: https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.autocommit https://docs.python.org/3/library/sqlite3.html#sqlite3.Conne... You can still use previous behavior with "legacy" mode that lets you control when transactions are opened in which isolation level.

jmalicki 3mo ago
> 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.
- dxdm 3mo ago
  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.Connection.autocommit https://docs.python.org/3/library/sqlite3.html#sqlite3.Conne... [1] https://docs.python.org/3/library/sqlite3.html#sqlite3-transaction-control-autocommit https://docs.python.org/3/library/sqlite3.html#sqlite3-trans... [2] https://sqlite.org/lang_transaction.html#deferred_immediate_and_exclusive_transactions https://sqlite.org/lang_transaction.html#deferred_immediate_...
  - jmalicki 3mo ago
    Thank you! The link had some of the details, but not the intelligent thought linking them into a narrative, much appreciated! This is something super important I would have been foot-gunned on at some point.

by dxdm 3mo ago

jmalicki 3mo ago

> 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.

dxdm 3mo ago
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.Connection.autocommit https://docs.python.org/3/library/sqlite3.html#sqlite3.Conne... [1] https://docs.python.org/3/library/sqlite3.html#sqlite3-transaction-control-autocommit https://docs.python.org/3/library/sqlite3.html#sqlite3-trans... [2] https://sqlite.org/lang_transaction.html#deferred_immediate_and_exclusive_transactions https://sqlite.org/lang_transaction.html#deferred_immediate_...
- jmalicki 3mo ago
  Thank you! The link had some of the details, but not the intelligent thought linking them into a narrative, much appreciated! This is something super important I would have been foot-gunned on at some point.