Why Does Your SQLite Database Still Throw 'Database Is Locked' Errors in WAL Mode?

Ever wondered why, after toggling Write-Ahead Logging (WAL) and expecting your application to handle traffic like a mini-Postgres, you’re still staring at a Database is locked error? It feels like a betrayal. The documentation promised that WAL mode enables "many readers and one writer" to coexist peacefully without blocking each other. You did the work, you set the pragma, and yet, under even moderate load, your Node.js logs are screaming SQLITE_BUSY.

The truth is that WAL mode isn't a magic "concurrency: infinite" button. It’s a significant optimization, but it operates within the strict, single-writer constraints of SQLite’s architecture. If you’re seeing lock errors in WAL mode, it’s usually because of a misunderstanding of how SQLite transitions through lock states or how the Node.js event loop interacts with the database file.

Let's pull back the curtain on why this happens and how to actually fix it.

The WAL Mode "Single Writer" Reality

In the standard rollback journal mode, a writer requires exclusive access. No one can read while someone is writing, and no one can write while someone is reading. WAL changed the game by moving updates to a separate -wal file, allowing readers to keep looking at the main database file while a writer appends to the log.

But here is the catch: SQLite still only allows one writer at a time.

Even in WAL mode, if Process A is halfway through a transaction that intends to write data, Process B cannot start its own write transaction. It will wait. If it waits too long, it throws SQLITE_BUSY.

In a high-concurrency Node.js environment, "too long" can happen in milliseconds. If you have multiple API endpoints hitting the same SQLite file, or multiple worker threads attempting to update records, you are hitting a bottleneck at the "Reserved" lock stage.

The Five States of SQLite Locking

To understand why your code is failing, you have to understand the lifecycle of a lock. SQLite doesn't just go from "open" to "locked." It moves through a state machine:

1. UNLOCKED: No one is touching the file.
2. SHARED: You are reading. Many people can have SHARED locks.
3. RESERVED: You *plan* to write. Only one person can have a RESERVED lock. Readers can still read.
4. PENDING: Someone wants to write and is waiting for the current readers to finish so they can move to EXCLUSIVE. No new readers are allowed.
5. EXCLUSIVE: The actual writing is happening. No one else can do anything.

The Database is locked error (specifically SQLITE_BUSY) usually happens at the transition from SHARED to RESERVED.

In Node.js, we often write code like this:

const db = require('better-sqlite3')('data.db');

// This starts an implicit read transaction
const user = db.prepare('SELECT * FROM users WHERE id = ?').get(1);

if (user.status === 'pending') {
  // This attempts to upgrade the lock to RESERVED
  db.prepare('UPDATE users SET status = "active" WHERE id = ?').run(1);
}

If another process grabs a RESERVED lock between your SELECT and your UPDATE, your attempt to upgrade will fail. SQLite sees that you have a SHARED lock and want a RESERVED lock, but someone else already has a RESERVED lock. Deadlock risk. Boom: SQLITE_BUSY.

The Secret Weapon: BEGIN IMMEDIATE

The most common reason for SQLITE_BUSY in WAL mode is using the wrong type of transaction. By default, BEGIN is "deferred." It doesn't actually acquire a write lock until the first write command is issued.

If you know you are going to write, you should never use a plain BEGIN. You should use BEGIN IMMEDIATE.

BEGIN IMMEDIATE tells SQLite: "I want to start a write transaction right now. If someone else is already writing, make me wait now, rather than letting me start a read and failing later when I try to upgrade."

Here is how you implement this in a standard Node.js environment using better-sqlite3:

const db = require('better-sqlite3')('app.db');
db.pragma('journal_mode = WAL');

// The WRONG way:
// const tx = db.transaction(() => { ... }); 
// In better-sqlite3, transaction() uses BEGIN by default.

// The RIGHT way for write-heavy logic:
const performUpdate = db.transaction((data) => {
  const current = db.prepare('SELECT balance FROM accounts WHERE id = ?').get(data.id);
  const newBalance = current.balance + data.amount;
  db.prepare('UPDATE accounts SET balance = ? WHERE id = ?').run(newBalance, data.id);
});

// Force the transaction to be IMMEDIATE
const safeUpdate = (data) => {
  db.prepare('BEGIN IMMEDIATE').run();
  try {
    performUpdate(data);
    db.prepare('COMMIT').run();
  } catch (err) {
    db.prepare('ROLLBACK').run();
    throw err;
  }
};

*Note: In better-sqlite3, the .transaction() method actually has a .immediate() helper that handles this boilerplate for you:*

const safeUpdate = db.transaction((data) => {
  // Logic here...
}).immediate(); // This uses BEGIN IMMEDIATE

By using IMMEDIATE, you ensure that the moment the function starts, you either have the write lock or you are waiting in the queue for it. This prevents the "upgrade" failure where you have a read lock but can't get a write lock because someone else jumped the line.

Setting a Busy Timeout (Don't Skip This)

Even with BEGIN IMMEDIATE, if a write lock is held by another process, your code will immediately throw an error unless you tell SQLite to wait.

Many developers forget to set the busy_timeout. Without it, SQLite returns SQLITE_BUSY the microsecond it encounters a lock. You want it to retry internally for a few seconds before giving up.

const db = require('better-sqlite3')('app.db');

// Set the timeout to 5000ms (5 seconds)
db.pragma('busy_timeout = 5000');
db.pragma('journal_mode = WAL');

This is often enough to solve 80% of locking issues. It gives the other process time to finish its COMMIT and release the lock. In a Node.js context, this "waiting" happens inside the C++ layer of the driver, so it doesn't block the Node.js event loop for other tasks (provided you aren't using a synchronous loop that starves the CPU).

