SQLite Basics: Observe a Stable Reader Snapshot in WAL Mode

A report should not silently change halfway through a transaction. In write-ahead logging (WAL) mode, a reader can keep its snapshot while another connection commits a change. This example orders the operations explicitly, so no threads or timing guesses are needed.

Prepare a scratch directory

Tested on Linux with CPython 3.12.14 and SQLite 3.53.1. You need Python 3.11 or newer with SQLite support, Bash, a text editor, and standard mktemp, rm, and rmdir utilities. No third-party packages are required. Run Python without -O so the checks remain enabled.

Keep the same terminal open. Stop if setup fails, before saving the source:

start_dir="$PWD"
work_dir="$(mktemp -d)" || exit 1
cd -- "$work_dir" || exit 1

Run the example

Save this as observe-wal-snapshot.py:

from pathlib import Path
import sqlite3
from tempfile import TemporaryDirectory

with TemporaryDirectory() as directory:
    database = Path(directory) / "snapshot.db"
    writer = sqlite3.connect(database, isolation_level=None)
    reader = None
    try:
        mode = writer.execute("PRAGMA journal_mode=WAL").fetchone()[0]
        assert mode == "wal"
        writer.execute("CREATE TABLE stock (id INTEGER PRIMARY KEY, quantity INTEGER)")
        writer.execute("INSERT INTO stock VALUES (1, 10)")
        reader = sqlite3.connect(database, isolation_level=None)
        reader.execute("BEGIN")
        first = reader.execute("SELECT quantity FROM stock WHERE id=1").fetchone()[0]
        print("Reader begins:", first)
        writer.execute("BEGIN IMMEDIATE")
        writer.execute("UPDATE stock SET quantity=15 WHERE id=1")
        writer.execute("COMMIT")
        print("Writer committed:", 15)
        same = reader.execute("SELECT quantity FROM stock WHERE id=1").fetchone()[0]
        assert first == same == 10
        print("Reader in same transaction:", same)
        reader.execute("COMMIT")
        fresh = reader.execute("SELECT quantity FROM stock WHERE id=1").fetchone()[0]
        assert fresh == 15
        print("Reader after transaction ends:", fresh)
    finally:
        if reader is not None:
            reader.close()
        writer.close()

Run it:

python3 -B observe-wal-snapshot.py

Observed output:

Reader begins: 10
Writer committed: 15
Reader in same transaction: 10
Reader after transaction ends: 15

Apply the result

The first read establishes the reader’s snapshot. The writer commits a new quantity, but a second read in the same reader transaction still returns 10. Ending that transaction lets the next query see 15. isolation_level=None leaves transaction boundaries to the explicit SQL statements used here.

Use short read transactions when reports need fresh data. A long-lived reader can prevent checkpoint progress past its snapshot and allow the WAL file to grow. WAL allows reader/writer overlap; it does not allow multiple simultaneous writers. Busy errors are still possible, and a reader that tries to upgrade an old snapshot to a write may need to restart its transaction.

The temporary database and both connections are local to this process. Keep WAL databases on a suitable local filesystem; WAL is not a way to share a database over a network filesystem. The temporary directory is removed only after both connections close. Do not manually delete WAL or shared-memory sidecars from an open production database.

See the official reference for the API and its constraints. The output above comes from this controlled fixture.

Clean up

Remove the saved source, return to the starting directory, then remove the empty practice directory:

rm -- observe-wal-snapshot.py
cd -- "$start_dir" || exit 1
rmdir -- "$work_dir"
unset start_dir work_dir

If the directory is not empty, inspect its contents before removing anything else.

Acknowledgements

This post was created with assistance from AI.