SQLite 'database is locked' Ignored My 30s busy_timeout: 311 Failures

My connection had timeout=30. The error log said sqlite3.OperationalError: database is locked. The timing column next to it said the failing statement ran for 1.3 milliseconds.

Not 30 seconds. Not even close. SQLite didn't wait at all. It looked at my request, decided waiting was pointless, and threw the error straight back.

This is the story of 311 "SQLite database is locked" errors in one batch run, why bumping the timeout to 120 seconds changed nothing, and the one keyword that took the count to zero.

database is locked instantly. busy_timeout does not apply.BEGIN, you hold a snapshot, and waiting can't make a stale snapshot fresh.BEGIN IMMEDIATE for any transaction that will write, and keep slow work (LLM calls, embeddings) outside the transaction. A batch pipeline that summarizes internal documents with Claude. 2,400 documents, 8 worker processes, one SQLite file as the job queue and result store. SQLite because it's one file, it's fast, and I didn't want to run Postgres for a weekend job.

I had Claude Code scaffold the worker. The flow per job:

Step 3 is where it went wrong. Here's the save function, more or less as written:

conn = sqlite3.connect("jobs.db", timeout=30, isolation_level=None)
conn.execute("PRAGMA journal_mode=WAL")

def save_result(conn, job_id, summary):
    conn.execute("BEGIN")
    (status,) = conn.execute(
        "SELECT status FROM jobs WHERE id = ?", (job_id,)
    ).fetchone()
    if status != "running":          # cancelled while Claude was thinking
        conn.execute("ROLLBACK")
        return
    rows = embed_chunks(job_id, summary)   # local model, ~120 ms
    conn.executemany(
        "INSERT INTO chunks (job_id, idx, text, vec) VALUES (?, ?, ?, ?)", rows
    )
    conn.execute("UPDATE jobs SET status = 'done' WHERE id = ?", (job_id,))
    conn.execute("COMMIT")

It reads like careful code. Check-then-write inside a transaction, so nobody can cancel the job between the check and the insert. The tests passed. The tests also ran with one worker.

311 of 2,400 jobs failed on the INSERT with database is locked. That's 13%.

The worse part: every one of those failures happened after the Claude call. My retry wrapper re-queued the job, the job ran again, and I paid for the summary twice. 311 thrown-away calls, roughly 1.1M input tokens of pure waste.

The first fix I tried, and the first one Claude Code suggested when I pasted the traceback, was a bigger timeout. So I measured it:

Flat. Noise. Then I varied worker count with the timeout fixed at 30s:

So the problem scaled with concurrency and ignored the timeout completely. That combination is the fingerprint of this bug.

Because the busy handler only runs when your connection doesn't hold a transaction yet. If you already read something inside BEGIN and then try to write, SQLite returns SQLITE_BUSY immediately instead of waiting.

Here's the chain:

DEFERRED) takes no lock. Nothing happens yet. SELECT starts a INSERT needs to SQLITE_BUSY_SNAPSHOT, and the message is the same old And why not wait? Because waiting can't fix it. The other writer's commit is already in the WAL. My snapshot is never going to catch up while my transaction is open. The only way out is to roll back and start over, so SQLite tells you immediately.

Even when the other writer is still holding the lock (not finished yet), SQLite won't invoke the busy handler for a connection that already has a read transaction open. Two connections that both hold read snapshots and both want to write could deadlock forever. Refusing fast is how SQLite avoids that.

Because the failure window was the time between my SELECT and my INSERT, and more workers means more commits landing inside that window.

Each worker commits about twice per job (claim and save), and a job took about 12 seconds on average. That's roughly 0.17 commits per second per worker. With 8 workers, the other 7 produce around 1.2 commits per second. The chance at least one lands inside a 120 ms window:

P(fail) ≈ 1 - e^(-rate × window)
        = 1 - e^(-1.17 × 0.12)
        ≈ 13%

The same formula predicts about 2% for 2 workers and 6% for 4. That matched my table closely enough that I stopped suspecting anything else.

On Python 3.11+, the exception carries the extended code, which made this easy to confirm:

except sqlite3.OperationalError as e:
    log.error("save failed", code=e.sqlite_errorname)

Of the 311, 268 were SQLITE_BUSY_SNAPSHOT (someone committed during my window) and 43 were plain SQLITE_BUSY (someone was mid-commit when I tried to upgrade). Not one of them ever waited.

Take the write lock at the start of the transaction with BEGIN IMMEDIATE, and move slow work out of the transaction.

BEGIN IMMEDIATE grabs the write lock before any read happens. At that moment the connection holds no transaction, so if another writer is busy, the busy handler kicks in and your 30 seconds of timeout actually get used. Once you have the lock, your reads are guaranteed current, because nobody else can commit until you do.

def save_result(conn, job_id, summary):
    rows = embed_chunks(job_id, summary)   # slow work first, no lock held

    conn.execute("BEGIN IMMEDIATE")        # waits here, honoring busy_timeout
    try:
        (status,) = conn.execute(
            "SELECT status FROM jobs WHERE id = ?", (job_id,)
        ).fetchone()
        if status != "running":
            conn.execute("ROLLBACK")
            return
        conn.executemany(
            "INSERT INTO chunks (job_id, idx, text, vec) VALUES (?, ?, ?, ?)", rows
        )
        conn.execute("UPDATE jobs SET status = 'done' WHERE id = ?", (job_id,))
        conn.execute("COMMIT")
    except Exception:
        conn.execute("ROLLBACK")
        raise

Two changes:

I did the same to the claim step, which I hadn't even noticed was the same shape (SELECT a pending job, then UPDATE it to running).

Yes. Three full runs of 2,400 jobs with 8 workers: 0 failures. The longest any worker waited on BEGIN IMMEDIATE was 410 ms, well inside the 30s timeout. Total wall time dropped slightly too, because no job ran twice.

Does BEGIN IMMEDIATE cost concurrency? Not really. SQLite allows exactly one writer at a time no matter what you do. BEGIN DEFERRED doesn't give you parallel writes; it gives you a later, more surprising failure. Readers in WAL mode are still never blocked by the writer.

isolation_level=None, set isolation_level="IMMEDIATE" so the implicit The AI part of this pipeline was fine. Claude summarized 2,400 documents without complaint. The bug was a textbook concurrency pattern that looks correct, passes single-process tests, and only shows its cost when it's burning paid API calls at 13% per job.

SQLite only honors busy_timeout when a connection is trying to start a transaction from nothing. If your code runs BEGIN, reads something, then writes, the write has to upgrade an existing read snapshot, and in WAL mode SQLite returns database is locked (SQLITE_BUSY or SQLITE_BUSY_SNAPSHOT) immediately rather than waiting, because another writer has already moved the database past your snapshot or could deadlock with you. Raising the timeout does nothing. Start write transactions with BEGIN IMMEDIATE, keep slow work outside them, and the busy handler will queue writers properly. In my 8-worker pipeline, that took failures from 311 of 2,400 to 0.

Written by the developer behind Preterview, an interview prep platform.