A longer busy timeout cannot make an old WAL read snapshot current. If another connection commits after your transaction has read, an attempt to turn that read transaction into a writer can fail with SQLITE_BUSY_SNAPSHOT. End the failed transaction, read the current state in a new transaction, and recompute the write. For a short operation that you already know will write, consider taking the writer position with BEGIN IMMEDIATE before reading.
This is one specific cause of “database is locked.” Check the extended error code before choosing this remedy. Ordinary writer contention, shared-cache conflicts, permissions, and a database on unsuitable storage need different investigation. Do not delete the database, WAL, or shared-memory files to clear a live lock.
Identify the transaction failure before increasing the timeout
Python's sqlite3 exceptions expose sqlite_errorcode and sqlite_errorname when they originate from SQLite, starting with Python 3.11. Log those attributes with the linked SQLite version, journal mode, connection timeout, and the operation that failed. Avoid logging private SQL parameters. Python's version alone does not tell you which SQLite library is running.
The primary SQLITE_BUSY category has value 5; the extended SQLITE_BUSY_SNAPSHOT result is 517. A text message alone can hide that distinction. Do not turn every OperationalError into a retry: syntax, schema, read-only, disk, and locking failures are not interchangeable.
A reader establishes a snapshot by reading inside its transaction. In WAL mode another connection can commit while that reader remains open. Waiting does not update the reader's snapshot. Repeating just the failing UPDATE inside that same transaction therefore leaves the reason for failure intact.
Reproduce the stale snapshot with two connections
Run this self-contained example on a disposable local directory. It explicitly controls transactions with isolation_level=None; on Python 3.12 and later it also explicitly selects legacy transaction control so the SQL BEGIN, COMMIT, and ROLLBACK statements have the intended role. It creates a private temporary database, closes both connections, and removes its files. It does not touch an application database.
import sqlite3
import sys
import tempfile
from pathlib import Path
options = {"isolation_level": None, "timeout": 0.1}
if sys.version_info >= (3, 12):
options["autocommit"] = sqlite3.LEGACY_TRANSACTION_CONTROL
with tempfile.TemporaryDirectory() as directory:
path = Path(directory) / "snapshot.db"
a = sqlite3.connect(path, **options)
b = sqlite3.connect(path, **options)
try:
assert a.execute("PRAGMA journal_mode=WAL").fetchone()[0] == "wal"
a.execute("CREATE TABLE counter (id INTEGER PRIMARY KEY, value INTEGER)")
a.execute("INSERT INTO counter VALUES (1, 0)")
a.execute("BEGIN")
assert a.execute("SELECT value FROM counter WHERE id=1").fetchone()[0] == 0
b.execute("UPDATE counter SET value=value+1 WHERE id=1")
for attempt in range(2):
try:
a.execute("UPDATE counter SET value=value+1 WHERE id=1")
except sqlite3.OperationalError as error:
print(attempt, error.sqlite_errorcode, error.sqlite_errorname)
assert error.sqlite_errorcode == sqlite3.SQLITE_BUSY_SNAPSHOT
assert a.in_transaction
else:
raise AssertionError("Expected a stale-snapshot failure")
a.execute("ROLLBACK")
a.execute("BEGIN IMMEDIATE")
current = a.execute("SELECT value FROM counter WHERE id=1").fetchone()[0]
assert current == 1
a.execute("UPDATE counter SET value=? WHERE id=1", (current + 1,))
a.execute("COMMIT")
assert b.execute("SELECT value FROM counter WHERE id=1").fetchone()[0] == 2
print("Recovered value: 2; SQLite", sqlite3.sqlite_version)
finally:
a.close()
b.close()Both failed attempts should report 517 and SQLITE_BUSY_SNAPSHOT. Connection B has already finished its update before A tries to write, so this fixture does not rely on a sleeping thread or a timing race. Its purpose is to verify the state transition, not benchmark timeout precision or concurrency throughput.
The recovery reads 1 after the rollback, then writes 2. Reusing the old value 0 after opening a new transaction could overwrite the intervening increment with 1. Restarting a transaction means repeating the reads and business decisions that determined the write, not simply replaying a previously calculated value.
Reserve the writer before a known read-then-write operation
BEGIN IMMEDIATE attempts to start a write transaction before the first read. Another writer can still prevent this statement from succeeding. A configured busy timeout can help with that contention, but expiry remains possible. Acquiring the writer position also reduces write concurrency: keep the transaction short and do not hold it during a network request, a user prompt, or expensive computation.
The next fixture shows contention at the start rather than a stale read-to-write upgrade. It deliberately uses a zero timeout so it completes without a sleep. Its SQLITE_BUSY result does not measure how long a nonzero timeout would wait.
import sqlite3
import sys
import tempfile
from pathlib import Path
options = {"isolation_level": None, "timeout": 0}
if sys.version_info >= (3, 12):
options["autocommit"] = sqlite3.LEGACY_TRANSACTION_CONTROL
with tempfile.TemporaryDirectory() as directory:
path = Path(directory) / "writer.db"
a = sqlite3.connect(path, **options)
b = sqlite3.connect(path, **options)
try:
assert a.execute("PRAGMA journal_mode=WAL").fetchone()[0] == "wal"
a.execute("CREATE TABLE item (value INTEGER)")
a.execute("BEGIN IMMEDIATE")
try:
b.execute("BEGIN IMMEDIATE")
except sqlite3.OperationalError as error:
assert error.sqlite_errorcode == sqlite3.SQLITE_BUSY
assert not b.in_transaction
print(error.sqlite_errorcode, error.sqlite_errorname)
else:
raise AssertionError("Expected the writer reservation to be busy")
a.execute("ROLLBACK")
b.execute("BEGIN IMMEDIATE")
b.execute("INSERT INTO item VALUES (7)")
b.execute("COMMIT")
assert a.execute("SELECT value FROM item").fetchall() == [(7,)]
print("Writer reservation succeeded after release")
finally:
a.close()
b.close()Do not issue another BEGIN on a connection that already has an active transaction. Check how your driver or framework starts transactions before copying this pattern. The examples explicitly choose SQL transaction control; mixing them with a framework's transaction wrapper or Python's autocommit=False requires adapting ownership of the transaction boundary.
Retry the complete database operation within a defined limit
On a confirmed stale-snapshot failure, roll back the active transaction before making another attempt. Reopen the transaction, repeat relevant reads, recalculate the decision, and commit only if its conditions still hold. Keep an application-specific attempt limit and report failure when it is reached. Endless retries hide a persistent concurrency problem.
A database rollback does not undo an email, payment request, file upload, or remote API call. Keep irreversible effects outside a blindly repeated transaction body, or design a separate idempotency and delivery mechanism. These examples only update a local counter and make no claim that arbitrary business operations are safe to replay.
For an unknown SQLITE_BUSY, first inspect which statement failed and whether another writer is active. SQLITE_LOCKED is a distinct result that can involve conflicts within a connection or shared cache. Do not classify it by the English word “locked” alone. If extended codes are unavailable in your driver, preserve the uncertainty rather than assert a stale snapshot.
Check the fix against your real workload
Verify that the failed transaction ends, a fresh read sees the intervening commit, and the final value reflects both operations. Also test the case where the new writer reservation itself fails. Measure real latency and error rate separately from this deterministic correctness fixture. A longer timeout can consume a worker or block a synchronous event loop; it is not free capacity.
A recent public OpenShell report describes intermittent SQLite lock failures with concurrent clients. The reporter labels its read-to-write diagnosis as unconfirmed without a trace. This guide verifies SQLite's documented mechanism in an independent Python fixture; it does not establish the cause of that project's incident or promise that changing its transaction mode fixes it.
Use the linked SQLite runtime's supported, patched release and verify current vendor guidance. WAL mode has version-specific fixes beyond the snapshot behavior explained here. The fixture does not test corruption recovery, network filesystems, multiple processes, Windows, or production load. It also does not show that every application should switch journal mode or start every transaction with BEGIN IMMEDIATE.
If your problem is transaction lifetime rather than snapshot promotion, see why a SQLite connection context does not close the connection. Keep connection cleanup, transaction rollback, and concurrency policy as separate decisions.