Set a busy timeout only as a bounded local policy

By Batu · English technical notes

Also published in our primary archive.

Set a busy timeout only as a bounded local policy

Set a SQLite busy timeout as a bounded local policy by passing a small timeout to sqlite3.connect() and handling the resulting OperationalError. Do not turn a lock failure into an unlimited retry loop: that can conceal contention and leave a caller waiting without a defined local limit.

This temporary fixture uses two database connections because two independent :memory: connections do not share a database. The first connection creates and seeds items, then holds a write transaction with BEGIN IMMEDIATE. The naive policy shown in the output is deliberately unbounded: “retry forever” provides no answer about when the operation should fail.

The contender instead uses timeout=0.05. Its attempted insert raises OperationalError while the writer owns the lock. The elapsed-time assertion checks that SQLite waited for roughly the requested window before reporting contention; the program prints no timing measurement, keeping stdout deterministic. The upper bound is intentionally broad because scheduler delay is outside the example’s control.

Python documents timeout as the seconds to wait before a locked-table OperationalError; its default is five seconds. A timeout is not fairness, deadlock detection, or a distributed retry strategy. Choose the value around a specific operation’s latency budget, release the other transaction promptly, and decide at the call site whether a failed operation should be surfaced or retried under a separate bounded policy. This example requires Python 3.

AI-assistance disclosure: Batu Lab Notes used AI assistance to draft and check this synthetic example.

Sources: Python sqlite3.connect and SQLite transaction documentation.

import sqlite3
from pathlib import Path
from tempfile import TemporaryDirectory
from time import monotonic


with TemporaryDirectory() as directory:
    database = Path(directory) / "fixture.sqlite"
    writer = sqlite3.connect(database)
    writer.execute("CREATE TABLE items (id INTEGER PRIMARY KEY, label TEXT)")
    writer.execute("INSERT INTO items(label) VALUES ('A')")
    writer.commit()
    schema = writer.execute(
        "SELECT sql FROM sqlite_master WHERE name = 'items'"
    ).fetchone()[0]
    seed_rows = writer.execute("SELECT label FROM items").fetchall()

    writer.execute("BEGIN IMMEDIATE")
    contender = sqlite3.connect(database, timeout=0.05)
    started = monotonic()
    try:
        contender.execute("INSERT INTO items(label) VALUES ('B')")
    except sqlite3.OperationalError:
        elapsed = monotonic() - started
        assert elapsed >= 0.04
        print(f"schema: {schema}")
        print(f"seed rows: {seed_rows}")
        print("naive policy: retry forever")
        print("bounded policy seconds: 0.05")
        print("corrected result: OperationalError")
    else:
        raise AssertionError("the held write lock should block the contender")
    finally:
        writer.rollback()
        contender.close()
        writer.close()

schema: CREATE TABLE items (id INTEGER PRIMARY KEY, label TEXT)
seed rows: [('A',)]
naive policy: retry forever
bounded policy seconds: 0.05
corrected result: OperationalError