Fix SQLITE_BUSY_SNAPSHOT in SQLite WAL Mode

If a read-then-update operation returns SQLITE_BUSY_SNAPSHOT in SQLite WAL mode, roll back and repeat the read in a fresh transaction. For a short transaction that is expected to write, you can use BEGIN IMMEDIATE before the first query. The Python example uses two local connections to reproduce a stale read snapshot and ordinary writer contention.

Layered cyan data plates with an amber write marker beside the newer version

Why a successful read can be followed by a failed write

In WAL mode, a read transaction sees a consistent snapshot from when it began reading. If connection A reads 0 and connection B updates the value to 10 and commits, A continues to see 0 within the same transaction. When A tries to write, SQLite rejects the attempt to branch from a stale snapshot and returns SQLITE_BUSY_SNAPSHOT. A keeps that snapshot even after B has finished writing, so a longer timeout cannot resolve this error. SQLite isolation · Extended result code

Reproduce the failure and recovery with two connections

Save the code as wal_snapshot.py and run python3 wal_snapshot.py. It uses Python 3.12’s autocommit=True and controls transactions spanning multiple statements through SQL BEGIN, ROLLBACK, and COMMIT. B’s standalone update commits when the statement finishes. Both connections use the same temporary file. Python sqlite3 transaction control

Distinguish the two errors in the output

The example ran on Linux with Python 3.12.14 and SQLite 3.53.1 on November 12, 2026. It checked that B saw 10 while A still saw 0, and that A’s attempt to upgrade to a writer returned the expected extended result code. Next, A started a write transaction first, and B’s BEGIN IMMEDIATE failed with SQLITE_BUSY. A read the current value of 10, added 1, and committed. B then read the new value of 11.

For SQLITE_BUSY_SNAPSHOT, roll back and rerun the whole operation. Retrying only the failed UPDATE, or starting a fresh transaction while still using the old read value, can produce incorrect data. Ordinary SQLITE_BUSY in this example means another write transaction is in progress. You can handle contention when starting the write transaction, with a bounded wait. SQLite transactions

Account for the cost of starting as a writer

BEGIN IMMEDIATE starts the write transaction before the first query. Other connections can still read in WAL mode, while other writers may wait or fail, so keep slow network calls outside the transaction. If the operation fits in one SQL statement, such as UPDATE counter SET value = value + 1, you can eliminate the separate read and calculation. For effects that a rollback cannot undo, such as external API calls, manage duplicate execution separately from transaction retries. The example verifies the sequence of operations across two connections to a local file. It does not measure workload throughput or retry latency. SQLite isolation · Transaction modes

한국어 버전 · SQLite WAL에서 SQLITE_BUSY_SNAPSHOT 해결하기