Tune SQLite WAL Mode for Concurrent Readers and a Writer

Enable WAL journaling, set busy_timeout and synchronous pragmas, and prove readers keep working while a write transaction commits.

~20 mindatabasesqliteconcurrency

Task

Configure SQLite so concurrent readers and a single writer coexist without database-is-locked errors, and verify the behavior under load.

When to use this

An embedded service uses SQLite for state. Every nightly import locks the database and every dashboard query fails with database is locked for its entire duration.

Prerequisites

SQLite 3.22+ where WAL is stable and well-supported
A workload with concurrent reads plus periodic bulk writes
Access to the connection setup code

Steps

1. Enable WAL journaling once per database.

sqlite3 app.db "PRAGMA journal_mode=WAL;"

Expected output: The command prints wal, and from now on writers append to the write-ahead log while readers use the main file.

2. Set a busy timeout on every connection.

const db = new Database("app.db");
db.pragma("journal_mode = WAL");
db.pragma("busy_timeout = 5000");
db.pragma("synchronous = NORMAL");

Expected output: A five-second busy timeout makes lock contention wait and retry instead of failing instantly with SQLITE_BUSY.

3. Prove readers proceed during a write transaction.

sqlite3 app.db "BEGIN; UPDATE events SET synced=1 WHERE synced=0;" & sleep 0.2; sqlite3 app.db "SELECT count(*) FROM events;"

Expected output: The count query returns immediately while the update transaction is still open, which default rollback mode would never allow.

4. Keep write transactions short to let checkpoints pass.

BEGIN IMMEDIATE;
UPDATE events SET synced = 1 WHERE synced = 0 AND id <= ?;
COMMIT;

Expected output: BEGIN IMMEDIATE grabs the writer slot up front, avoiding mid-transaction upgrade failures, and the bounded range keeps the transaction short.

5. Check WAL size and checkpoint health.

sqlite3 app.db "PRAGMA wal_checkpoint(PASSIVE); SELECT * FROM pragma_wal_list;"

Expected output: A WAL file that stays bounded (a few tens of megabytes) confirms checkpoints are keeping up with the write rate.

Troubleshooting

Problem

database is locked still appears

Solution

Some connection still runs in rollback mode or holds a long read transaction that blocks checkpointing; verify journal_mode on every connection and cap reader transaction length.

Problem

WAL file grows unboundedly

Solution

A permanent reader keeps the checkpoint from advancing; close idle read connections periodically or schedule incremental checkpoints with wal_checkpoint(TRUNCATE).

Problem

Reader sees stale data during imports

Solution

That reader opened a transaction before the write committed; this is snapshot isolation working as designed, so finish the read and reopen if freshness matters.

Limitations

WAL allows one writer at a time; concurrent writers still serialize on the writer lock.
WAL does not work reliably on network filesystems; keep the database file on local disk.

One writer, many readers, no drama

In default rollback mode, SQLite blocks every reader for the entire duration of a write transaction, so one nightly import takes the dashboard down with it. WAL mode inverts the arrangement: writers append to a log file while readers snapshot the main database, and both proceed concurrently. Add a busy timeout so contention waits instead of erroring, keep write transactions short, and the database-is-locked era ends.

# Enable WAL once
sqlite3 app.db "PRAGMA journal_mode=WAL;"

# Reader unblocked while a write transaction is open
sqlite3 app.db "BEGIN; UPDATE events SET synced=1 WHERE synced=0;" &
sleep 0.2
sqlite3 app.db "SELECT count(*) FROM events;"   # returns immediately

busy_timeout is the courtesy clause

Even in WAL mode, two writers or a checkpoint collision can briefly contend. Without a busy timeout, SQLite fails the loser instantly with SQLITE_BUSY and pushes retry logic into every caller. Five seconds of server-side waiting absorbs nearly all contention and simplifies every client that talks to the database.

Checkpoints are the hidden garbage collector

The WAL file grows until a checkpoint folds it back into the main database, and a checkpoint cannot advance past the oldest active reader. A dashboard that holds a transaction open forever will balloon the WAL no matter how fast you write. Close idle readers on a schedule and watch the WAL size as a health metric.