- Book: Database Playbook
- Also by me: Thinking in Go (2-book series) — Complete Guide to Go Programming + Hexagonal Architecture in Go
- My project: Hermes IDE | GitHub — an IDE for developers who ship with Claude Code and other AI coding tools
- Me: xgabriel.com | GitHub
SQLite on a production server stopped being a stunt a while ago. Rails 8 shipped with SQLite-backed adapters for its cache, its job queue and Action Cable, and the Rails team says there are "thousands of installations" of Campfire and Writebook running on SQLite. The SQLite authors themselves say a site with fewer than 100K hits a day should be fine, and call that figure conservative.
Then you add a second worker process. Then a cron job. Then a queue consumer. Four processes write to the same file, and the logs fill with database is locked.
That message is SQLITE_BUSY, and almost every fix you will find online is one of three settings: WAL mode, a busy timeout, or BEGIN IMMEDIATE. Most posts hand you all three at once. This one turns them on one at a time with a script you can run, so you can see which setting removes which failure. The third one matters most, and it is the one people skip.
Why four writers is enough
SQLite allows one writer at a time per database file. That is by design. The docs say most write transactions take milliseconds, so multiple writers can take turns. The question is what a writer does when it is not its turn.
In the default rollback-journal mode (journal_mode=DELETE), the locking model has readers holding SHARED locks, one would-be writer holding a RESERVED lock, and the actual write to the file needing an EXCLUSIVE lock that can only be taken once every SHARED lock is gone. So in this mode, readers block the commit of a writer.
When a connection can't get the lock it needs, SQLite returns SQLITE_BUSY (primary code 5, per the result-code docs). With no busy timeout set, it returns it right away. node:sqlite defaults the timeout to 0 milliseconds. So the first collision is a thrown error.
SQLite lets one writer through at a time. The settings below decide whether the others wait or fail.
The script: four writers, four setups
The script below uses only Node's built-in node:sqlite. The timeout option it relies on was added in Node v22.16.0 and v24.0.0, so use one of those or newer. It forks N child processes. Each runs a read-modify-write transaction in a loop: read a counter, write the counter plus one, commit. That shape (read first, write second) is what most real application code does: check a balance, then debit it; check stock, then reserve it.
// contend.mjs
// usage: node contend.mjs <setup> [writers] [txns]
import { DatabaseSync } from 'node:sqlite';
import { fork } from 'node:child_process';
import { rmSync } from 'node:fs';
const FILE = 'contend.db';
const SETUPS = {
rollback: { wal: false, timeout: 0, begin: 'BEGIN' },
wal: { wal: true, timeout: 0, begin: 'BEGIN' },
'wal-timeout': { wal: true, timeout: 5000, begin: 'BEGIN' },
'wal-immediate': {
wal: true, timeout: 5000, begin: 'BEGIN IMMEDIATE',
},
};
const args = process.argv.slice(2);
const name = args[0] ?? 'rollback';
const setup = SETUPS[name];
if (!setup) throw new Error(`unknown setup: ${name}`);
const WRITERS = Number(args[1] ?? 4);
const TXNS = Number(args[2] ?? 500);
// errcode is the extended code; the low 8 bits are the primary
const isBusy = (err) => (err.errcode & 0xff) === 5;
function writer() {
const db = new DatabaseSync(FILE, { timeout: setup.timeout });
const read = db.prepare(
'SELECT value FROM counter WHERE id = 1');
const write = db.prepare(
'UPDATE counter SET value = ? WHERE id = 1');
let ok = 0, busy = 0, snapshot = 0;
for (let i = 0; i < TXNS; i++) {
try {
db.exec(setup.begin);
const { value } = read.get();
write.run(value + 1);
db.exec('COMMIT');
ok++;
} catch (err) {
if (!isBusy(err)) throw err;
busy++;
if (err.errcode === 517) snapshot++;
try { db.exec('ROLLBACK'); } catch {}
}
}
db.close();
process.send({ ok, busy, snapshot });
}
function spawn() {
return new Promise((resolve, reject) => {
const child = fork(import.meta.filename, args, {
env: { ...process.env, CONTEND_WORKER: '1' },
});
child.once('message', resolve);
child.once('error', reject);
child.once('exit', (code) => {
if (code) reject(new Error(`writer exited ${code}`));
});
});
}
async function main() {
for (const ext of ['', '-wal', '-shm', '-journal']) {
rmSync(FILE + ext, { force: true });
}
const db = new DatabaseSync(FILE);
if (setup.wal) db.exec('PRAGMA journal_mode = WAL');
db.exec(`CREATE TABLE counter
(id INTEGER PRIMARY KEY, value INTEGER NOT NULL);
INSERT INTO counter VALUES (1, 0);`);
db.close();
const results = await Promise.all(
Array.from({ length: WRITERS }, spawn));
const sum = (k) => results.reduce((a, r) => a + r[k], 0);
const check = new DatabaseSync(FILE);
const { value } = check
.prepare('SELECT value FROM counter WHERE id = 1').get();
check.close();
console.log({
setup: name,
attempted: WRITERS * TXNS,
committed: sum('ok'),
busy: sum('busy'),
busySnapshot: sum('snapshot'),
counter: value,
});
}
if (process.env.CONTEND_WORKER === '1') writer();
else await main();
Run it four times:
node contend.mjs rollback
node contend.mjs wal
node contend.mjs wal-timeout
node contend.mjs wal-immediate
The counts depend on disk speed, core count and scheduling, so this post doesn't print numbers. Two lines in the output are there to check your reading of it. counter must equal committed in every setup: SQLite never loses an update, it refuses one. And busySnapshot counts extended code 517, SQLITE_BUSY_SNAPSHOT, which only appears in WAL mode and is the clue to the third setup.
A note on naming: in Node v26.11.0 DatabaseSync was renamed to Database, with the old name kept as a documentation-only deprecated alias. The script uses DatabaseSync so it runs on the 22 and 24 lines too.
What each setup changes
rollback. The default journal, no timeout. Every collision is a thrown SQLITE_BUSY: a writer can't take the RESERVED lock because another writer has it, or can't commit because a reader still holds SHARED. Expect a lot of failures.
wal. WAL mode changes the reader side: "readers do not block writers and a writer does not block readers." Writers append to a separate log, so commits no longer wait for readers. But the same page is plain about the other half: "there can only be one writer at a time." With the timeout still at 0, two writers colliding still means an instant error. WAL mode is persistent once set, which is why the script sets it once in the parent and the children inherit it.
wal-timeout. Now each connection waits up to 5 seconds for a lock instead of failing at once. Node's timeout option is the same thing as PRAGMA busy_timeout. Most guides stop here. Expect busy errors to remain, and expect some of them to be 517.
wal-immediate. Same timeout, but every transaction opens with BEGIN IMMEDIATE. The transaction docs say this "causes the database connection to start a new write immediately, without waiting for a write statement." The result-code docs give the guarantee that matters: BEGIN IMMEDIATE "might itself return SQLITE_BUSY, but if it succeeds, then SQLite guarantees that no subsequent operations on the same database through the next COMMIT will return SQLITE_BUSY." The wait moves to the BEGIN, where the timeout can actually do its job. With transactions this short, you should expect busy to drop to zero.
BEGIN IMMEDIATE asks for the write lock up front, so waiting happens before you have read anything.
Why busy_timeout can't save a deferred transaction
Plain BEGIN is DEFERRED, the default. Nothing is locked until the first statement. If that statement is a SELECT, you get a read transaction, and the docs say a later write "will upgrade the transaction to a write transaction if possible, or return SQLITE_BUSY." The upgrade is not possible "if some other database connection has already modified the database or is already in the process of modifying the database."
Walk through it in WAL mode. Writer A runs BEGIN and reads value = 41. A now has a snapshot of the database as it was at that moment. Writer B commits value = 42. A tries UPDATE ... SET value = 42. If SQLite let that through, B's write would vanish. So A gets SQLITE_BUSY_SNAPSHOT: another connection "has already written to the database and thus invalidated prior reads." Waiting can't fix this. A's snapshot is old, and no amount of sleeping makes it current again. The only way out is to roll back and start over with a fresh read.
The rollback-journal version of the same trap is spelled out in the busy handler docs: "The presence of a busy handler does not guarantee that it will be invoked when there is lock contention." One process holds a read lock and wants to promote it; another holds the reserved lock and needs the readers gone before it can commit. Each waits on the other, so SQLite skips the busy handler and returns SQLITE_BUSY to the reader "hoping that this will induce the first process to release its read lock."
busy_timeout only helps a connection that holds nothing yet. A deferred transaction that has already read is holding something, and BEGIN IMMEDIATE makes it wait before the read.
The settings to copy, and their limits
For a server process writing to SQLite, the combination looks like this:
PRAGMA journal_mode = WAL; -- once; it persists
PRAGMA busy_timeout = 5000; -- per connection
PRAGMA synchronous = NORMAL; -- optional, see below
Then open every transaction that will write with BEGIN IMMEDIATE, including the ones that read first. Read-only transactions can stay deferred. The synchronous = NORMAL line is the pragma docs' own recommendation for WAL: transactions stay atomic, consistent and isolated, but you "lose durability across power lose" (their typo). Decide whether a lost last commit after a power cut is acceptable before you copy it.
Keep transactions short. A busy timeout only holds while the writer ahead of you finishes quickly. If one transaction calls an HTTP API while holding the write lock, every other writer waits with it, and 5 seconds goes fast.
WAL also has a hard constraint: "all processes using a database must be on the same host computer; WAL does not work over a network filesystem," per the WAL docs. And even in WAL mode the same page lists a few rare cases that still return SQLITE_BUSY, such as a connection running recovery after a crash, so keep the error handling.
When the single writer means it's time for Postgres
The SQLite authors give the cutoff themselves. Their checklist says choose a client/server database when "many threads and/or processes need to write the database at the same instant (and they cannot queue up and take turns)," when the data sits across a network from the application, or when it won't fit comfortably in one file. For a write-intensive site, or one "so busy that it requires multiple servers," they say to consider a client/server engine.
Translated into symptoms from the script: if wal-immediate still throws busy errors at your real transaction length, or the time writers spend queued at BEGIN IMMEDIATE becomes a visible share of your request latency, you are past the point where taking turns works. The same applies the day you need a second app server writing to the same data. In PostgreSQL's MVCC, reading never blocks writing and writing never blocks reading, and its locks are per table or per row rather than per file. You pay for it with a server to run.
Until then, run the script with your real transaction body in place of the counter update. If wal-immediate comes back clean, four writers is fine on SQLite.
If this was useful
Picking a data store usually comes down to limits like the one in this post: the single writer, the same-host rule, what a lock costs you when traffic grows. The Database Playbook goes through those trade-offs for SQLite, Postgres, Redis, DynamoDB and the rest, so you can tell before the logs fill up whether you have a configuration problem or need a different store.
Top comments (0)