Databases and servers
SQLite WAL Mode: What It Fixes, What It Costs and Where It Breaks
In short
Turning on SQLite WAL mode stops readers and a writer from waiting on each other, and the choice is written into the database file, so it outlives the connection that made it. It still allows only one writer, it does not work across machines on a network share, and a database that always has a reader open can grow its -wal file without limit.
What does WAL change about readers and writers?
In the default rollback-journal mode, a write has to keep readers out while it changes the database file. With a write-ahead log, the original pages stay in the main file and new content is appended to a separate -wal file. A transaction commits when its commit record lands in that log.
Because a writer only appends, readers can keep reading while it works, and a reader never stops a writer. The SQLite page itself calls this "mostly true", and the exceptions are listed further down. There is one log file per database, so there is still exactly one writer at a time.
Readers get snapshot isolation. A read transaction keeps seeing the database as it was when that transaction began, even after another connection commits. To see the newer data, it has to end and start a fresh read transaction.
How do you switch SQLite WAL mode on, and does it stay on?
One pragma does it, and its return value is the thing to check:
- The switch fails when the VFS lacks the shared-memory primitives WAL needs. The mode stays as it was, with no error raised, so code should compare the returned string.
- Unlike other journal modes, WAL is persistent: close and reopen the file and it is still in WAL mode, and every connection to that file uses it.
- The mode cannot change while a transaction is active.
- Only SQLite 3.7.0 or newer can open a WAL database. To go back, run
PRAGMA journal_mode=DELETE, which also restores the older file-format version bytes.
PRAGMA journal_mode=WAL;
-- returns 'wal' on success, or the old mode (for example 'delete') if the switch failedWhy can the -wal file keep growing?
By default SQLite checkpoints automatically once the log reaches 1,000 pages, which the docs put at about 4 MB, and then starts writing the log from the beginning again. Every automatic checkpoint is PASSIVE: it copies what it can and gives up rather than wait for other connections.
A checkpoint can only rewind the log when no connection is still reading from it. If overlapping readers mean some read transaction is always open, no checkpoint completes and the file grows without bound. One very large write transaction has the same effect while it runs, and setting wal_autocheckpoint to zero removes the brake entirely.
Size matters for speed too. Each reader has to search the log for pages, and that work grows with the log, so read performance falls as the file gets bigger.
- Leave gaps with no open readers, and make sure checkpoints run during them.
- Run
PRAGMA wal_checkpoint(TRUNCATE)at a quiet moment. It waits for writers and readers to finish with the log, then cuts the file to zero bytes; the first result column is 1 if it was blocked. - A normal checkpoint does not shrink the file. Set
journal_size_limitif you want leftover log files cut back after each reset; its default of -1 means no limit.
Where does WAL mode stop working?
The limits below come straight from the documentation, and most of them surface only after deployment:
- Network filesystems: every process must be on the same host, because WAL depends on a small block of shared memory that separate machines cannot share. The SQLite network page separately warns that broken locking on some network filesystems has corrupted databases.
- ATTACHed databases: a transaction that changes several of them is atomic per file, not across the set.
page_sizeis fixed once in WAL mode, including throughVACUUMor a backup restore. Switch to a rollback mode first if you need to change it.- Read-only locations: the opener needs write access to the
-shmfile or its directory. Since 3.22.0 a read-only database can be opened if the-shmand-walfiles already exist, can be created, or the file is marked immutable. The docs still advise converting to DELETE mode before shipping a database on read-only media. - Copying files: committed transactions may live only in the
-walfile. Separating the database from its log can lose them or corrupt the file, and the only safe way to fold the log in is to open and cleanly close the database. SQLITE_BUSYstill appears when another connection uses exclusive locking mode, while the last connection is cleaning up on close, and while a new connection runs recovery after a crash.
What stays the same after the switch?
Separate connections still get serializable behaviour because writes are serialized, and there is still no isolation between statements on the same connection. Busy handling is still needed. A BEGIN that starts by reading and later tries to write fails with SQLITE_BUSY if another connection wrote in between; the isolation page calls this SQLITE_BUSY_SNAPSHOT. Starting with BEGIN IMMEDIATE avoids it, and in WAL mode IMMEDIATE and EXCLUSIVE behave the same.
Durability depends on synchronous. With FULL, the log is synced on every commit. With NORMAL, WAL stays consistent and safe from corruption, but a committed transaction can roll back after a power loss or OS crash. An application crash alone never loses a commit.
Huge transactions are less of a reason to avoid WAL than they were: the docs say that since 3.11.0 WAL handles them as efficiently as rollback mode. The same page still says WAL works best with smaller transactions.
Which SQLite versions carry the WAL-reset bug?
The WAL page documents a rare data race it calls the WAL-reset bug. It needs two or more connections, in different threads or processes, writing or checkpointing the same WAL database at the same instant.
It is likely present from 3.7.0 through 3.51.2 and fixed in 3.51.3, released 2026-03-13, with backports in 3.44.6 and 3.50.7. The page calls it unlikely in common use, but a multi-process server writing to one file is the case it describes, so I would check the bundled SQLite version of the language runtime before relying on WAL there.
Checked against: source 1
What else do people ask?
Does WAL mode let two processes write at once?
No. Writers are still serialized; WAL only stops reads and the single write from blocking each other.
Is it safe to delete the -wal file?
Not by hand. Committed data can sit in it. Open the database with SQLite and close it cleanly, and the last connection checkpoints and removes the file itself.
Sources
- SQLite, write-ahead logging, fetched 2026-11-20
- SQLite, pragma statements (journal_mode, wal_autocheckpoint, wal_checkpoint, journal_size_limit, synchronous), fetched 2026-11-20
- SQLite, BEGIN TRANSACTION, fetched 2026-11-20
- SQLite, isolation in SQLite, fetched 2026-11-20
- SQLite over a network, caveats and considerations, fetched 2026-11-20
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Written only from five sqlite.org pages read on 20 Nov 2026; no SQLite database was exercised to produce it. Not covered are BEGIN CONCURRENT, the wal2 branch, language-binding defaults and performance numbers. Found a mistake? Tell me.