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

A developer running a 2,400-document Claude summarization pipeline across 8 worker processes hit 311 "database is locked" failures, about 13% of jobs, despite a 30-second busy_timeout. Testing showed the failure count was flat across 5s, 30s and 120s timeouts but scaled with worker count (0 at one worker, 311 at eight), because SQLite returns SQLITE_BUSY_SNAPSHOT immediately when a connection already holds a read transaction inside a deferred BEGIN. Switching to BEGIN IMMEDIATE for write transactions and keeping slow work like LLM calls and embeddings outside the transaction eliminated the errors.

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: | 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. 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. python 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 https://preterview.com/en , an interview prep platform.