SQLite, explained visually

WAL mode keeps reads moving while writes land safely.

Instead of changing the main database file during every transaction, SQLite appends committed changes to a write-ahead log. Readers assemble a stable snapshot from both places.

Many readers · one writer · same machine

The inversion

Traditional rollback journaling protects the old state before changing the database. WAL preserves the main file and records the new state elsewhere first.

Rollback journal

Save old pages, then overwrite

Old page→Rollback journal
New page→Main DB

The journal exists so SQLite can restore the old contents if the transaction fails.

Write-ahead log

Keep the main file, append changes

New page→WAL
WAL→ later →Main DB

A commit is recorded in the WAL. Copying those pages into the main file is a later checkpoint.

The three movements

Use the controls to isolate each operation. Only the arrows that matter to the selected phase are shown.

SQLite WAL read, write, and checkpoint flow Readers combine database pages with committed WAL pages, a writer appends to the WAL, and checkpointing copies committed pages back to the main database. Reader Astable snapshot Writerone at a time Reader Bmay see a newer snapshot gateway.dbmain database pagesABC gateway.db-walnew committed pagesB′D -shmpage lookupindex MAIN FILEWAL TAIL READS CONTINUEAPPEND COPY COMMITTED PAGES

Three ordinary cases

WAL is not a queue or a second database. It is SQLite’s transactional path between a write and the main database file.

1

Email arrives during an agent query

The sync worker inserts an email while the agent is paging through existing records.

10:00:00Agent begins a stable read
10:00:01Sync appends email pages to WAL
10:00:02Agent finishes its original snapshot
next readNew email becomes visible
2

Several readers, one writer

A dashboard, an agent, and a backup inspection can read concurrently.

Reader AEnd mark at commit 41
WriterAppends commit 42
Reader BEnd mark at commit 42
ResultBoth snapshots stay internally consistent
3

A long read delays cleanup

A reader holds an old end mark while new transactions accumulate.

ReaderKeeps transaction open
WritesContinue appending to WAL
CheckpointStops before reader’s protected pages
Reader endsCheckpoint can finish and recycle WAL

The three files you may see

gateway.db

The durable main database. Checkpointing eventually copies committed WAL pages here.

gateway.db-wal

Committed page changes not yet fully transferred into the main file. It is part of the database’s persistent state.

gateway.db-shm

A shared-memory-backed index that helps readers quickly locate the newest applicable page in the WAL.

Turning it on

WAL mode is persistent for the database file. Check the returned value instead of assuming the request succeeded.

PRAGMA journal_mode=WAL;
-- expected result: wal

PRAGMA foreign_keys=ON;
PRAGMA busy_timeout=5000;

SQLite automatically attempts a checkpoint at roughly 1,000 WAL pages by default. Most small applications should begin with that default and measure before tuning.

What WAL does—and does not—buy you

Readers and a writer overlap

A writer appends to the WAL instead of overwriting pages currently being read.

Each reader gets a stable snapshot

Its end mark stays fixed for the duration of the read transaction.

There is still only one writer

WAL improves read/write concurrency; it does not allow multiple simultaneous SQLite writers.

Keep it on one host

The WAL index relies on shared memory. Do not place a live WAL database on a network filesystem for access from different machines.

Long reads can grow the WAL

Close read transactions promptly so checkpoints can complete and the WAL can be recycled.

Copy all database state safely

Do not copy only the .db file while live WAL state exists. Use SQLite’s backup facilities or a coordinated snapshot.

Version note, August 2026: SQLite’s official documentation describes a rare WAL-reset race fixed in 3.51.3 and later, with fixes also available in identified backport releases. For a new WAL deployment, use a release containing that fix and verify the SQLite version provided by your runtime or operating system.
✓

The mental model

A read chooses a snapshot. A write appends committed changes. A checkpoint copies those changes home. WAL improves concurrency without turning SQLite into a multi-writer or distributed database.