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.
TL;DR
- In WAL mode, a transaction that starts as a read and then tries to write can get
database is lockedinstantly.busy_timeoutdoes not apply. - SQLite only calls the busy handler when the connection holds no transaction yet. Once you have read inside
BEGIN, you hold a snapshot, and waiting can't make a stale snapshot fresh. - My failure rate scaled with worker count (2%, 6%, 13% for 2, 4, 8 workers) and stayed flat across timeouts of 5s, 30s and 120s.
- Fix: use
BEGIN IMMEDIATEfor any transaction that will write, and keep slow work (LLM calls, embeddings) outside the transaction. - Result: 0 failures across 3 runs of 2,400 jobs.
What I was building
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:
- Claim a pending job (short transaction).
- Call Claude to summarize the document. 5 to 40 seconds.
- Save the result: check the job wasn't cancelled, split the summary into chunks, embed them with a small local model, insert the chunks, mark the job done.
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.
What happened on the first real run?
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:
| busy timeout | failures (of 2,400) |
|---|---|
| 5 s | 318 |
| 30 s | 311 |
| 120 s | 306 |
Flat. Noise. Then I varied worker count with the timeout fixed at 30s:
| workers | failures (of 2,400) |
|---|---|
| 1 | 0 |
| 2 | 47 |
| 4 | 136 |
| 8 | 311 |
So the problem scaled with concurrency and ignored the timeout completely. That combination is the fingerprint of this bug.
Why does SQLite say "database is locked" when busy_timeout is set?
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:
-
BEGIN(plain, which meansDEFERRED) takes no lock. Nothing happens yet. - The
SELECTstarts a read transaction. In WAL mode, that pins a snapshot of the database as of that moment. - 120 ms of embedding later, the
INSERTneeds to upgrade that read transaction to a write transaction. - If any other worker committed during those 120 ms, my snapshot is stale. Letting me write on top of a stale snapshot could silently overwrite their changes. SQLite refuses. The extended code is
SQLITE_BUSY_SNAPSHOT, and the message is the same olddatabase is locked.
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.
Why did more workers mean more failures?
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.
How do you fix "database is locked" in SQLite WAL mode?
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:
-
BEGIN IMMEDIATEinstead ofBEGIN. - Embedding moved above the transaction. The write lock is now held for about 3 ms instead of 120+. If the job got cancelled in the meantime, I wasted 120 ms of local CPU, not a paid Claude call.
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).
Did the fix hold up?
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.
The checklist I use now
- Any transaction that might write starts with
BEGIN IMMEDIATE. No exceptions for "it's just a quick check first." - Never hold a transaction open across a network call, an LLM call or a model inference. Do the slow thing first, then lock, re-check, write, commit.
- If you use Python's implicit transactions instead of
isolation_level=None, setisolation_level="IMMEDIATE"so the implicitBEGINtakes the write lock. - If you see
database is lockederrors that took milliseconds while your timeout is seconds, stop tuning the timeout. You have a read-then-write upgrade somewhere. - Test with the real worker count. One worker can't reproduce any of this.
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.
So why does SQLite "database is locked" ignore busy_timeout?
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.
Top comments (1)
The flat curve across 5s, 30s and 120s timeouts is the diagnostic that gives it away, and a good one to keep. Once BEGIN IMMEDIATE is in, one thing to watch: the job-queue claim step. If claims also use BEGIN IMMEDIATE, 8 workers serialize on the write lock, so keep the claim to a single UPDATE ... WHERE status='pending' ... RETURNING and nothing else inside it. Also worth a small check that the cancel path still works: a job cancelled while Claude is thinking should leave no chunks behind, so a test that cancels mid-run and asserts zero rows in chunks and a non-done status would catch a regression of the status check you moved.