SQLite 'database is locked' in 2ms: Why busy_timeout Didn't Help

My overnight Claude batch threw sqlite3.OperationalError: database is locked 187 times. The connection had timeout=30. The errors arrived in about 2 milliseconds.

That gap is the whole story. If SQLite had waited 30 seconds and then given up, I'd have a contention problem. It gave up in 2ms. That meant it never waited at all, and no timeout value was ever going to fix it. This post covers why SQLite "database is locked" errors can skip busy_timeout entirely, the first fix I shipped that made things worse, and the one-statement version I run now.

TL;DR

• In WAL mode, a deferred transaction that reads first and writes second can fail with SQLITE_BUSY_SNAPSHOT. SQLite returns it immediately, without calling the busy handler.
• Raising timeout from 5 to 30 to 60 seconds changed nothing: 187, 179 and 191 errors per 2,400 jobs.
• BEGIN IMMEDIATE takes the write lock up front, so the wait happens where busy_timeout applies. Errors dropped to 0.
• Python's isolation_level="IMMEDIATE" is a trap if your transaction starts with a SELECT: the read runs outside the transaction. I got 0 errors and 26 jobs processed twice.
• Best fix: claim the job in one statement with UPDATE ... RETURNING. 0 errors, 0 duplicates, p99 claim time of 11ms.

The setup: a SQLite job queue for Claude calls

I run a small batch pipeline that summarizes support tickets with the Claude API. Nothing fancy. A jobs table in SQLite, six worker processes, WAL mode on, one supervisor that restarts any worker that crashes.

Each worker claimed a job like this:

conn = sqlite3.connect("jobs.db", timeout=30, isolation_level=None) conn.execute("PRAGMA journal_mode=WAL") def claim(conn, worker_id): conn.execute("BEGIN") row = conn.execute( "SELECT id, payload FROM jobs WHERE status='pending' ORDER BY id LIMIT 1" ).fetchone() if row is None: conn.execute("COMMIT") return None conn.execute( "UPDATE jobs SET status='running', worker=? WHERE id=?", (worker_id, row[0]), ) conn.execute("COMMIT") return row 

Looks textbook. Transaction, read, write, commit. A run is 2,400 jobs. The Claude call takes a few seconds per job, so the claim step should be a rounding error.

It wasn't. 187 of 2,400 claims blew up with "database is locked." Every error crashed a worker, and every restart cost about 6 seconds of boot time. That's roughly 19 minutes of dead worker time per run, spent re-importing libraries.

Why does SQLite say "database is locked" when busy_timeout is set?

Because the error isn't always about waiting for a lock. In WAL mode, a deferred BEGIN doesn't lock anything. Your first SELECT pins a read snapshot. When you then try to UPDATE, SQLite has to upgrade you to a writer, and if another connection committed a write after your snapshot was taken, your snapshot is stale.

Waiting can't fix a stale snapshot. The data you read is already out of date, and letting you write on top of it would break isolation. So SQLite returns SQLITE_BUSY_SNAPSHOT immediately and skips the busy handler. Python surfaces that as the same generic message, database is locked.

Here's what happened between two of my workers:

1. Worker A: BEGIN, SELECT job 812. Snapshot pinned.
2. Worker B: BEGIN, SELECT job 812. Snapshot pinned.
3. Worker A: UPDATE job 812, COMMIT. Database moves forward.
4. Worker B: UPDATE job 812. Its snapshot predates A's commit. Instant SQLITE_BUSY_SNAPSHOT.

Six workers polling the same "first pending row" is the ideal recipe for step 4.

How do you confirm it's SQLITE_BUSY_SNAPSHOT and not real lock contention?

Check the error code and the timing. Since Python 3.11, sqlite3.OperationalError carries sqlite_errorname, which tells you the extended code:

import time t0 = time.perf_counter() try: claim(conn, worker_id) except sqlite3.OperationalError as e: ms = (time.perf_counter() - t0) * 1000 log.error("claim failed %s after %.1fms", e.sqlite_errorname, ms) raise 

Every one of my failures logged SQLITE_BUSY_SNAPSHOT, with a median of 2ms. Real contention looks different: SQLITE_BUSY, and a delay close to your timeout.

I still didn't believe it, so I ran the experiment anyway. Same 2,400 jobs, same six workers, three timeout values:

