Diagnose busy, locked, and stale-snapshot failures

Understand the likely causes

• Another connection may own the writer role, an open statement may retain resources, or a read transaction may be too old to upgrade safely after another commit.
• A driver policy, shared-cache interaction, filesystem/VFS condition, or unfinished transaction can produce a different conflict. WAL preserves one active writer and does not remove every busy outcome.

Record connection and transaction context

Capture runtime/build identity, journal mode, host transaction settings, extended error name, operation key, and which connection owns each transaction. Restrict real filenames and SQL values to approved diagnostics. Do not delete sidecars, restart unrelated services, or open a configured database as part of this guide. Determine whether the failed request is a new write, a read-to-write upgrade, or COMMIT before choosing a response.

Separate waiting from a stale snapshot

A busy timeout can wait for some transient lock contention. It cannot make an obsolete WAL snapshot a valid basis for a write. After SQLITE_BUSY_SNAPSHOT, the owning boundary normally must abandon the snapshot and restart the complete eligible transaction with fresh reads. A busy COMMIT can leave work active with different retry possibilities. SQLITE_LOCKED also deserves its own documented connection/statement investigation.

Use a private bounded schedule

The complete program creates its own temporary file, holds one write transaction, and gives the second connection zero wait time. The attempted insert is rejected with SQLITE_BUSY, then succeeds after the first rolls back. This is sequential coordination, not load testing or a WAL-reset race. Both connections close before the private directory is removed. The existing row plus the later insert give two rows.

import sqlite3
from pathlib import Path
from tempfile import TemporaryDirectory
from contextlib import closing

with TemporaryDirectory(prefix="sqlite-guide-busy-") as temporary:
    path = Path(temporary) / "training.db"
    with closing(sqlite3.connect(path, isolation_level=None, timeout=0)) as writer, \
         closing(sqlite3.connect(path, isolation_level=None, timeout=0)) as contender:
        writer.executescript("CREATE TABLE item(id INTEGER PRIMARY KEY); INSERT INTO item VALUES(1);")
        writer.execute("BEGIN IMMEDIATE")
        try:
            contender.execute("INSERT INTO item VALUES(2)")
        except sqlite3.OperationalError as error:
            assert error.sqlite_errorcode == sqlite3.SQLITE_BUSY
            print("blocked", error.sqlite_errorname)
        else:
            raise AssertionError("expected contention")
        finally:
            writer.execute("ROLLBACK")
        contender.execute("INSERT INTO item VALUES(2)")
        print("rows", contender.execute("SELECT COUNT(*) FROM item").fetchone()[0])

blocked SQLITE_BUSY
rows 2

Repair ownership and choose bounded retries

Shorten owned transactions, finalize readers promptly, and define write admission from the application's needs. BEGIN IMMEDIATE can avoid some later upgrade surprises by requesting the writer role earlier, but it is not a universal cure. Use bounded retries with request identity and classify uncertain outcomes. Check official WAL patch notes for the deployed build; the3.45.1 teaching runtime is not a production patch recommendation.

Keep it from recurring

• Document connection ownership, transaction duration budgets, driver defaults, cancellation, journal mode, backup behavior, and supported patched runtimes. Preserve meaningful extended error information without exposing private data, and keep sidecar handling within an approved lifecycle/recovery procedure.