Write-Ahead Logging: How WAL Changes Read/Write Concurrency

Learning outcomes

Rollback mode protects the old database image in a journal while the database file is updated. Write-Ahead Logging (WAL) reverses that direction: the original database remains in place while changed page versions are appended to a separate WAL file. That inversion lets a reader keep an older snapshot while a writer commits newer versions. It improves read/write overlap, but it does not create multiple writers.

Rollback journal versus WAL: reverse where the new pages go

A WAL commit is an append plus a commit record

A WAL transaction writes changed page images as frames to the WAL. Commit is represented by a commit record in that WAL. The writer does not need to overwrite the corresponding main-database pages at commit time. Multiple committed transactions can accumulate in the same WAL until a checkpoint copies eligible frames back.

main database:  [page1 v1] [page2 v1] [page3 v1] ...
                         ^ stable base image

writer appends WAL:
                  [page2 v2][page7 v2][COMMIT A]
                                      [page2 v3][COMMIT B]

reader R started after COMMIT A:
  end mark --------------------^   (R ignores later COMMIT B)

checkpoint later copies safe committed page versions
from WAL back into the main database.

Readers get an end mark: one stable snapshot per read transaction

When a WAL reader begins, it records the location of the last valid commit in the WAL—its end mark. For a requested page, the reader uses the newest WAL version at or before that end mark, otherwise the main-database page. The end mark stays fixed for that read transaction, which is why a long reader can continue seeing an older value even after another connection commits a new one.

Scanning a large WAL for every page would be expensive, so SQLite maintains a wal-index in shared memory. The ordinary implementation uses a memory-mapped -shm file beside the database. That shared-memory design is the reason all WAL participants normally must be on the same host.

Enable WAL—and verify the return value

PRAGMA journal_mode=WAL; is a request that returns the resulting mode. Do not issue it and assume success. The VFS/environment must support WAL’s required shared-memory primitives.

PRAGMA journal_mode=WAL;-- expected on success: walPRAGMA journal_mode;-- expected: walPRAGMA wal_autocheckpoint;-- normally 1000, unless the build/default was changed

Unlike rollback modes such as TRUNCATE, WAL mode is persistent for the database. Close and reopen the file and PRAGMA journal_mode; should still report wal until the database is explicitly moved back to a rollback journal mode.

The -wal and -shm files are not junk

While WAL-mode connections are active, you can commonly observe fieldnotes.db-wal and fieldnotes.db-shm. The WAL file can contain transactions already committed from the application’s perspective but not yet copied into the main database file. SQLite’s documentation therefore treats the WAL as part of persistent database state: separating a live database file from its WAL can lose committed transactions or damage consistency.

Timeline comparison: the same long reader

ROLLBACK MODE
Reader A: BEGIN -- reads v1 ------------------------- COMMIT
Writer B:       BEGIN -- update v2 -- COMMIT?
                                      ^ may wait/fail on reader lock

WAL MODE
Reader A: BEGIN -- reads v1 ------------------------- COMMIT
Writer B:       BEGIN -- update v2 -- COMMIT succeeds
New reader C:                         BEGIN -> sees v2
Reader A:                              still sees v1

WAL improves reader/writer overlap. It does not add writer B2 simultaneously.

Concurrent snapshot lab

This lab proves the model using two connections. Disable autocheckpoint only for the short disposable experiment so the WAL remains visible. Re-enable normal policy or delete the disposable database afterward.

import sqlite3from pathlib import Pathp = Path("wal-snapshot-lab.db")for x in (p, Path(str(p)+"-wal"), Path(str(p)+"-shm")):    x.unlink(missing_ok=True)setup = sqlite3.connect(p, isolation_level=None)print(setup.execute("PRAGMA journal_mode=WAL").fetchone()[0])  # walsetup.execute("PRAGMA wal_autocheckpoint=0")setup.execute("CREATE TABLE reading(id INTEGER PRIMARY KEY, value TEXT NOT NULL)")setup.execute("INSERT INTO reading VALUES(1,'v1')")setup.execute("PRAGMA wal_checkpoint(TRUNCATE)")setup.close()reader = sqlite3.connect(p, isolation_level=None)writer = sqlite3.connect(p, isolation_level=None)reader.execute("BEGIN")print(reader.execute("SELECT value FROM reading WHERE id=1").fetchone()[0])  # v1writer.execute("BEGIN IMMEDIATE")writer.execute("UPDATE reading SET value='v2' WHERE id=1")writer.execute("COMMIT")  # succeeds while reader remains openprint(reader.execute("SELECT value FROM reading WHERE id=1").fetchone()[0])  # still v1fresh = sqlite3.connect(p)print(fresh.execute("SELECT value FROM reading WHERE id=1").fetchone()[0])   # v2fresh.close()reader.execute("COMMIT")reader.close(); writer.close()

The important state is not just “writer succeeded.” Reader A keeps its original snapshot, while a connection that begins afterward sees v2.

WAL still has one writer

All writers append to the one WAL stream, so SQLite still serializes write transactions. If writer A holds BEGIN IMMEDIATE, writer B can receive SQLITE_BUSY exactly as in Lesson 2. WAL is powerful for workloads with many readers and short writes; it is not a substitute for a client/server engine when many long writers must progress in parallel.

Same host is a correctness requirement

The default wal-index uses shared memory coordinated among processes on one machine. Official SQLite documentation states that WAL does not work over a network filesystem when participants are on different hosts. A network share that “looks local” in an application path does not remove this requirement.

Persistence has cross-connection consequences

Once one connection successfully changes the file to WAL mode, other connections opening that same database operate with WAL mode too. That makes journal mode part of deployment state, not a private toggle for one request. Plan migrations, old runtimes, backups, and observability accordingly.

Lab verification

Production judgment and bridge

Choose WAL because its snapshot/checkpoint model matches your workload, not because a blog says it fixes “database locked.” WAL shifts the dominant coordination problem from reader-versus-writer commit to writer serialization and checkpoint management. Lesson 4 makes checkpoint progress and WAL growth observable.

Authoritative references

• SQLite — Write-Ahead Logging.
• SQLite — PRAGMA journal_mode.
• SQLite — Result and Error Codes.
• SQLite — URI filenames / VFS overview.