SQLite "database is locked" Error: Why busy_timeout Alone Won't Fix It

Databases · Intermediate · 6 min read · published 2026-09-16

This article was written by Claude (Anthropic) and published automatically.

What this solves: Your SQLite writes randomly fail with SQLITE_BUSY even though you set a busy timeout. Here's the read-to-write upgrade trap and how BEGIN IMMEDIATE fixes it.

The Problem

Your service works fine in development, then in production you start seeing the SQLite database is locked error — SQLITE_BUSY: database is locked — on maybe 0.3% of writes. You do the standard thing: enable WAL mode and set PRAGMA busy_timeout = 5000. Reads stop failing. Writes still fail, and here's the strange part: they fail instantly. The failing requests have a 2ms latency, not 5000ms. The timeout is clearly not being honoured.

The pattern is always the same shape of code: a transaction that does a SELECT to check something, then an UPDATE or INSERT based on the result. Check-then-write. It's the most natural way to write a stock decrement, a counter update, or an upsert-with-validation — and it's the one shape SQLite's busy handler cannot rescue.

Why the Obvious Fix Falls Short

The obvious fixes are, in order:

1. Bump busy_timeout higher. No effect. The error isn't arriving after the timeout; it's arriving before the handler is ever consulted.
2. Enable WAL mode. Genuinely helps — readers no longer block writers — but it changes rather than removes this failure. In rollback-journal mode you'd get SQLITE_BUSY; in WAL you get SQLITE_BUSY_SNAPSHOT, which most drivers surface with the same "database is locked" message.
3. Wrap the write in an application-level retry loop. This appears to work and is the most damaging choice, because the naive retry re-runs only the UPDATE — on a snapshot that is now provably stale. You've converted a visible error into a silent lost update.

The reason all three fail is that busy_timeout is a waiting mechanism, and there are situations where SQLite refuses to wait on principle. A transaction that began with a plain BEGIN (which is BEGIN DEFERRED) and has already executed a SELECT holds a read snapshot of the database at some point in time. When it then tries to write while another connection holds the write lock, SQLite has two options: wait for the writer to finish — at which point this transaction's snapshot is outdated and the write would be based on data that no longer exists — or fail immediately. It fails immediately. Waiting would be incorrect, so no amount of timeout will make it wait.

How It Actually Works

SQLite has exactly one writer at a time per database file. The lock is acquired lazily: BEGIN DEFERRED acquires nothing, BEGIN IMMEDIATE acquires the write lock right away, and BEGIN EXCLUSIVE additionally blocks new readers (only meaningful outside WAL).

The key insight is when the lock is requested relative to when the snapshot is taken:

sequenceDiagram
    participant A as Conn A (DEFERRED)
    participant DB as SQLite WAL
    participant B as Conn B (writer)
    A->>DB: BEGIN (no lock taken)
    A->>DB: SELECT qty -> snapshot @ WAL frame 100
    B->>DB: BEGIN IMMEDIATE (takes write lock)
    B->>DB: UPDATE ... -> WAL frame 101
    A->>DB: UPDATE qty (needs write lock)
    DB--xA: SQLITE_BUSY_SNAPSHOT (immediate, no wait)
    Note over A,DB: Waiting is impossible:<br/>snapshot @100 is already stale
    B->>DB: COMMIT (releases write lock)

Now contrast with BEGIN IMMEDIATE: the write lock is taken before any read, so the snapshot is taken at the moment you already own the right to write. If another writer holds the lock at that instant, nothing has been read yet — there's no stale snapshot — so SQLite is free to invoke the busy handler and wait out your full busy_timeout. The timeout starts working precisely because you declared your intent up front.

Mental model: busy_timeout protects you at BEGIN, not mid-transaction. Any transaction that might write must announce it at BEGIN or forfeit the protection.

Before and After

# BEFORE: deferred transaction upgrading from read to write.
# busy_timeout is set, but never gets a chance to run.
conn = sqlite3.connect("app.db", isolation_level=None)
conn.execute("PRAGMA journal_mode=WAL")
conn.execute("PRAGMA busy_timeout=5000")

