SQLite Concurrency and WAL

Rollback journal versus WAL

In the default rollback journal mode, a writer copies original pages to a journal and modifies the database file. Readers are blocked while a write is in progress.

In WAL mode, writers append to a separate write-ahead log and readers continue reading the main file at a consistent snapshot. Readers no longer block the writer, and the writer no longer blocks readers.

PRAGMA journal_mode = WAL;

This setting is persistent — it is stored in the database file, so it survives reconnections.

Still one writer

WAL does not give you concurrent writers. A second write transaction waits, or fails with SQLITE_BUSY.

PRAGMA busy_timeout = 5000;   -- wait up to 5 seconds for a lock before failing

Without a busy timeout, a concurrent writer fails immediately — which is the most common cause of "SQLite doesn't work under load" reports.

Transactions

SQLite has three transaction types, and choosing the right one prevents a specific deadlock:

BEGIN DEFERRED;    -- default: no lock until the first access
BEGIN IMMEDIATE;   -- acquires the write lock immediately
BEGIN EXCLUSIVE;   -- strongest; blocks other readers in non-WAL modes

# Read-modify-write done safely.
conn.execute("BEGIN IMMEDIATE")
row = conn.execute("SELECT balance FROM accounts WHERE id = ?", (account_id,)).fetchone()
conn.execute("UPDATE accounts SET balance = ? WHERE id = ?", (row[0] - amount, account_id))
conn.commit()

WAL checkpointing

The WAL file grows until a checkpoint copies its contents back into the main database. Checkpoints happen automatically when the WAL exceeds wal_autocheckpoint pages (1000 by default).

PRAGMA wal_autocheckpoint = 1000;
PRAGMA wal_checkpoint(TRUNCATE);   -- force a checkpoint and shrink the WAL file

Durability

PRAGMA synchronous = FULL;     -- fsync on every commit; safest
PRAGMA synchronous = NORMAL;   -- with WAL: safe against process crash, small risk on power loss
PRAGMA synchronous = OFF;      -- fast, and a power loss can corrupt the database

NORMAL with WAL is the usual production choice: durable against application and OS crashes, with a narrow exposure to power loss. OFF is only acceptable for data you can regenerate.