Python SQLite writer contention: reject a busy transaction before changing state

Operation contract

Two owned connections open the same temporary database. The first begins an immediate transaction and updates one amount. The second uses a zero busy timeout and fails to acquire its own immediate transaction. After the first commits, the second can acquire the writer slot, update and commit. The final amount proves the rejected attempt did not apply an extra update.

Failure and ownership boundary

A longer busy timeout changes waiting behavior rather than removing contention. Retrying a whole operation must preserve request identity and not repeat unrelated effects. Transaction modes and journal settings also affect when locks are acquired. This fixture uses local SQLite, not a claim about every production database. Python sqlite3 transactions: bind values and roll back failed batches, Python SQLite job project: reject conflicting replays by request identity and Python SQLite outbox project: commit a receipt and its pending event together connect the state contracts.

Working program

from pathlib import Path
import sqlite3
import tempfile

with tempfile.TemporaryDirectory() as directory:
    path = Path(directory) / "owned.sqlite3"
    first = sqlite3.connect(path, timeout=0, isolation_level=None)
    second = sqlite3.connect(path, timeout=0, isolation_level=None)
    try:
        first.execute("CREATE TABLE receipts (id INTEGER PRIMARY KEY, amount INTEGER NOT NULL)")
        first.execute("INSERT INTO receipts VALUES (41,125)")
        first.execute("BEGIN IMMEDIATE")
        first.execute("UPDATE receipts SET amount=amount+25 WHERE id=41")
        try:
            second.execute("BEGIN IMMEDIATE")
        except sqlite3.OperationalError as failure:
            print("busy rejected:", failure.sqlite_errorcode == sqlite3.SQLITE_BUSY)
        first.commit()
        second.execute("BEGIN IMMEDIATE")
        second.execute("UPDATE receipts SET amount=amount+25 WHERE id=41")
        second.commit()
        print("committed amount:", first.execute("SELECT amount FROM receipts WHERE id=41").fetchone()[0])
    finally:
        first.close(); second.close()

Output

busy rejected: True
committed amount: 175

Costs and limits

Lock acquisition depends on database state and timeout policy, not just SQL statement count. This fixture uses one row and no waiting. A deployment needs bounded retries, cancellation and observability rather than an unbounded retry loop.

Common Mistakes

• A busy acquisition is not permission to perform the write outside its transaction.
• Retrying an operation must not duplicate external effects.

Connected lessons

Python sqlite3 transactions: bind values and roll back failed batches, Python SQLite job project: reject conflicting replays by request identity, Python SQLite outbox project: commit a receipt and its pending event together.