SQLite WAL Mode: Diagnosing Readers Blocked by a Writer

The condition: reads fail while a writer runs

A SQLite-backed service starts returning database is locked or SQLITE_BUSY on SELECT statements while a background job is writing. Sometimes it is the reverse: writes fail while a long report query runs. Both look like SQLite cannot handle concurrency, but the fix depends on which journal mode the file is actually in and which connection is holding what.

The short version: Write-Ahead Logging (WAL) lets readers and writers proceed at the same time, and it is a persistent property of the database file rather than a per-connection setting. Enabling it is one pragma. Living with it means managing two extra files, a checkpoint schedule, and a set of busy errors that WAL does not remove.

Cause map: similar symptoms, different findings

Ordered checks

1. Confirm the version and the file's journal mode. Run in the sqlite3 CLI against the database file, or the equivalent query from an application connection:

   SELECT sqlite_version();
   PRAGMA journal_mode;

   WAL requires SQLite 3.7.0 or newer. journal_mode is stored in the database header, so it persists across connections and restarts. If it does not return wal, no connection has successfully switched this file, and the behavior you are seeing is rollback-journal behavior.

2. Check whether the switch is actually accepted. From a connection with write access, and ideally with no other connections open:

   PRAGMA journal_mode=WAL;

   The pragma returns the resulting mode. If it returns the previous mode instead of wal, the change was refused, usually because another connection holds a conflicting lock or the filesystem cannot support it. After a successful switch, expect a -wal and a -shm file next to the database.

3. Check busy handling on every connection. PRAGMA busy_timeout; defaults to 0, meaning an immediate SQLITE_BUSY. Set a timeout, for example PRAGMA busy_timeout=5000;, on each connection including readers. A timeout does not make concurrent writers possible; it only makes a connection wait for the lock instead of failing at once.

4. Look for long-lived read transactions. A read transaction that stays open, whether from an unfinalized statement, a BEGIN with no COMMIT, or a pooled connection holding a cursor, pins a snapshot and prevents a checkpoint from reclaiming WAL space. This is the most common reason a -wal file keeps growing in an application that looks idle.

5. Measure the WAL and checkpoint state. Check the size of the -wal file, then run PRAGMA wal_checkpoint;, which reports whether the checkpoint was blocked and how many frames were processed. The default automatic checkpoint runs every 1000 pages, visible as PRAGMA wal_autocheckpoint;. A busy result means a reader is still holding an older snapshot.

6. Check where the file lives. WAL assumes all processes are on the same host and that the filesystem provides the shared memory and locking SQLite expects. Network shares and some container volume types are the usual source of locking failures that do not reproduce locally. If the path is a network mount, that is the finding, not a tuning problem.

Fixes matched to the finding

• journal_mode is not wal: enable it once from a write connection and re-check with PRAGMA journal_mode;. If it is refused, find the other open connection or move the file to local storage.
• SQLITE_BUSY on writes with WAL active: keep write transactions short, set busy_timeout, and avoid read-then-write upgrades inside one transaction. A connection that reads and then tries to write can fail with SQLITE_BUSY_SNAPSHOT if another connection committed in between; the correct response is to retry the whole transaction, not just the write statement.
• WAL growth: close the long reader, then checkpoint explicitly with PRAGMA wal_checkpoint(TRUNCATE); during a quiet period. TRUNCATE also shrinks the WAL file, but it cannot complete while a reader holds an older snapshot.
• Backups and copies: do not copy only the .db file while WAL is active. Use the SQLite backup API, the CLI .backup command, or VACUUM INTO (SQLite 3.27.0 and newer), which produce a consistent single file.
• Network filesystem: move the database to local storage, or use a client/server database. There is no pragma that makes WAL safe over a network share.

What WAL does not fix

WAL removes the reader-versus-writer conflict. It does not allow two writers at once: write transactions still serialize, and a second writer waits or fails. If the workload genuinely needs concurrent writers, WAL is the wrong tool and a client/server engine is the right one. WAL is also a poor fit for read-only media, because it needs to create and write the -wal and -shm files, and for single-process embedded use where two extra files per database are a liability.

Verification

After enabling WAL, confirm three things. First, PRAGMA journal_mode; returns wal from a fresh connection. Second, the -wal file exists and changes size while writes are running. Third, open two connections, hold a write transaction on one, and run a SELECT on the other; with WAL active the read should complete without waiting for the writer to commit. Finally, close all connections and check that -wal and -shm are removed, since by default the last connection to close checkpoints and deletes them. Files left behind after a clean shutdown point to a connection that did not close properly.

Rollback

WAL is persistent, so reverting is a state change. With no other connections open, run PRAGMA journal_mode=DELETE; or the previous mode, and verify the result with PRAGMA journal_mode;. Confirm that -wal and -shm are gone before restarting the application. Do not delete a -wal file by hand while it may contain committed transactions that have not been checkpointed; deleting it discards that data. The -shm file is rebuildable and can be removed if it is corrupted, but only when no connection is using the database.

Escalate when

• You need more than one concurrent writer; WAL cannot provide it.
• The database must live on a network share you cannot move.
• SQLITE_BUSY persists with WAL, a nonzero busy_timeout, and short transactions. Capture the exact result code, SQLITE_BUSY versus SQLITE_BUSY_SNAPSHOT, before changing anything, because the two point to different fixes.
• The -wal file grows with no long readers and no open transactions, which suggests a connection lifecycle problem rather than a WAL setting.