What is WAL mode in SQLite, and when should I use it?

What you gain

Readers and the writer stop blocking each other. This is the main reason to use WAL. In rollback-journal mode a writer has to wait until nobody is reading before it can commit, and nobody can read while it commits. A slow report can stall every write, and a write can make page loads fail. In WAL mode a reader sees the database as it was when its query began and is not affected by writes that happen meanwhile.

You can see the difference with two terminals and the sqlite3 shell. First create a small database:

-- sqlite3 demo.db
CREATE TABLE notes (id INTEGER PRIMARY KEY, body TEXT);
INSERT INTO notes (body) VALUES ('first');

Then, in terminal 1, start reading and leave the transaction open:

BEGIN;
SELECT * FROM notes;

In terminal 2, try to write:

INSERT INTO notes (body) VALUES ('second');
-- rollback-journal mode: fails with "database is locked"
-- WAL mode: succeeds

Type COMMIT; in terminal 1 to end its transaction. To repeat the test in the other mode, quit both shells, open one, run PRAGMA journal_mode = WAL;, and start again.

Writes are usually faster. A rollback-journal commit writes each changed page twice, once to the journal and once to the database, at scattered positions in the file. A WAL commit writes each page once, one after another at the end of the log.

Fewer sync operations. Forcing data to disk is the slowest part of a commit. WAL needs fewer of these, and with the synchronous = NORMAL setting it only syncs during checkpoints.

SQLite's own documentation says WAL is significantly faster in most scenarios. How much depends on your storage and workload, so measure with your own data if the number matters.

What it costs

These are the disadvantages listed in the SQLite documentation, in plain terms.

• Every process must be on the same machine. WAL coordinates connections through a small region of shared memory, which is backed by the -shm file. Processes on different computers cannot share memory.
• Two extra files. yourdb-wal and yourdb-shm sit next to the database while it is open. The -wal file holds real data. If you copy or move the database while it is in use, the WAL must go with it.
• Checkpoints exist. They run automatically, so you can ignore them at first. In a busy application they need some attention, because a reader that never finishes can stop them from completing and let the -wal file grow.
• No atomic commit across attached databases. If one transaction changes two databases joined with ATTACH, each is committed safely on its own, but a crash at the wrong moment can leave one updated and the other not.
• Slightly slower when nearly everything is a read. The documentation estimates 1% or 2% for applications that mostly read and seldom write.
• The page size is fixed while in WAL mode. To change it you must switch back to a rollback mode first.

When you should not use it

• The database is on a network filesystem such as NFS or SMB, or several machines open the same file. This is the firm rule. WAL does not work across hosts.
• The platform has no shared memory. Some unusual or embedded environments use a storage layer without it. Turning WAL on fails there, and the database stays in its previous mode. (There is a workaround for a single process, exclusive locking mode, covered in the internals article linked below.)
• You depend on one transaction updating several attached databases atomically.
• The database will sit on read-only media, such as a disk image or a read-only mount. A WAL database can be read there only under certain conditions. SQLite's advice is to convert it to DELETE mode before putting it on read-only media.
• The file is a document format that users copy, email or sync as a single file. The extra files are easy to lose. SQLite removes them when the last connection closes cleanly, but a copy taken while the application is open can miss data.
• You run very large single transactions. A transaction that rewrites hundreds of megabytes produces a WAL of that size, and the WAL cannot be trimmed until the transaction ends. Since SQLite 3.11.0 WAL handles large transactions as efficiently as rollback mode, but the disk space is still needed. Split bulk loads into several transactions, or do them in rollback mode.
• Only one connection ever uses the database. A single-user script gains no concurrency. WAL may still be faster for writes, but there is nothing wrong with the default.

If none of these apply and more than one thread or process opens the database, turn WAL on.

How to turn it on

Run one statement on any connection:

PRAGMA journal_mode = WAL;

The statement returns a row containing the mode now in effect. Check it. wal means it worked. Anything else, such as delete, means SQLite could not switch and left the database as it was.

From the command line:

sqlite3 app.db "PRAGMA journal_mode = WAL;"

In Python:

import sqlite3

conn = sqlite3.connect("app.db")
mode = conn.execute("PRAGMA journal_mode = WAL").fetchone()[0]
print(mode)   # wal

Requirements: SQLite 3.7.0 or later (released in 2010, so nearly every installation qualifies) and a real file. An in-memory database cannot use WAL; the statement returns memory.

Two more settings usually go with it. Unlike the journal mode, these apply only to the connection that runs them, so set them each time you connect:

PRAGMA busy_timeout = 5000;    -- wait up to 5 seconds for a lock instead of failing at once
PRAGMA synchronous = NORMAL;   -- optional: faster commits, see below