The Hidden Trap: WAL Checkpointing

So you’ve got WAL mode on, you’re using BEGIN IMMEDIATE, and you’ve set a busy_timeout. Yet, once a day, the database locks up for 30 seconds. What gives?

Enter Checkpointing.

In WAL mode, updates are written to the -wal file. Eventually, that data needs to be moved back into the main .db file. This process is called a checkpoint. By default, SQLite triggers a checkpoint automatically when the WAL file reaches 1,000 pages (about 4MB).

During a "Passive" checkpoint (the default), SQLite tries to move the data without upsetting anyone. But if a reader is holding an old transaction open, the checkpointer can't move the pages past that reader's "point in time." If the WAL file keeps growing because it can't be fully checkpointed, SQLite might eventually trigger a "Truncate" or "Restart" checkpoint, which requires an exclusive lock on the database.

If you have a long-running read operation (like a slow report or a leaked transaction that never closed), you can block the checkpointer. The WAL file grows, the system gets sluggish, and eventually, everything grinds to a halt.

The Fix:
1. Keep transactions short. Never, ever perform an API call or a heavy computation *inside* a database transaction.
2. Monitor your WAL file size. If it's gigabytes large, you have a "lingering reader" problem.

Connection Pooling: You're Doing It Wrong

If you come from the world of Postgres or MySQL, your instinct is to create a connection pool. In SQLite, this is usually a mistake, especially in Node.js.

SQLite is a file. If you have a pool of 10 connections in one Node.js process, you are essentially creating 10 competitors for the same file lock. Since Node.js is single-threaded (mostly), having multiple connections to a local SQLite file within a single process rarely provides a performance boost; instead, it increases the likelihood that Connection A will hold a lock that Connection B is waiting for.

The Strategy:
Use one single connection for your entire Node.js process. SQLite handles internal re-entrancy quite well, and the better-sqlite3 driver is designed for this. If you are using a serverless environment (like AWS Lambda) or a clustered setup (PM2), you'll have multiple processes hitting the same file. In that case, the busy_timeout and BEGIN IMMEDIATE are your only lines of defense.

When "Locked" Is Actually a Code Smell

Sometimes, a locked database is a signal that you're using the wrong tool for the job. SQLite is incredible, but it is not a multi-master, high-write-throughput engine.

I once worked on a system where we were logging every single incoming HTTP request (thousands per minute) into a SQLite table. Even in WAL mode, the "one writer" bottleneck caused the API response times to spike.

If you find yourself frequently fighting the SQLITE_BUSY error despite all the optimizations above, consider these architectural shifts:

1. A Write-Ahead Queue: Instead of writing to SQLite directly from your fast API path, push the data into an in-memory queue (or a fast local Redis instance) and have a single worker process drain that queue into SQLite in batches. Batching 1,000 inserts into a single transaction is vastly more efficient than 1,000 individual transactions.
2. Separate Databases: Does your "User Session" data need to be in the same file as your "Order History"? Probably not. Splitting data into multiple SQLite files allows for parallel writing (one writer per file).
3. The "Big Switch": If your write volume is consistently high and the data is highly relational/interconnected, it might be time to graduate to Postgres. There is no shame in it.

Implementation Checklist

If you are currently fighting a Database is locked error, go through this list in order:

1. Enable WAL Mode: PRAGMA journal_mode = WAL;.
2. Set a Busy Timeout: PRAGMA busy_timeout = 5000;.
3. Audit Transactions: Ensure every INSERT/UPDATE/DELETE is wrapped in a transaction that starts with BEGIN IMMEDIATE.
4. Check for Leaked Reads: Ensure every SELECT statement is finalized or closed. In some drivers, failing to consume the entire result set of a query can keep a SHARED lock open longer than you think.
5. Flatten Your Logic: Ensure you aren't doing "Read -> Wait for Network Call -> Write." Read the data, close the transaction, do your network call, then open a *new* IMMEDIATE transaction to write the result.

A Practical Example of the "Retry" Pattern

Sometimes, despite your best efforts, a SQLITE_BUSY still leaks through. This often happens in multi-process environments (like a Dockerized app with multiple replicas). A simple wrapper can save your life:

async function executeWithRetry(fn, retries = 5, delay = 100) {
  for (let i = 0; i < retries; i++) {
    try {
      return fn();
    } catch (err) {
      if (err.code === 'SQLITE_BUSY' && i < retries - 1) {
        // Wait a bit before retrying
        await new Promise(res => setTimeout(res, delay * Math.pow(2, i)));
        continue;
      }
      throw err;
    }
  }
}

// Usage
await executeWithRetry(() => {
  safeUpdate({ id: 123, amount: 50 });
});

This exponential backoff is a bit of a nuclear option, but it prevents your application from crashing when the OS or a checkpointing process temporarily clamps down on the file.

Why WAL Mode Still Wins

Despite the locking headaches, WAL mode is still the best way to run SQLite for web applications. The ability to have a heavy read query (like a 10-second aggregation for a dashboard) running in the background without blocking the INSERT of a new user record is a game-changer.

The "locked" errors aren't a sign that WAL is broken; they are a sign that the "one writer" rule is being challenged. Respect the single writer, use IMMEDIATE transactions to call your dibs early, and set a generous timeout. Do that, and you'll find that SQLite can go much further than most developers give it credit for.