SQLITE_BUSY_SNAPSHOT: stale read can't become a write
In WAL mode, a transaction that started by reading tried to write after another connection had already changed the database. Waiting won't help; restart the transaction.
- Result code
SQLITE_BUSY_SNAPSHOT- Name
517- Category
- Extended result codes
What it means
SQLITE_BUSY_SNAPSHOT (517) happens only on WAL-mode databases. A read transaction sees a snapshot of the database. If it then tries to write, SQLite must upgrade it to a write transaction, but if another connection committed changes after the snapshot was taken, the earlier reads are out of date and the upgrade is refused.
Unlike plain SQLITE_BUSY, a busy timeout doesn't fix this: the transaction must be rolled back and run again from the start.
Common causes
BEGIN(deferred) followed bySELECTand thenUPDATE, while another process commits in between.- Long read transactions in an application that later decides to write.
How to fix it
-
Start transactions that will write with
BEGIN IMMEDIATE, so the write lock is taken before any reads.BEGIN IMMEDIATE; SELECT balance FROM accounts WHERE id = 1; UPDATE accounts SET balance = balance - 10 WHERE id = 1; COMMIT;
-
When it does happen, roll back and retry the whole transaction.
ROLLBACK;
Related errors
SQLITE_BUSYdatabase is lockedSQLITE_BUSY_RECOVERYanother process is recovering the WAL40001PostgreSQL: could not serialize access3960SQL Server: Snapshot isolation update conflictORA-08177Oracle: can't serialize access for this transaction
More extended result codes errors
SQLITE_ERROR_MISSING_COLLSEQno such collation sequenceSQLITE_LOCKED_SHAREDCACHErecord locked in shared cacheSQLITE_CONSTRAINT_CHECKCHECK constraint failedSQLITE_ABORT_ROLLBACKstatement aborted by ROLLBACKSQLITE_IOERR_SHORT_READread returned fewer bytesSQLITE_CANTOPEN_ISDIRthe path is a directorySQLITE_BUSY_TIMEOUTblocking file lock timed outSQLITE_READONLY_ROLLBACKhot journal can't be rolled back
The code and message text are from the SQLite result and error codes documentation. Browse the full SQLite error list or search every database error.
Work on SQLite in SQLly
A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for SQLite and the other databases you use.