阅读中文版本

node-sqlite3 SQLITE_BUSY — Database Is Locked

Fix intermittent SQLITE_BUSY database is locked errors from node-sqlite3 by enabling WAL mode, setting a busy timeout, and shortening write transactions.

db.run('PRAGMA busy_timeout = 5000')

db.run('PRAGMA journal_mode = WAL')

db.each('PRAGMA lock_status', (e, r) => console.log(e, r))

Symptoms

This guide profiles the failure behind “node-sqlite3 SQLITE_BUSY — Database Is Locked”. It walks through the observable symptoms, a reproducible diagnosis path, the underlying root cause, the concrete fix, and how to verify the change and prevent a regression.

Observed symptoms include:

• Writes fail intermittently with “SQLITE_BUSY: database is locked”
• Reads succeed while a concurrent write is running
• Errors appear only under load or with more than one process

Reproduce and Diagnose

Reproduce the failure in isolation before changing any code. Work through the checks below in order; each one narrows the search space.

Step 1: Enable a busy timeout and retry the operation

db.run('PRAGMA busy_timeout = 5000')

With a timeout, transient lock contention waits and succeeds instead of throwing immediately.

Step 2: Switch the journal mode to WAL

db.run('PRAGMA journal_mode = WAL')

WAL lets readers proceed while a writer is active, removing most reader/writer contention.

Step 3: Inspect for a long-held transaction

db.each('PRAGMA lock_status', (e, r) => console.log(e, r))

A writer that stays open across awaits or network calls keeps the lock and starves others.

Likely contributors, in order of frequency:

• SQLite allows a single writer; another write transaction still holds the lock
• No busy timeout is set, so a lock conflict fails immediately instead of waiting
• Long write transactions, often with an await inside, keep the lock for too long

Root Cause

SQLite serializes writers at the database-file level. In the default rollback journal mode a writer blocks readers and other writers; without a busy timeout any conflict is reported immediately as SQLITE_BUSY.

The Fix

Turn on WAL, set a busy timeout of a few seconds, keep write transactions short and free of awaits, and serialize writes through a single connection or an application-level mutex.

Watch out: WAL creates -wal and -shm sidecar files; back them up together or data may be lost on restore.

import sqlite3 from 'sqlite3';

const db = new sqlite3.Database('./app.db');
db.serialize(() => {
  db.run('PRAGMA journal_mode = WAL');
  db.run('PRAGMA busy_timeout = 5000');
  db.run('PRAGMA synchronous = NORMAL');
});

// Keep writes short; avoid awaits inside a transaction.
db.run('BEGIN IMMEDIATE');
db.run('UPDATE counters SET n = n + 1 WHERE id = ?', [1]);
db.run('COMMIT');

Verify and Prevent

Run the write workload under concurrency and confirm no SQLITE_BUSY. Check that journal_mode reports wal and busy_timeout is nonzero in PRAGMA output.

Once verified, add a regression test that reproduces the original symptom so the failure cannot silently return.

This class of failure is usually environmental rather than pure logic, so capture the exact command, the environment variables, and the working directory in your bug report. Reproduce it in the smallest possible script, then widen the scope only once the minimal case is understood. A short written checklist for this failure mode saves hours the next time it appears.