SQLite WAL Mode: What Actually Changes When You Switch from the Rollback Journal

The symptom: readers blocking readers

You have a long-running batch job writing to a SQLite database, and every time it runs, your read-only endpoints start timing out. The database itself is small, the queries are fast, but SQLite's default rollback journal locks the file during writes, so a reader that arrives during a write has to wait. The takeaway: switching to WAL (Write-Ahead Logging) mode with one PRAGMA lets readers proceed concurrently with a single writer, and it often removes the blocking entirely.

SQLite's default journal mode is DELETE, which uses a rollback journal. During a write, the writer must lock out readers to guarantee atomicity. WAL changes this: commits append to a separate -wal file, and readers see a consistent snapshot from the last commit they know about, so a reader can query while a writer holds a transaction open.

What WAL changes

In WAL mode, the writer appends new page versions to the -wal file instead of modifying the main database in place. Readers consult the log to reconstruct the state at their snapshot. A checkpoint later folds pages back into the main database file. Because the log is append-only, readers and the single writer don't block each other.

WAL is persistent per database file: once set, the mode survives across connections and processes, so it only needs to be enabled once rather than on every connection.

Enable it once, verify it stuck

Run this on the database file:

PRAGMA journal_mode=WAL;

Then confirm with:

PRAGMA journal_mode;

It should return wal. Reconnect and check again to prove it persisted. If you see delete, the mode didn't take effect. This is the first place to look when debugging.

A worked example: two connections, one writer, one reader

Here is a Python script that demonstrates the difference. Run it in a directory where you can write files, with no special permissions needed beyond file access:

import sqlite3

# Connection 1: writer
c1 = sqlite3.connect('demo.db')
c1.execute('PRAGMA journal_mode=WAL;')
c1.execute('BEGIN;')
c1.execute('INSERT INTO demo VALUES (1);')
# Connection 2: reader
c2 = sqlite3.connect('demo.db')
c2.execute('SELECT * FROM demo;')
print(c2.fetchall())
# In WAL mode this returns immediately; in DELETE mode it blocks or raises

In WAL mode, the reader sees the database as it was before the writer's transaction began. In DELETE mode, the reader blocks until the writer commits. This is the core concurrency win.

Throughput and durability trade-offs

WAL typically improves write throughput for many small transactions because commits append sequentially to the log instead of fsyncing scattered pages. A practical companion setting is PRAGMA synchronous=NORMAL; in WAL mode, which keeps crash durability (database stays consistent) while relaxing only the guarantee that the very last committed transactions survive a power loss.

However, WAL still allows only one writer at a time; write-heavy multi-connection workloads will still serialize and can hit SQLITE_BUSY unless busy_timeout is configured. Very large transactions can make the -wal file grow until a checkpoint runs.

When to avoid WAL

WAL requires the database to live on a filesystem supporting reliable shared-memory or file locking; it does not work on read-only media and is unreliable on some network filesystems, where rollback journal is safer. If your database must live on a network share or read-only filesystem, stick with the default.

Check the result

After enabling WAL, verify the mode persisted across a fresh connection. Then run a bulk insert and watch the -wal file grow. Finally, run:

PRAGMA wal_checkpoint(TRUNCATE);

to confirm the checkpoint folds the log back and the -wal file shrinks. If you need to copy or back up a live database, use the SQLite backup API or VACUUM INTO rather than copying the file, because the -wal and -shm files must travel with the database file.

Closing thought

WAL is a one-line change with a one-time cost: a separate -wal file and the need to checkpoint. For most applications, the concurrency win outweighs the extra file. Try it on a test database, run the two-connection script, and watch the reader proceed while the writer holds a transaction open. If you see the reader block, check the journal mode first.