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

Category: Locking & ConcurrencyVersions: SQLite 3.7.0+

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

1A long-running reader connection in WAL mode is holding an open read transaction against an old WAL snapshot
2The read connection was opened before the most recent write committed and is still active
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

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

sqlite3.OperationalError: database is locked

Fix 1: Close or commit idle read transactions promptly

When readers are held open longer than necessary.

fix

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

Why it's wrong: 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 docs: https://www.sqlite.org/rescode.html#busy_snapshot

🔧 Source ref: sqlite3.h — SQLITE_BUSY_SNAPSHOT = 517

📖 Further reading: SQLite WAL mode

Confidence assessment

✅ HIGH confidence

Well-documented. WAL snapshot mechanics are described in detail in the WAL mode documentation.