Tuning busy_timeout Against Application Retries

There are two places to absorb lock contention in a SQLite application, and most codebases accidentally use both at full strength. SQLite’s internal busy_timeout sleeps and re-attempts the lock for you; an application-level retry loop catches the eventual sqlite3.OperationalError and tries the whole statement again with its own back-off. Stack a 5000 ms timeout under a loop that retries five times and you have quietly built a worst-case wait of 25 seconds for a single row — a request that should have failed fast, or succeeded in under a second, instead pins a worker thread for half a minute. The question this page answers is where to draw the line: how much of the wait to delegate to the engine’s busy_timeout window, and how thin the residual retry above it should be. It sits inside the broader SQLite Architecture & Production Hardening discipline, and it assumes you have already decided to set a timeout — the mechanics of doing so, and sizing it for storage, are covered in Configuring busy_timeout for IoT Sensor Writes. Here the concern is the layering, not the value alone.

Diagnosis

Before tuning the split, confirm which error class you are actually retrying, because only one of them is a candidate for waiting at all. SQLITE_BUSY (code 5) means another process holds a lock the engine could not acquire within its wait budget; waiting longer can resolve it. SQLITE_LOCKED (code 6) means the conflict is inside the same database connection — a table locked by a still-open statement or cursor on this very handle — and no amount of busy_timeout or application retry will clear it, because the thing you are waiting for is you. The Python driver renders both as OperationalError; you have to read the code to tell them apart:

import sqlite3

try:
    conn.execute("INSERT INTO events(payload) VALUES (?);", (blob,))
except sqlite3.OperationalError as exc:
    code = getattr(exc, "sqlite_errorcode", None)   # populated on Python 3.11+
    if code == sqlite3.SQLITE_BUSY:
        ...   # transient cross-connection contention -> waiting/retry can help
    elif code == sqlite3.SQLITE_LOCKED:
        ...   # same-connection conflict -> finalize the open cursor; DO NOT retry blindly
    else:
        raise

If you are on an older interpreter without sqlite_errorcode, match on the message text (database is locked for busy, database table is locked for locked) as a fallback, but treat that as temporary — text is not a contract.

Next, measure how much of your latency is already the timeout doing its job versus the loop piling on top. Read back the configured window and time a contended statement:

window = conn.execute("PRAGMA busy_timeout;").fetchone()[0]   # ms the engine will wait internally
print(f"busy_timeout = {window} ms")

If busy_timeout reports, say, 5000 and your retry loop also sleeps and re-runs the statement several times, each retry re-enters that same 5000 ms internal wait. The tell is a p99 latency that is an integer multiple of the timeout: ~5 s, ~10 s, ~15 s. That multiplication — not the timeout itself — is the problem this page removes.

Figure — a bounded busy_timeout absorbs transient contention inside one wait; a thin idempotent retry with jitter handles only the residual that outlives the window, then hands off to a fallback rather than looping again.

Layering a bounded busy_timeout under a thin application retry A left-to-right flow. A write attempt enters SQLite's busy_timeout, a bounded internal wait capped near 2000 milliseconds. If the lock is acquired the statement commits. If the wait is exhausted the engine returns a residual SQLITE_BUSY, which a thin application retry catches and re-attempts two or three times with jittered back-off. On success it commits; once those few retries are exhausted it routes the payload to a durable fallback buffer instead of looping indefinitely. write attempt BEGIN IMMEDIATE busy_timeout bounded wait ≤ 2000 ms residual SQLITE_BUSY thin retry idempotent + jitter ×3 exhausted commit lock acquired commit fallback buffer success retries spent

Solution

Delegate the bulk of the wait to busy_timeout — it is cheaper than an application loop because it never unwinds the C stack, never re-parses the statement, and sleeps on an escalating schedule already tuned to lock churn. Keep the value bounded to a fraction of your request deadline (a 2000 ms window is a reasonable default when the request budget is a few seconds). Then put a thin retry above it — two or three attempts — that exists only for the residual SQLITE_BUSY the window could not outlast, and gate that retry on idempotency so a replay can never double-apply a write.

import sqlite3
import logging
import random
import time

logger = logging.getLogger("busy_retry")

def open_writer(db_path: str, timeout_ms: int = 2000) -> sqlite3.Connection:
    # isolation_level=None -> autocommit, so the PRAGMA runs now, not inside an implicit BEGIN.
    conn = sqlite3.connect(db_path, isolation_level=None)
    conn.execute(f"PRAGMA busy_timeout = {timeout_ms};")  # engine waits up to 2000 ms per attempt
    applied = conn.execute("PRAGMA busy_timeout;").fetchone()[0]  # read BACK from SQLite, not the arg
    if applied != timeout_ms:
        conn.close()
        raise RuntimeError(f"busy_timeout not applied: wanted {timeout_ms}, got {applied}")
    return conn

