Skip to main content

Solving 'SQLITE_BUSY: database is locked' with WAL Mode and Busy Timeouts

 You have likely encountered SQLITE_BUSY (or OperationalError: database is locked) just as your application scales beyond a single user. It is the most common reason developers abandon SQLite for Postgres or MySQL prematurely.

The error occurs not because SQLite is incapable of concurrency, but because its default configuration prioritizes safety and backward compatibility over concurrent performance. By understanding SQLite's locking mechanism and adjusting two specific settings—Journal Mode and Busy Timeout—you can handle high-throughput concurrent workloads in Node.js and Python without migration.

The Root Cause: Rollback Journals and Exclusive Locking

To understand the fix, you must understand the locking model.

By default, SQLite uses a Rollback Journal (specifically DELETE mode). When a connection needs to write to the database:

  1. It acquires a SHARED lock to read data.
  2. It acquires a RESERVED lock to signal intent to write.
  3. It acquires an EXCLUSIVE lock. This is the bottleneck.

While holding the EXCLUSIVE lock, SQLite copies the original page content to a journal file and writes changes to the main database file. During this window, no other connection can read or write. If a second process attempts to write while the first holds the lock, it fails immediately with SQLITE_BUSY.

Additionally, the default "busy timeout" is often set to 0ms. If the lock cannot be acquired instantly, the driver throws an error rather than waiting for the lock to free up.

The Solution: WAL Mode and Busy Timeouts

We need to change the locking behavior to allow non-blocking reads and queue concurrent writes.

  1. Write-Ahead Logging (WAL): In WAL mode, changes are written to a separate -wal file. Readers read from the main database and the WAL file combined. Crucially, writers do not block readers, and readers do not block writers.
  2. Busy Timeout: Even in WAL mode, SQLite allows only one writer at a time. If two requests try to write simultaneously, one must wait. Setting a timeout instructs the driver to poll/sleep for a specified duration before throwing an error.

Implementation in Node.js

In the Node ecosystem, better-sqlite3 is the preferred driver for high-performance synchronous I/O. It is significantly faster than the asynchronous sqlite3 driver because it bypasses V8 overhead for C++ bindings.

Here is a robust singleton pattern ensuring the database is configured correctly on startup.

// lib/db.ts
import Database from 'better-sqlite3';
import path from 'node:path';

const dbPath = path.resolve('data.db');

// Open the database connection
const db = new Database(dbPath);

// CRITICAL: Enable Write-Ahead Logging
// This allows concurrent readers and writers.
db.pragma('journal_mode = WAL');

// CRITICAL: Set Busy Timeout
// If the DB is locked by another writer, wait up to 5000ms 
// before throwing SQLITE_BUSY.
db.pragma('busy_timeout = 5000');

// Optional: Optimize WAL checkpointing and synchronization
// NORMAL is faster and safe enough for most apps not requiring 
// strict durability on power loss.
db.pragma('synchronous = NORMAL');

export default db;

// Usage Example
// import db from './lib/db';
// const stmt = db.prepare('INSERT INTO users (name) VALUES (?)');
// stmt.run('Alice');

Implementation in Python

Python's standard sqlite3 library is blocking by default. When using Python in a concurrent environment (like FastAPI, Flask, or AWS Lambda), strict configuration is required.

# database.py
import sqlite3
from typing import Optional

DB_PATH = "data.db"

def get_db_connection() -> sqlite3.Connection:
    # isolation_level=None enables autocommit mode. 
    # This prevents Python from opening a transaction implicitly 
    # and holding locks longer than necessary.
    conn = sqlite3.connect(DB_PATH, isolation_level=None)
    
    # Enable WAL Mode
    conn.execute("PRAGMA journal_mode = WAL;")
    
    # Set Busy Timeout to 5000ms (5 seconds)
    conn.execute("PRAGMA busy_timeout = 5000;")
    
    # Performance tuning for WAL
    conn.execute("PRAGMA synchronous = NORMAL;")
    
    # Optional: Enable foreign keys (disabled by default in SQLite)
    conn.execute("PRAGMA foreign_keys = ON;")
    
    # Return rows as dictionaries for better DX
    conn.row_factory = sqlite3.Row
    
    return conn

# Usage Example:
# with get_db_connection() as conn:
#     conn.execute("INSERT INTO users (name) VALUES (?)", ("Alice",))

Why This Works

1. Concurrency via WAL

By switching to journal_mode = WAL, the locking dynamic changes fundamentally.

  • Old Way (DELETE mode): 1 Writer OR N Readers.
  • New Way (WAL mode): 1 Writer AND N Readers.

Your read-heavy endpoints (e.g., GET /api/products) will no longer return errors or block just because a background job is inserting data.

2. Resilience via Busy Timeout

WAL allows concurrent reads, but write transactions are still serialized. If Process A is writing, Process B cannot write until Process A commits.

Without a busy timeout, Process B fails immediately: Process A (Writing) -> Process B (Try Write) -> Process B (Error: Locked)

With busy_timeout = 5000: Process A (Writing) -> Process B (Try Write) -> Wait 10ms -> Wait 20ms -> Process A (Commit) -> Process B (Acquire Lock) -> Success

This essentially turns a hard crash into a slight latency spike, which is acceptable for the vast majority of web applications.

Conclusion

SQLite is capable of handling surprisingly high throughput if configured correctly. The default settings are designed for embedded devices, not servers. By enabling WAL mode to unblock readers and setting a busy timeout to queue writers, you eliminate the SQLITE_BUSY error for 99% of use cases.

If you are running on a networked file system (like NFS or AWS EFS), WAL mode is generally discouraged due to file locking inconsistencies. In those specific environments, stick to DELETE mode or use a dedicated database server. For block storage (local disk, AWS EBS), WAL is the standard.

Labels: