Why Your SQLite App Locks Up Under Load — and What WAL Mode Actually Fixes

You built something small — a job queue, a metrics collector, a side project with a handful of users — and SQLite was the obvious choice. Then traffic picked up, and suddenly you're seeing database is locked errors every time a write lands while someone else is reading. The fix is usually one line of SQL, but it's worth understanding what that line changes before you flip it in production.

The default journal mode serializes everything

Out of the box, SQLite uses rollback-journal mode. In that mode, a writer takes an exclusive lock on the whole database file, and readers must wait until the transaction commits. Reads don't block each other, but any write blocks all reads, and any read blocks all writes. For a single-user app this is invisible. For a web service handling concurrent requests, it turns your database into a queue where every request waits its turn.

The thesis here is simple: if your workload mixes concurrent reads and writes, WAL (Write-Ahead Logging) mode is almost always the right default, and the cost of switching is low — but it comes with operational caveats you should know before you commit.

What WAL changes mechanically

In WAL mode, committed changes are appended to a separate file, yourdb.sqlite-wal, instead of being written directly into the main database file. Readers keep reading the main file plus a snapshot view of the log, coordinated through a shared-memory index in yourdb.sqlite-shm. The result: readers never block the writer, and the writer never blocks readers. Only writers still serialize against each other — SQLite remains single-writer no matter what.

Periodically, a checkpoint copies pages from the WAL back into the main database and resets the log. By default this happens automatically once the WAL reaches about 1000 pages, tunable with PRAGMA wal_autocheckpoint. A larger threshold means fewer checkpoints (less I/O churn) but a bigger WAL file and slightly slower recovery after a crash.

Enabling it, concretely

Run this from any connection with write access to the database file — the sqlite3 CLI works fine:

PRAGMA journal_mode=WAL;

Unlike most pragmas, this one is persistent: the mode is stored in the database header, so every future connection sees it. Verify with:

PRAGMA journal_mode;  -- should return: wal

Then list the directory. You should see -wal and -shm files appear next to your database once writes start flowing. To confirm checkpointing works, run PRAGMA wal_checkpoint(TRUNCATE); and watch the -wal file shrink to zero while the main file grows. One caution when converting an existing database: let the switch complete cleanly rather than killing the process mid-conversion, so the file isn't left needing recovery.

In application code, also set a busy timeout — WAL reduces lock contention but doesn't eliminate writer-vs-writer conflicts:

PRAGMA busy_timeout=5000;  -- milliseconds; set per connection

The trade-offs worth knowing

WAL is not free, and there are cases where you should stay on the default:

• Network filesystems. WAL depends on shared-memory coordination between processes. On NFS, SMB mounts, or many container volume drivers, that coordination is unreliable and can produce locking errors or corruption. If your database lives on a network mount, don't use WAL — or better, don't put the database there at all.
• File permissions. Every process opening the database needs read-write access to the -shm and -wal files, not just the main file. Permission mismatches across services or containers are a common source of confusing lock errors.
• Extra files to manage. Backups that copy only the .sqlite file while the database is live can miss un-checkpointed data. Use sqlite3 db.sqlite ".backup backup.sqlite" or checkpoint before copying.
• Single-connection workloads. If one process does everything sequentially, WAL adds I/O overhead without buying you anything.

How to check it's actually helping

Don't take the improvement on faith. Before and after switching, measure read latency under concurrent write load — even a simple loop of timed queries while a background writer commits will show whether lock waits disappeared. Watch the -wal file size under sustained writes; if it grows unboundedly, a long-running read transaction is pinning the log and preventing checkpoints, which is a real problem to fix in your code.

The actionable version: enable WAL, set a busy timeout on every connection, keep the database on local disk, and checkpoint before file-level backups. That covers the overwhelming majority of SQLite locking pain in small-to-medium services.