def write_with_residual_retry(conn, sql, params, *, attempts=3, base=0.05, cap=0.4):
    """Thin retry ABOVE busy_timeout. Only for SQLITE_BUSY; the SQL must be idempotent."""
    for n in range(1, attempts + 1):
        try:
            conn.execute("BEGIN IMMEDIATE;")   # take the write lock up front, not at first write
            conn.execute(sql, params)
            conn.execute("COMMIT;")
            return
        except sqlite3.OperationalError as exc:
            conn.execute("ROLLBACK;")
            if getattr(exc, "sqlite_errorcode", None) != sqlite3.SQLITE_BUSY or n == attempts:
                raise                          # SQLITE_LOCKED or budget spent -> do not keep waiting
            # Full jitter: sleep in [0, min(cap, base * 2**n)). Decorrelates competing writers so
            # they do not re-collide in lockstep after each busy_timeout window expires.
            time.sleep(random.uniform(0, min(cap, base * (2 ** n))))
            logger.warning("residual SQLITE_BUSY, retry %d/%d", n, attempts)

Two design choices carry the weight. The retry sleeps are tens of milliseconds, not seconds — the seconds already live in busy_timeout, so the loop adds only decorrelating jitter, never another full wait. And every statement flows through BEGIN IMMEDIATE, which acquires the write lock at transaction start; that both makes the busy condition surface at a single predictable point and makes a rolled-back attempt safe to replay, because nothing was half-committed. The INSERT ... ON CONFLICT DO NOTHING shape, or a write keyed by a unique client-supplied id, is what makes the “idempotent” precondition true rather than aspirational.

Verification

First, prove the layering multiplies latency the way you intended — bounded, not stacked. Hold a lock for a known interval and time a contended write through the wrapper:

import threading, time, sqlite3

def hold(path, seconds):
    c = sqlite3.connect(path, isolation_level=None)
    c.execute("BEGIN IMMEDIATE;")
    time.sleep(seconds)
    c.execute("COMMIT;")

threading.Thread(target=hold, args=(db_path, 1.2), daemon=True).start()
time.sleep(0.05)

w = open_writer(db_path, timeout_ms=2000)
t0 = time.monotonic()
write_with_residual_retry(w, "INSERT OR IGNORE INTO events(id, payload) VALUES (?, ?);", (42, b"x"))
elapsed = (time.monotonic() - t0) * 1000
assert elapsed < 2000, f"waited {elapsed:.0f} ms — the busy_timeout window alone should absorb this"
print(f"committed after {elapsed:.0f} ms with no residual retry")

Because the 1200 ms lock-hold fits inside the 2000 ms window, the engine’s internal wait alone covers it — the application loop never fires, and the elapsed time is roughly the lock-hold, not a multiple of it. That single-window behaviour is the fix working.

Second, assert idempotency directly: run the wrapper twice with the same key and confirm the row count did not double.

before = w.execute("SELECT count(*) FROM events WHERE id = 42;").fetchone()[0]
write_with_residual_retry(w, "INSERT OR IGNORE INTO events(id, payload) VALUES (?, ?);", (42, b"x"))
after = w.execute("SELECT count(*) FROM events WHERE id = 42;").fetchone()[0]
assert after == before, "replay inserted a duplicate — the statement is not idempotent"

If that assertion ever trips, the retry loop is unsafe as written and must be disabled for that statement until the write is made idempotent.

Failure Modes & Gotchas

Double-waiting: the timeout and the loop each pay the full window. The most common regression is a retry loop wrapped around a statement whose connection also carries a large busy_timeout. Each loop iteration re-enters the engine’s full internal wait before the exception even reaches your except, so N retries cost roughly N × busy_timeout in the worst case. Keep exactly one layer long: a bounded busy_timeout that owns the seconds, and a loop whose sleeps are jittered milliseconds. If you genuinely want the loop to own the waiting instead, set busy_timeout = 0 so the engine fails fast and the loop is the only waiter — but never let both wait at full strength.

Retrying a non-idempotent write applies it twice. A residual SQLITE_BUSY can be returned after the write was durably committed but before the success acknowledgement propagated back — rare, but real under adversarial timing. A blind retry of a plain INSERT then creates a duplicate; a retry of UPDATE balance = balance - 10 double-charges. Gate every retried statement behind a uniqueness constraint, an upsert (ON CONFLICT), or a client-supplied idempotency key, and roll back before each attempt. Writes that cannot be made idempotent must not be retried automatically — route their tail to a durable buffer through your fallback routing strategy instead of replaying them.

A busy_handler silently overrides busy_timeout, and deferred transactions defeat both. Installing a custom busy handler (sqlite3_busy_handler(), exposed in some wrappers) replaces the timeout’s built-in handler — the last one registered wins — so a library that sets its own handler can make your carefully tuned PRAGMA busy_timeout a no-op. Read the value back after the full stack initializes, not just after your own call. Separately, a default deferred transaction takes the write lock only at the first write statement, so contention can surface mid-transaction where a rollback is costlier; BEGIN IMMEDIATE moves that failure to a clean boundary. This is the same recycled- and misconfigured-handle territory covered in reducing lock contention in multi-threaded apps; the authoritative meanings of codes 5 and 6 are in the SQLite result and error codes reference.