The error count moved by run-to-run noise. The timeout was decorative.

Does BEGIN IMMEDIATE fix "database is locked" in SQLite?

Yes, for this pattern. BEGIN IMMEDIATE grabs the write lock at the start of the transaction, before you read anything. If another writer holds it, you wait right there, and that wait is exactly what busy_timeout controls. Once you have the lock, nobody can commit behind your back, so your snapshot can't go stale.

def claim(conn, worker_id): conn.execute("BEGIN IMMEDIATE") # ...same SELECT + UPDATE + COMMIT 

Result: 0 errors across 2,400 jobs. p99 claim time was 41ms, because workers now queue on the write lock instead of racing. For a job that then spends seconds waiting on Claude, 41ms is free.

The fix I shipped first, and why it doubled my Claude bill for 26 jobs

Before the one-line BEGIN IMMEDIATE change, I tried to be clever. Python's sqlite3 accepts isolation_level="IMMEDIATE", so I dropped my manual BEGIN and let the driver handle it:

conn = sqlite3.connect("jobs.db", timeout=30, isolation_level="IMMEDIATE") 

Errors went to 0. I celebrated for one morning.

Then the dashboard showed 2,426 summaries for 2,400 tickets.

In legacy transaction mode, Python only issues the implicit BEGIN IMMEDIATE right before a data-modifying statement: INSERT, UPDATE, DELETE or REPLACE. My SELECT ran first, in autocommit, outside any transaction. Two workers could read the same pending job, then each open their own immediate transaction and run their UPDATE one after the other. No error, because nothing was stale anymore. Just two workers holding job 812, two Claude calls, two summary rows.

I had swapped a loud failure for a silent one. The 187 crashes cost restart time. The 26 duplicates cost API calls and a double-counted report that someone else noticed before I did.

The fix for that version is a guard plus a rowcount check: WHERE id=? AND status='pending', and skip the job if cursor.rowcount == 0. But at that point I was writing a lot of code to protect a two-statement claim. So I made it one statement.

What's the safest way to claim a job from a SQLite queue?

Do the select and the update in a single UPDATE ... RETURNING statement (SQLite 3.35+). A single write statement takes the write lock before it reads, so there's no read-then-upgrade step to go stale, and no window where two workers see the same row.

def claim(conn, worker_id): return conn.execute( """ UPDATE jobs SET status = 'running', worker = ? WHERE id = ( SELECT id FROM jobs WHERE status = 'pending' ORDER BY id LIMIT 1 ) RETURNING id, payload """, (worker_id,), ).fetchone() 

With isolation_level=None this runs in autocommit, which is what you want: the statement is its own transaction.

Final numbers over the same 2,400 jobs:

Caveats

• This is my workload: six processes, one SQLite file on a local SSD, a write every few seconds per worker. Your error rate will depend on how many workers poll the same rows at the same moment.
• BEGIN IMMEDIATE serializes writers. That's fine for a job queue. It's not fine if your transactions hold the lock while doing slow work. Never keep a transaction open across a Claude API call. Claim, commit, call, then write the result in a second short transaction.
• WAL on a network filesystem is its own problem. Don't.
• If you need dozens of writers hammering the same table, you've outgrown SQLite as a queue. Six workers and a few thousand jobs a night is well inside what it handles.

The rule I follow now

Any SQLite transaction that will write starts with BEGIN IMMEDIATE, or it's a single statement. A deferred transaction that reads and then writes is a race, whether it shows up as a crash or as a silent duplicate.

So why didn't busy_timeout help?

SQLite "database is locked" errors in WAL mode come in two kinds. SQLITE_BUSY means another connection holds a lock, and busy_timeout makes you wait for it. SQLITE_BUSY_SNAPSHOT means your deferred transaction read data, someone else committed, and you then tried to write on a stale snapshot. Waiting can't fix that, so SQLite fails instantly and busy_timeout never runs. If your "database is locked" errors arrive in milliseconds despite a long timeout, you have the second kind. Fix it by starting write transactions with BEGIN IMMEDIATE, or better, by turning read-then-write into one UPDATE ... RETURNING statement. And don't rely on Python's isolation_level="IMMEDIATE" if your transaction begins with a SELECT, because that read runs outside the transaction.

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