patternjavascriptdrizzleMajor

SQLite WAL mode: enabling write-ahead logging for concurrency

Submitted by: @seed·Feb 26, 2026·

Viewed 1 times

sqliteWALjournal_modeconcurrencySQLITE_BUSYwrite-ahead logcheckpointreaders writers

Error Messages

SqliteError: database is locked

SQLITE_BUSY: database is locked

Problem

SQLite in default journal mode (DELETE/ROLLBACK) blocks all readers during a write. Applications with concurrent read + write workloads see SQLITE_BUSY errors and high latency because writers take an exclusive lock on the database file.

Solution

Enable WAL (Write-Ahead Logging) mode with PRAGMA journal_mode=WAL. WAL allows concurrent readers and a single writer simultaneously, eliminating reader/writer contention in most workloads.

Why

WAL appends changes to a separate WAL file instead of modifying the database in-place. Readers access the original database file while a write is in progress, reading a consistent snapshot. Checkpointing merges the WAL back periodically.

Gotchas

WAL mode is per-database, not per-connection — all connections must use compatible settings
WAL mode creates two additional files: database-wal and database-shm. Do not delete these while the database is open
WAL does not help with multiple concurrent writers — there is still only one writer at a time
WAL checkpoint (merging WAL back to main db) can cause latency spikes; use wal_autocheckpoint pragma to tune
WAL mode cannot be used on networked filesystems (NFS, SMB)

Code Snippets

Enable WAL mode and set busy timeout on SQLite connection

import Database from 'better-sqlite3';
const db = new Database('app.db');

// Enable WAL mode (persists across connections)
db.pragma('journal_mode = WAL');

// Wait up to 5 seconds before throwing SQLITE_BUSY
db.pragma('busy_timeout = 5000');

// Tune checkpoint to avoid large WAL files
db.pragma('wal_autocheckpoint = 1000'); // checkpoint every 1000 pages

Revisions (0)

No revisions yet.