Field Notes From 48 Hours of SQLITE_BUSY: The Deferred Transaction Was the Lock Fight

Two writers, one SQLite file, and an error that only appeared when a long read overlapped a short write. That is the entire shape of this bug, and it took me far longer to see than I want to admit. The message was always the same: sqlite3.OperationalError: database is locked, raised from a code path whose connections had a five-second busy_timeout configured. Why would a five-second timeout not buy me five seconds of patience?

These are field notes from a 48-hour window, rewritten into the four-hour version I would follow next time. I am not attaching production numbers to this story, because I do not have metrics I can honestly publish. What I can hand you is a two-process reproduction, the reason the busy handler stayed silent, and the transaction shape that survived review.

What the symptom actually looked like

• Reads were fine, and single-threaded writes were fine, so most of day one went to the wrong suspect list.
• Failures clustered around nightly jobs that read a lot of rows before writing one summary row.
• busy_timeout was set to 5000 ms on every connection, yet the error arrived in well under a millisecond.
• Restarting the process cleared it for hours, which is exactly how a stale read snapshot behaves from the outside.

That last bullet is the tell, and I walked past it twice before writing it down on paper.

Four things I would not spend hours on again

1. Raising busy_timeout from 5 s to 30 s. Nothing changed, because the busy handler was never invoked on this path.
2. Adding a retry loop around the failing UPDATE. Retrying inside a still-open transaction cannot succeed; you have to roll back first.
3. Blaming the bind mount and the filesystem under it. WAL is genuinely a bad idea on network filesystems, but this was a local volume.
4. Blaming the ORM connection pool. It was innocent and faithfully emitting the default BEGIN that I never asked about.

None of those ideas are stupid in isolation; they are just expensive before you have a twenty-line reproduction in front of you.

The reproduction I would write on hour one

Two files, one shared database, and one deliberate delay. The reader opens a transaction, reads a row, and then thinks for a while before writing. The writer squeezes in a committed write during that pause.

# repro_reader.py
import sqlite3, time

con = sqlite3.connect('repro.db', isolation_level=None, timeout=5.0)
con.execute('PRAGMA journal_mode=WAL')
con.execute('PRAGMA busy_timeout=5000')

con.execute('BEGIN')  # DEFERRED: no write lock is taken yet
row = con.execute('SELECT v FROM counters WHERE id = 1').fetchone()
print(f'[reader] read v={row[0]}', flush=True)
time.sleep(3)  # "reporting work" while holding a read snapshot

try:
    con.execute('UPDATE counters SET v = v + 1 WHERE id = 1')
    con.execute('COMMIT')
    print('[reader] write committed', flush=True)
except sqlite3.OperationalError as exc:
    print(f'[reader] FAILED: {exc}', flush=True)

# repro_writer.py
import sqlite3, time

time.sleep(1)  # land inside the reader's pause
con = sqlite3.connect('repro.db', isolation_level=None, timeout=5.0)
con.execute('PRAGMA journal_mode=WAL')
con.execute('PRAGMA busy_timeout=5000')

con.execute('BEGIN IMMEDIATE')
con.execute('UPDATE counters SET v = v + 1 WHERE id = 1')
con.execute('COMMIT')
print('[writer] write committed', flush=True)

Seed the table once, then run the two processes side by side:

sqlite3 repro.db "PRAGMA journal_mode=WAL;
  CREATE TABLE IF NOT EXISTS counters(id INTEGER PRIMARY KEY, v INTEGER);
  INSERT OR IGNORE INTO counters(id, v) VALUES (1, 0);"
python repro_reader.py & python repro_writer.py; wait

The third line of output is the whole bug, and it arrives instantly:

[reader] read v=0
[writer] write committed
[reader] FAILED: database is locked

Why a five-second timeout did not help

The reader's BEGIN is deferred, so it acquires only a read snapshot and takes no write lock at that moment. In WAL mode a writer can commit while that snapshot lives, which is why the writer succeeded so cheerfully. The reader's UPDATE then requires an upgrade from snapshot to write lock, and that upgrade can only succeed if nobody has written since the snapshot began.

Here is the part that cost me a day: for that specific upgrade, SQLite may return SQLITE_BUSY immediately instead of invoking the busy handler. The busy handler exists to wait out a competing lock; in this case waiting would not help, because the reader's snapshot is already stale and waiting will never refresh it. So busy_timeout=5000 sits there looking responsible while the failure returns in microseconds.

Diagnostics that finally made this visible in the process list:

sqlite3 repro.db "PRAGMA journal_mode; PRAGMA busy_timeout;"
ls -l repro.db-wal repro.db-shm 2>/dev/null
lsof repro.db 2>/dev/null || fuser -v repro.db

