Python SQLite WAL reader: an open transaction keeps its snapshot

Operation contract

A report begins a read transaction and sees 47 units. Another connection commits an update to 52. The report still sees 47 until it ends that transaction; a new read then sees 52. This supports a consistent report, but is not a live view. WAL checkpoints and backups have separate lifecycle rules.

Failure and ownership boundary

A long reader can keep WAL content from being reclaimed. Do not hold a read transaction while formatting or sending a large response. The example uses a temporary file because separate :memory: connections would be separate databases and could not show one shared snapshot.

Working program

import sqlite3
import tempfile
from pathlib import Path

with tempfile.TemporaryDirectory() as directory:
    database = Path(directory) / "inventory.db"
    writer = sqlite3.connect(database, isolation_level=None)
    reader = sqlite3.connect(database, isolation_level=None)
    writer.execute("PRAGMA journal_mode=WAL")
    writer.execute("CREATE TABLE stock (sku TEXT PRIMARY KEY, units INTEGER)")
    writer.execute("INSERT INTO stock VALUES (?, ?)", ("SKU-47", 47))
    reader.execute("BEGIN")
    before = reader.execute("SELECT units FROM stock").fetchone()[0]
    writer.execute("UPDATE stock SET units = ? WHERE sku = ?", (52, "SKU-47"))
    during = reader.execute("SELECT units FROM stock").fetchone()[0]
    reader.execute("COMMIT")
    after = reader.execute("SELECT units FROM stock").fetchone()[0]
    print("snapshot:", before, during)
    print("new read:", after)
    reader.close()
    writer.close()

Output

snapshot: 47 47
new read: 52

Costs and limits

A stable read trades freshness for consistency and can delay checkpoint progress. Measure WAL growth under long reports.

Common Mistakes

• An open read transaction does not see later commits.
• Two :memory: connections do not test one shared database.
• Do not retain a report transaction longer than its consistency need.

Connected lessons

Python SQLite checkpoint project: commit data and cursor together, Python SQLite backup project: copy a committed snapshot to another connection, Python SQLite busy writer: handle contention as a bounded outcome.