EC
SQLITE_BUSY_SNAPSHOT
SQLiteERRORNotableLocking & ConcurrencyHIGH confidence

cannot start a transaction within a transaction (WAL snapshot conflict)

What this means

SQLITE_BUSY_SNAPSHOT (extended code 517) is a specialised form of SQLITE_BUSY that occurs in WAL mode when a write transaction cannot be started because another connection holds a read transaction pointing to an older WAL snapshot. The write cannot proceed because the WAL cannot be checkpointed past the point the reader is reading.

Why it happens
  1. 1A long-running reader connection in WAL mode is holding an open read transaction against an old WAL snapshot
  2. 2The read connection was opened before the most recent write committed and is still active
  3. 3A connection that was used for reading was left with an uncommitted read transaction
How to reproduce

A read connection holds a BEGIN while a writer tries to write in WAL mode.

trigger — this will error
trigger — this will error
import sqlite3

# Setup WAL mode
conn_setup = sqlite3.connect('wal_demo.db')
conn_setup.execute('PRAGMA journal_mode=WAL')
conn_setup.execute('CREATE TABLE t (x INTEGER)')
conn_setup.commit()
conn_setup.close()

# Reader opens a snapshot
reader = sqlite3.connect('wal_demo.db')
reader.execute('BEGIN')  # pins the WAL snapshot
reader.execute('SELECT * FROM t')

# Writer tries to write — may get SQLITE_BUSY_SNAPSHOT
writer = sqlite3.connect('wal_demo.db')
writer.execute('BEGIN IMMEDIATE')  # conflict if checkpoint needed

expected output

sqlite3.OperationalError: database is locked

Fix

Close or commit idle read transactions promptly

WHEN When readers are held open longer than necessary.

Close or commit idle read transactions promptly
import sqlite3
reader = sqlite3.connect('wal_demo.db')
rows = reader.execute('SELECT * FROM t').fetchall()
reader.commit()  # or reader.close() — releases the snapshot

Why this works

Committing or closing the read connection releases the WAL snapshot pin. The WAL can then be checkpointed past the old read point, unblocking the writer.

What not to do

✕ Use long-held BEGIN transactions for reading in WAL mode

Each open BEGIN in WAL mode pins a snapshot, preventing checkpointing and causing the WAL file to grow unboundedly until the reader releases.

Version notes
SQLite 3.7.0+

WAL mode and SQLITE_BUSY_SNAPSHOT introduced together.

Sources
Official documentation ↗

sqlite3.h — SQLITE_BUSY_SNAPSHOT = 517

SQLite WAL mode ↗

Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev

← All SQLite errors