Transactions in Python — The Implicit Model

The most surprising part of Python's sqlite3

Python's sqlite3 module has its own implicit transaction behavior, sitting on top of SQLite's. The defaults often surprise people:

By default the module opens a transaction automatically before the first DML statement (INSERT/UPDATE/DELETE).
It commits automatically before any non-DML statement (CREATE TABLE, etc.) — this is the famous 'PRAGMA inside a transaction' bug source.
The module's isolation_level attribute controls this. Setting conn.isolation_level = None turns off the implicit transactions — you manage everything explicitly with BEGIN / COMMIT.

The Pythonic pattern most people end up using:

with conn: — context manager: implicit BEGIN, COMMIT on success, ROLLBACK on exception.
Or set isolation_level = None and explicitly conn.execute('BEGIN IMMEDIATE') when you want explicit control.

Warning: Python 3.12 added a new autocommit attribute that's clearer than the legacy isolation_level. New code should prefer autocommit=False with explicit transactions, or autocommit-style with autocommit=True. Old code uses isolation_level; both APIs coexist.

Code

with conn — the everyday pattern·python

import sqlite3

conn = sqlite3.connect('demo.db')

with conn:
    conn.execute('INSERT INTO users(email) VALUES (?)', ('a@x.com',))
    conn.execute('INSERT INTO users(email) VALUES (?)', ('b@x.com',))
    # An exception here triggers ROLLBACK; clean exit triggers COMMIT.

Explicit transaction control·python

import sqlite3

conn = sqlite3.connect('demo.db', isolation_level=None)  # autocommit-ish

conn.execute('BEGIN IMMEDIATE')
try:
    conn.execute('INSERT INTO orders(...) VALUES (...)')
    conn.execute('UPDATE inventory SET qty = qty - 1 WHERE id = ?', (sku,))
    conn.execute('COMMIT')
except Exception:
    conn.execute('ROLLBACK')
    raise

Exercise

Demonstrate three transaction behaviors in Python: (1) implicit transaction with with conn:, (2) explicit transaction with isolation_level = None, (3) the bug where running PRAGMA inside an open transaction commits the implicit transaction. For each, write a short test that reveals the behavior and decide which pattern you'd use in your code.

Transactions in Python — The Implicit Model

The most surprising part of Python's sqlite3

Code

External links

Exercise

Progress

Comments 0