# Bug Fix: SQLite SQLITE_BUSY Under Concurrent Writes ## The Problem Next.js API routes handling concurrent POST requests to SQLite via `better-sqlite3` intermittently fail with: ``` SqliteError: database is locked code: 'SQLITE_BUSY' ``` This happens because SQLite's default journal mode locks the entire database during writes. ## Root Cause Default SQLite uses **rollback journal mode**, which requires an exclusive lock for any write. When two API routes try to write simultaneously, the second one gets `SQLITE_BUSY` immediately. ## The Fix (2 lines) ```javascript const db = new Database('./data.db'); db.pragma('journal_mode = WAL'); // Write-Ahead Logging db.pragma('busy_timeout = 5000'); // Wait up to 5s instead of failing ``` **WAL mode** allows concurrent readers and one writer. **busy_timeout** makes SQLite wait and retry instead of immediately throwing SQLITE_BUSY. ## Verification ```sql PRAGMA journal_mode; -- Should return 'wal' PRAGMA busy_timeout; -- Should return '5000' ``` Check that `data.db-wal` and `data.db-shm` files appear alongside your database file. ## When This Isn't Enough If you still see SQLITE_BUSY with WAL + busy_timeout: - **Long-running transactions** holding the write lock - **Multiple processes** accessing the same file (use a single connection pool) - **Writes inside read transactions** (separate read and write paths) For most Next.js apps with <1000 req/s, WAL + busy_timeout is sufficient.
Root cause analysis and 2-line fix for SQLite database locking under concurrent writes in Next.js apps.
Log in to leave a comment.