SQLite WAL Mode: Let Readers and Writers Coexist Without Lock Errors

An embedded app writes a few sensor rows per second while a dashboard opens long-running reads. On the default rollback journal you get intermittent database is locked errors. The useful takeaway is that switching SQLite to Write-Ahead Logging decouples readers from writers, so reads no longer block writes and writes no longer block reads.

WAL is a supported SQLite journal mode, not an add-on. It changes where changes are recorded and how access is coordinated between connections.

What changes in I/O with WAL

In DELETE journal mode, a writer creates a rollback journal, modifies the main database file in place, then deletes the journal on commit. Readers that start before the write must wait for the lock to clear.

In WAL mode writes are appended to a separate -wal file. The main database file is left untouched until a checkpoint merges data back. Readers continue to read from a snapshot of the main file plus any needed pages from the WAL. A single writer can proceed while multiple readers are active.

This append-only pattern reduces disk I/O for small transactions and is especially helpful for high read-to-write ratios common in telemetry and logging apps.

How coordination works with -wal and -shm

WAL relies on two auxiliary files in the same directory as the database:

• db.sqlite-wal holds the log of recent changes, appended sequentially.
• db.sqlite-shm is a shared-memory file used to coordinate readers and writers across processes and threads.

Because coordination uses POSIX shared memory and file locks, WAL is not safe on network filesystems such as NFS or SMB. The locking primitives are not reliable there and can lead to corruption.

A checkpoint is required to move committed frames from the WAL back into the main database file and truncate the WAL. SQLite performs checkpoints automatically under certain conditions, and you can control them with PRAGMA wal_checkpoint.

Worked example: enable and verify WAL safely

Run the following in a SQLite client or from your application code where the database file is writable. Replace db.sqlite with your path.

-- Run once per database, requires write permission on the directory
PRAGMA journal_mode=WAL;
PRAGMA journal_mode;

Expected check: the second statement returns wal. If it returns delete, the change did not persist, usually because the directory is not writable or another connection holds the database open.

After a write transaction, list the directory. You should see db.sqlite-wal and db.sqlite-shm appear while connections are open. Their presence means WAL is active. Do not move or copy the database file while those files exist; the set is not a single-file entity during active sessions.

Risk note: enabling WAL changes the on-disk layout. Back up the database before changing journal mode in production, and ensure all processes using the file are restarted so they open it with the new mode.

Trade-offs you will feel in production

Checkpoint latency. Merging the WAL into the main file can cause occasional write latency spikes, especially if the WAL grows large. You can observe size with PRAGMA wal_checkpoint(TRUNCATE); in a maintenance window, but avoid running aggressive checkpoints on the hot path.

File layout. The database is no longer a single file while in use. Deployment scripts that expect to copy db.sqlite alone will capture a stale snapshot.

Platform limits. WAL is unsupported on network filesystems and some FUSE mounts. Verify the storage backend before enabling.

Verification you can do without load testing: open two connections, start a long read transaction on one, then issue a write on the other. In WAL mode the write proceeds without database is locked. In DELETE mode the write would block or fail depending on busy timeout.

Actionable closing

Enable WAL for embedded apps with concurrent readers and occasional writers, monitor WAL size, and schedule checkpoints during low traffic. Keep the database and its -wal/-shm files on local storage, and treat the three files as a unit for backup and restore.