SQLite WAL Mode, Busy Timeout, Concurrent Readers

Default SQLite uses a rollback journal. A writer takes a reserved lock, copies changed pages aside, then exclusive-locks the file while it commits. Readers cannot hold the shared lock through that exclusive window, so even a short write burst stalls every SELECT. On a VPS with a web process, a cron job, and a backup connection, that stall shows up as random SQLITE_BUSY errors and tail latencies you cannot explain from CPU graphs.

Write-Ahead Logging flips the arrangement. The writer appends new pages to a sidecar -wal file. Readers keep using the main database plus a snapshot of WAL frames they have not checkpointed yet. A single writer and many readers coexist. That is the mode you want on any long-lived application database.

Enable WAL once, then check it

journal_mode is persistent. You set it, SQLite rewrites the file header, and later connections inherit WAL. Still set it on boot so a restored copy or a freshly created file cannot silently fall back.

PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA wal_autocheckpoint = 1000;
PRAGMA busy_timeout = 5000;

NORMAL plus WAL fsyncs the WAL at critical points without forcing a full disk flush on every commit. That is the usual production setting on High-Speed SSD. FULL is for the rare case where you would rather lose throughput than risk the last transaction after a hard host crash. wal_autocheckpoint of 1000 pages (about 4 MB with the default page size) keeps the WAL from growing without checkpointing on every commit.

Confirm what the running process actually has open. A PRAGMA against a different file path than the app uses tells you nothing.

sqlite3 /var/lib/myapp/data/app.db "PRAGMA journal_mode; PRAGMA busy_timeout; PRAGMA wal_checkpoint(PASSIVE);"
ls -l /var/lib/myapp/data/app.db*

You should see app.db, app.db-wal, and app.db-shm. If -wal never appears, you are not in WAL mode, or nothing has written since the last full checkpoint.

What busy_timeout actually does

SQLITE_BUSY is not a crash. It means another connection holds a lock this connection needs. Without a timeout, the statement fails immediately and your framework turns it into a 500. busy_timeout tells SQLite to sleep and retry for up to N milliseconds before giving up.

Five seconds is a sane default for a VPS app. It covers a writer finishing a short transaction. It does not cover a writer that holds a transaction open while it calls the network. If you see timeouts after 5000 ms, the fix is shorter transactions, not a 60 second timeout that hides a lock leak.

BEGIN IMMEDIATE is the other half of the story. BEGIN DEFERRED takes the writer lock only when the first write happens. Two connections can both start a deferred transaction, both read, then both try to write, and one loses. BEGIN IMMEDIATE takes the reserved lock at the start so the conflict happens early and the loser waits on busy_timeout instead of failing at COMMIT.

Readers, writers, and checkpoints

Readers in WAL mode do not block the writer. The writer does not block readers. The remaining contention is the checkpoint: copying WAL frames back into the main file. PASSIVE checkpoints only work that will not disturb readers. RESTART and TRUNCATE wait for readers to finish so the WAL can be reset. A backup or a long analytical query that sits on an old snapshot will delay a TRUNCATE checkpoint and let the WAL grow.

Operationally: let autocheckpoint handle the steady state. Run a PASSIVE checkpoint from a timer if you want a metric. Use TRUNCATE only during a quiet window when you need the WAL file small, for example before a filesystem snapshot.

Keep one writer. WAL is not MVCC for multiple writers. Two processes committing at once still serialize on the write lock. Many readers plus one writer is the design point. Many writers is a queue, a dedicated writer process, or Postgres.

WAL, a busy timeout, and one writer are the three settings that turn SQLite from a single-user file into a VPS database that can take web traffic. You can spin up an Ubuntu 24.04 instance on Netbay in under 60 seconds and follow along — netbayhosts.in.