If you see -wal and -shm files and a second process holding the same inode, you already know more than I did at hour twenty.

The fix that held up: take the write lock when you intend to write

Change one word in the reader and the failure mode changes shape. BEGIN IMMEDIATE asks for the write lock up front, before the slow read, so busy_timeout now applies to a real lock wait instead of a doomed snapshot upgrade.

con.execute('BEGIN IMMEDIATE')  # write intent declared before the slow read

Three rules came out of this, and I would apply all of them before touching a retry loop:

1. Use BEGIN IMMEDIATE for any transaction that will write, even if the write comes last.
2. Keep transactions short: fetch what you need, commit, then do the slow non-database work.
3. Put retry logic outside the transaction, after a ROLLBACK, and only for genuine SQLITE_BUSY waits.

Worth knowing: Python's sqlite3 legacy transaction handling is implicit, and isolation_level=None (as used above) simply disables that so you control BEGIN yourself; Python 3.12 also added an autocommit attribute for the same purpose. I would check your ORM's documentation for a documented way to emit BEGIN IMMEDIATE, because plenty of ORMs emit a plain deferred BEGIN and leave you to discover this later.

A test plan that fails on the old code

A regression test here does not need a benchmark, only two processes and a positive assertion about which error you get.

# test_lock_shape.py
import subprocess, sys, sqlite3

def seed(path):
    con = sqlite3.connect(path)
    con.execute('PRAGMA journal_mode=WAL')
    con.execute('CREATE TABLE IF NOT EXISTS counters(id INTEGER PRIMARY KEY, v INTEGER)')
    con.execute('INSERT OR IGNORE INTO counters(id, v) VALUES (1, 0)')
    con.commit()
    con.close()

def run_pair(tmp_path, reader, writer):
    db = tmp_path / 'repro.db'
    seed(db)
    p1 = subprocess.Popen([sys.executable, reader, str(db)], stdout=subprocess.PIPE, text=True)
    p2 = subprocess.Popen([sys.executable, writer, str(db)], stdout=subprocess.PIPE, text=True)
    return p1.communicate()[0] + p2.communicate()[0]

def test_deferred_upgrade_loses(tmp_path):
    out = run_pair(tmp_path, 'repro_reader.py', 'repro_writer.py')
    assert 'database is locked' in out  # documents the trap, do not ship blindly

def test_immediate_upgrade_waits(tmp_path):
    out = run_pair(tmp_path, 'repro_reader_immediate.py', 'repro_writer.py')
    assert 'database is locked' not in out
    assert out.count('committed') == 2

One honest caveat about that first test: it asserts the broken behaviour, so it belongs next to a comment explaining why it exists. Keep it as documentation of the trap, not as a contract you want to hold forever.

Where MonkeyCode actually fit into this workflow

I used two things here, and I want to be precise about what each one did and did not do. First, the free model access was useful as a review partner for the reproduction: I described the ordering of BEGIN, SELECT, sleep, and UPDATE, and asked what the busy handler was doing. Its answers were plausible and incomplete, which is normal, so every claim in this article that matters is grounded in SQLite's own documented behaviour rather than in a model's summary.

Second, the free server option is a straightforward way to get a second host for the two-process harness. Running the writer on another host against a copied database file is a cheap way to ask whether a failure is about lock semantics or about the local filesystem, and having a free place to park that second process removed the "I will set it up later" excuse.

Disclosure: This article was prepared as part of MonkeyCode's product outreach.

If you want to run the two-process harness without provisioning anything first, the free server option is one reasonable place to start; just do not point WAL at a network mount while you are there.

Limitations, and who should not use this approach

• SQLite is single-writer by design. If your workload has genuinely concurrent writers, BEGIN IMMEDIATE just converts fast failures into polite queueing, and a client-server database is the real answer.
• Do not run WAL over NFS or SMB. It depends on shared memory between processes, and network filesystems break that assumption.
• This is about lock semantics, not performance. I have no throughput numbers to offer, and I would not trust anyone who quotes you one from a twenty-line script.
• Skip this if you already run a managed database. The lesson is about embedded concurrency, not about migrating away from a server you already operate.
• Do not copy the failure-asserting test into a suite that blocks deploys; keep it annotated or delete it once the fix lands.

What I would repeat, and what I would drop

Repeat the twenty-line reproduction, the lsof check, and the habit of writing down the exact timing of the failure relative to the other process. Drop the reflex to raise busy_timeout first, and drop any retry loop that wraps a transaction instead of following a rollback.

So which is cheaper in your codebase: an hour spent writing a two-process reproduction, or two days spent blaming a filesystem that was never the problem? And when your own database is locked appears at 2 a.m., will you already know whether your transaction was deferred or immediate?