synchronous = NORMAL is the common pairing with WAL. The database cannot be corrupted by a crash or power failure in this configuration, but the last few transactions committed just before a power loss might be missing afterwards. If losing a committed transaction is never acceptable, leave synchronous at FULL.

The setting is persistent

The journal mode is recorded in the database file itself. Set it once and the database is in WAL mode for every connection, in every program and language, including after a restart. You do not need to run the pragma on each connection, though doing so is harmless.

This also means you can convert an existing application without changing its code: stop it, run the pragma once from the sqlite3 shell, start it again.

This is the exception among the journal modes. If you set another one, such as TRUNCATE, the database goes back to DELETE the next time it is opened. busy_timeout and synchronous are not stored in the file either.

To see what mode a database is in:

PRAGMA journal_mode;

How to switch back

PRAGMA journal_mode = DELETE;

This must be the only connection to the database. If any other connection has it open, the statement fails with "database is locked", so stop the application first. On success SQLite checkpoints the remaining changes into the database file and removes the -wal and -shm files, leaving a single self-contained file.

Switch back before you put a database on read-only media, hand it to someone as a single file, or move it to a machine with a very old SQLite.

Quick answers

Does WAL allow more than one writer at a time? No. SQLite permits one write transaction at a time in every mode. WAL removes the conflicts between readers and the writer, not between writers. A second writer waits for the first, for as long as its busy_timeout allows.

Why do I still get "database is locked" with WAL on? Usually because two connections want to write at once and one has no timeout, or because a transaction read first and tried to write afterwards. SQLITE_BUSY error: what it means and what causes it explains the causes and How to fix SQLITE_BUSY: a step-by-step playbook has the fixes. For Python there is How do I fix SQLITE_BUSY ("database is locked") in Python?.

Can I delete the -wal and -shm files? Not by hand. The -wal file can contain committed transactions that are not in the main file yet, and deleting it loses them. Close every connection and SQLite removes both files itself.

Why are the files still there after my program exits? Either the program did not close its connection cleanly, another process still has the database open, or the last connection to close was a read-only one. The files are harmless. Opening and closing the database normally cleans them up. Some platform builds of SQLite, including the one that ships with macOS, keep them on purpose.

How big does the -wal file get? With default settings SQLite checkpoints once it reaches about 1000 pages, roughly 4 MB. It grows well beyond that only if checkpoints cannot finish.

Does WAL make reads faster? Not individually. What improves is that reads are no longer stopped by writes, and writes no longer wait for reads.

How do I back up a WAL database? Not by copying the main file while the application runs. Use the .backup command of the sqlite3 shell or VACUUM INTO, both of which produce a consistent copy from a live database.

Is it safe? Yes. WAL has been part of SQLite since version 3.7.0, and with synchronous = FULL transactions are fully durable. One thing to check is the SQLite version: a rare corruption bug in WAL mode was fixed in 3.51.3, so prefer a current release.

For checkpoints, file growth, durability settings, backups and read-only access in detail, see SQLite WAL mode: how it works, checkpoints and tuning. For whether SQLite as a whole fits your project, see SQLite in production: WAL mode and knowing when it's enough.

Common mistakes

• Not checking what the pragma returned. If it did not return wal, nothing changed.
• Assuming WAL means concurrent writers. It does not.
• Copying only the .db file from a running application.
• Deleting the -wal file because it looks like a temporary file.
• Putting the database on a shared network drive so several machines can use it.
• Setting busy_timeout once and expecting it to stick. Only the journal mode is saved in the file.

Checklist

1. Confirm the database file is on a local disk and all processes that use it run on the same machine.
2. Run PRAGMA journal_mode = WAL; once and confirm it returns wal.
3. Set PRAGMA busy_timeout on every connection.
4. Choose synchronous: NORMAL for speed, FULL if no committed transaction may ever be lost.
5. Treat -wal and -shm as part of the database; never delete or separate them by hand.
6. Back up with .backup or VACUUM INTO.
7. Switch back to DELETE mode before shipping the database as a single file or on read-only media.

References

• Write-ahead logging (sqlite.org): advantages, disadvantages, persistence, read-only databases
• PRAGMA journal_mode and PRAGMA synchronous
• Atomic commit in SQLite: how the rollback journal works
• Isolation in SQLite: what readers see in each mode
• SQLite over a network: caveats and considerations
• SQLite backup API and VACUUM INTO

SQLite WAL mode: how it works, checkpoints and tuning

How the -wal and -shm files, read marks and checkpoints work, why a WAL grows without bound, and how to set checkpointing, journal_size_limit and synchronous.

#concurrency#configuration#internals