conn.execute("BEGIN")                       # == BEGIN DEFERRED, no lock
qty = conn.execute(
    "SELECT qty FROM inventory WHERE id=?", (item_id,)
).fetchone()[0]                             # read snapshot taken here
if qty > 0:
    # If another writer grabbed the lock in between:
    # sqlite3.OperationalError: database is locked  <-- instantly
    conn.execute("UPDATE inventory SET qty=qty-1 WHERE id=?", (item_id,))
conn.execute("COMMIT")

# AFTER: declare write intent at BEGIN. Two changes:
#  1. BEGIN IMMEDIATE -> write lock acquired before the snapshot
#  2. busy_timeout now actually applies to that BEGIN
conn = sqlite3.connect("app.db", isolation_level=None)
conn.execute("PRAGMA journal_mode=WAL")
conn.execute("PRAGMA busy_timeout=5000")
conn.execute("PRAGMA synchronous=NORMAL")   # safe with WAL, much faster commits

conn.execute("BEGIN IMMEDIATE")             # waits up to 5s here if a writer is active
try:
    qty = conn.execute(
        "SELECT qty FROM inventory WHERE id=?", (item_id,)
    ).fetchone()[0]
    if qty > 0:
        conn.execute("UPDATE inventory SET qty=qty-1 WHERE id=?", (item_id,))
    conn.execute("COMMIT")
except Exception:
    conn.execute("ROLLBACK")
    raise

Equivalent in other drivers: better-sqlite3 → db.transaction(fn).immediate(); Rust rusqlite → conn.transaction_with_behavior(TransactionBehavior::Immediate); Go mattn/go-sqlite3 → _txlock=immediate in the DSN; Django → "init_command": "PRAGMA journal_mode=WAL; PRAGMA busy_timeout=5000;" plus transaction_mode="IMMEDIATE" (Django 5.1+).

When NOT to Use This

• Read-only transactions. BEGIN IMMEDIATE on a pure read serialises it behind every writer for no benefit. In WAL mode a deferred read never blocks and never fails — keep those deferred.
• Long transactions. BEGIN IMMEDIATE holds the single global write lock for the whole transaction. If your transaction does an HTTP call or a 30-second batch job in the middle, you've just built a global stall. Split it, or do the slow work before BEGIN.
• Genuinely high write concurrency. SQLite tops out around a few thousand small writes/sec on one writer. If BEGIN IMMEDIATE waits are your bottleneck rather than your bug, you want a server database (Postgres) or a single-writer queue in front of SQLite — not a bigger timeout.
• Multiple processes over NFS or a network share. SQLite's locking is unreliable there; no pragma fixes that.

Gotchas

• busy_timeout is per-connection, not per-database. Every connection your pool opens must set it, and every pragma must be re-issued on reconnect. A single connection missing the pragma produces intermittent, unreproducible failures.
• WAL mode is persistent, the others are not. journal_mode=WAL sticks in the file header; busy_timeout, synchronous, and foreign_keys reset on every new connection.
• ORMs default to DEFERRED. SQLAlchemy, Django (<5.1), Prisma, and better-sqlite3 all emit plain BEGIN unless told otherwise. Your carefully written service layer is still deferred underneath.
• A checkpoint is a writer too. WAL checkpointing takes the write lock. If you see busy errors clustering at regular intervals with no obvious traffic, that's wal_autocheckpoint firing. Consider PRAGMA wal_autocheckpoint=0 plus an explicit wal_checkpoint(TRUNCATE) on a schedule you control.
• Don't blanket-apply IMMEDIATE. Forcing every transaction to take the write lock converts a fully concurrent read workload into a serial one, and your p99 latency will tell you about it.
• Retries are still worth having, but only around the whole transaction. Retrying just the failing statement reuses the stale snapshot. Roll back, re-read, re-decide.

Key takeaway: If a transaction will ever write, open it with BEGIN IMMEDIATE — busy_timeout cannot save a deferred transaction that tries to upgrade from read to write.

// POST /orders handler
db.transaction(() => {          // better-sqlite3 default = DEFERRED
  const stock = db.prepare('SELECT qty FROM inventory WHERE id=?').get(id);
  if (stock.qty > 0) db.prepare('UPDATE inventory SET qty=qty-1 WHERE id=?').run(id);
})();

const place = db.transaction(/* same body */);
place.immediate(); // BEGIN IMMEDIATE -> takes write lock first, busy_timeout now applies