Python database interview: a retained SQLite read snapshot can be older than a committed write

Operation contract

The owned SQLite database uses WAL mode. A reader begins a transaction and reads the initial receipt amount, establishing its snapshot. A writer commits a larger amount. The reader still sees the old amount until it ends that transaction; its next read sees the committed value. The code checks transaction lifetime rather than adding a timing sleep.

Failure and ownership boundary

This is SQLite WAL behavior under the tested local configuration, not a statement that every database isolation level behaves identically. Long readers can also retain old WAL pages and affect checkpoint progress. An application must decide where its unit of work begins and ends. Python SQLite writer contention: reject a busy transaction before changing state and Python sqlite3 transactions: bind values and roll back failed batches provide related traces.

Working program

from pathlib import Path
import sqlite3
import tempfile

with tempfile.TemporaryDirectory() as directory:
    path = Path(directory) / "snapshot.sqlite3"
    writer = sqlite3.connect(path, isolation_level=None)
    reader = sqlite3.connect(path, isolation_level=None)
    try:
        assert writer.execute("PRAGMA journal_mode=WAL").fetchone()[0] == "wal"
        writer.execute("CREATE TABLE receipts (id INTEGER PRIMARY KEY, amount INTEGER NOT NULL)")
        writer.execute("INSERT INTO receipts VALUES (41,125)")
        reader.execute("BEGIN")
        print("first read:", reader.execute("SELECT amount FROM receipts WHERE id=41").fetchone()[0])
        writer.execute("UPDATE receipts SET amount=250 WHERE id=41")
        print("retained snapshot:", reader.execute("SELECT amount FROM receipts WHERE id=41").fetchone()[0])
        reader.commit()
        print("new read:", reader.execute("SELECT amount FROM receipts WHERE id=41").fetchone()[0])
    finally:
        reader.close(); writer.close()

Output

first read: 125
retained snapshot: 125
new read: 250

Costs and limits

The fixture reads one indexed row. Snapshot lifetime affects concurrency and retained journal state independently of query count. It does not profile checkpoints, multiple processes or a deployment filesystem.

Common Mistakes

• A second read inside the same snapshot need not see another connection’s commit.
• Do not generalize one journal/isolation configuration to every database.

Connected lessons

Python SQLite writer contention: reject a busy transaction before changing state, Python sqlite3 transactions: bind values and roll back failed batches, Django atomic transactions: rollback the batch and defer callbacks.