Python SQLite busy writer: handle contention as a bounded outcome

Operation contract

Two connections target one database file. The first begins an immediate transaction and inserts a receipt. The second has a zero busy timeout, so its competing BEGIN fails immediately. After the first commits, the second can start a new transaction. Writer contention is a process-level concern, not merely a thread scheduling detail.

Failure and ownership boundary

A lock error does not say that the first command failed. If the second operation retries, it needs a stable business key and a bounded attempt policy. Do not hold a write transaction over a network call while other writers wait. The fixture uses explicit BEGIN with isolation_level=None to expose the lock boundary; transaction ownership should remain in one application layer.

Working program

import sqlite3
import tempfile
from pathlib import Path

with tempfile.TemporaryDirectory() as directory:
    database = Path(directory) / "receipts.db"
    owner = sqlite3.connect(database, timeout=0, isolation_level=None)
    contender = sqlite3.connect(database, timeout=0, isolation_level=None)
    owner.execute("CREATE TABLE receipts (receipt_id TEXT PRIMARY KEY)")
    owner.execute("BEGIN IMMEDIATE")
    owner.execute("INSERT INTO receipts VALUES (?)", ("R-47",))
    try:
        contender.execute("BEGIN IMMEDIATE")
    except sqlite3.OperationalError:
        print("second writer blocked")
    owner.execute("COMMIT")
    contender.execute("BEGIN IMMEDIATE")
    contender.execute("INSERT INTO receipts VALUES (?)", ("R-48",))
    contender.execute("COMMIT")
    print("rows:", owner.execute("SELECT count(*) FROM receipts").fetchone()[0])
    owner.close()
    contender.close()

Output

second writer blocked
rows: 2

Costs and limits

A longer busy timeout waits rather than failing immediately, but holds a request worker. Keep transactions short and size retry time to the service deadline.

Common Mistakes

• Do not classify every OperationalError as a lock error in production.
• Do not retry a non-idempotent command under a new identity.
• Do not hold an immediate transaction during HTTP calls.

Connected lessons

Python SQLite writer contention: reject a busy transaction before changing state, Python sqlite3 transactions: bind values and roll back failed batches, Python SQLite upsert: make a replay update explicit.