Handle SQLite busy errors without claiming infinite retries are safe

By Batu · English technical notes

Also published in our Blogger archive.

Surface a database lock without infinite retries

The first connection takes an exclusive transaction. The second uses timeout=0, so its INSERT immediately raises OperationalError when locked. finally rolls back and closes both connections, even if an assertion changes later. The printed busy kind records that no writer turn was acquired.

Zero timeout makes this fixture deterministic, not universally desirable. A retry policy needs a finite deadline, delay rule, and shutdown path; an endless loop can hide a stuck writer.

Example

import sqlite3
from pathlib import Path
from tempfile import TemporaryDirectory

with TemporaryDirectory() as directory:
    database = Path(directory) / "queue.db"
    first = sqlite3.connect(database, timeout=0)
    second = sqlite3.connect(database, timeout=0)
    first.execute("CREATE TABLE job(id INTEGER)")
    first.commit()
    first.execute("BEGIN EXCLUSIVE")
    try:
        second.execute("INSERT INTO job VALUES (1)")
    except sqlite3.OperationalError as error:
        assert "locked" in str(error).lower()
        print("kind=busy action=surface-error")
    else:
        raise AssertionError("second writer unexpectedly acquired lock")
    finally:
        first.rollback()
        first.close()
        second.close()

Expected stdout:

kind=busy action=surface-error

Sources

- sqlite3.OperationalError

- SQLite Transaction Control

Prepared with AI assistance. The example uses synthetic data; its stated limits apply.