SQLite PRAGMA journal_mode=WAL: concurrent readers and a writer

By default SQLite uses a rollback journal (journal_mode=DELETE): while a writer commits, readers wait. In WAL (write-ahead log) mode, changes are appended to database.db-wal; readers keep reading the last committed state from the main file plus the log, so readers never block the writer and the writer never blocks readers. There is still only one writer at a time.

Turn it on and check it

python - <<'EOF'
import os, sqlite3
db = sqlite3.connect("app.db")
print("default mode:", db.execute("pragma journal_mode").fetchone()[0])
print("new mode:    ", db.execute("pragma journal_mode=wal").fetchone()[0])
db.execute("create table t (x)"); db.execute("insert into t values (1)"); db.commit()
print("files:       ", sorted(f for f in os.listdir(".") if f.startswith("app.db")))
db.close()

again = sqlite3.connect("app.db")          # a new connection: the setting is stored in the file
print("reopened:    ", again.execute("pragma journal_mode").fetchone()[0])
EOF

default mode: delete
new mode:     wal
files:        ['app.db', 'app.db-shm', 'app.db-wal']
reopened:     wal

Unlike most pragmas, journal_mode=WAL is persistent: set it once and every later connection uses it. The -wal and -shm files exist while connections are open and are folded back into the main file at checkpoints and when the last connection closes.

A typical setup for an application

PRAGMA journal_mode = WAL;     -- once per database
PRAGMA busy_timeout = 5000;    -- every connection: wait up to 5 s for the write lock
PRAGMA synchronous = NORMAL;   -- every connection: safe with WAL, fewer fsyncs
PRAGMA foreign_keys = ON;      -- every connection: off by default

Limits

• All processes must be on the same machine: WAL relies on shared memory, so it does not work over network file systems.
• Back up with VACUUM INTO 'backup.db' or the backup API; copying only the main file while the -wal file has data loses recent commits.
• Long-running read transactions stop checkpoints, and the -wal file grows until they finish.