SQLITE_BUSY, busy_timeout, Retries, and Short Transactions

Learning outcomes

A lock conflict is useful information: another connection currently prevents this operation from proceeding. Reliable applications turn that fact into a bounded policy. They do not retry forever, and they do not use a 60-second timeout to hide transactions that should last 20 milliseconds. This lesson separates SQLITE_BUSY from SQLITE_LOCKED, configures connection-level waiting, and measures how transaction duration changes the result.

SQLITE_BUSY and SQLITE_LOCKED are not synonyms

Many drivers render both as some form of “locked,” so applications should preserve SQLite’s result code when possible. The distinction guides diagnosis.

A classic BUSY case is connection A holding a write transaction while connection B attempts another write. A classic LOCKED-style case is trying to change schema/table state on a connection while another active statement on that same connection prevents it. Shared cache adds specialized locking cases and is not a recommended default concurrency strategy for this course.

Busy timeout: bounded waiting on one connection

SQLite provides a busy handler mechanism. sqlite3_busy_timeout() installs a handler that sleeps/retries until the configured accumulated waiting time is reached; after that, the blocked operation returns SQLITE_BUSY. A connection can have only one busy handler, so replacing the handler/timeout replaces the previous busy policy.

-- sqlite3 shell command: milliseconds.timeout 750-- SQL/driver-accessible PRAGMA: millisecondsPRAGMA busy_timeout = 750;PRAGMA busy_timeout;

The shell’s .timeout and PRAGMA busy_timeout are convenient teaching tools. Production drivers often expose their own connection option or API; use that when available so configuration is explicit in connection initialization.

A timeout buys time; it does not create write concurrency

If A holds the writer for 2 seconds and B is willing to wait 500 ms, B still fails. If A finishes in 50 ms, the same 500 ms budget may be enough. The important variable is often transaction hold time, not the largest timeout you can tolerate.

bad shape
A: BEGIN -------- HTTP call -------- user wait -------- UPDATE -------- COMMIT
B:          |--------------------- BUSY wait ---------------------X

better shape
A: HTTP call ---- prepare values ---- BEGIN -- UPDATE -- COMMIT
B:                                     waits briefly -> succeeds

Measure a conflict with two writers

This Python lab uses two independent connections to one disposable file. The first transaction deliberately holds the writer. The second has a 300 ms timeout, so its failure duration should be roughly bounded by that policy rather than hanging indefinitely.

import sqlite3, timefrom pathlib import Pathpath = Path("busy-lab.db")path.unlink(missing_ok=True)setup = sqlite3.connect(path)setup.execute("CREATE TABLE counter(id INTEGER PRIMARY KEY, value INTEGER NOT NULL)")setup.execute("INSERT INTO counter VALUES(1,0)")setup.commit()setup.close()A = sqlite3.connect(path, timeout=0, isolation_level=None)B = sqlite3.connect(path, timeout=0.300, isolation_level=None)A.execute("BEGIN IMMEDIATE")A.execute("UPDATE counter SET value=value+1 WHERE id=1")t0 = time.monotonic()try:    B.execute("BEGIN IMMEDIATE")except sqlite3.OperationalError as exc:    elapsed = time.monotonic() - t0    print(type(exc).__name__, round(elapsed, 3), exc)    # expected: a lock/busy error after roughly the configured wait budgetfinally:    A.rollback()    A.close(); B.close()

Exact timing varies with scheduler and platform. The meaningful observation is bounded behavior: B does not silently wait forever.

Shorten the transaction and watch the outcome change

Now use two threads solely as a test harness. Writer A holds its transaction for 100 ms; B allows 750 ms. Because A releases the writer before B’s budget expires, B can acquire the writer and commit.

import sqlite3, threading, timepath = "busy-lab.db"def writer_a():    con = sqlite3.connect(path, timeout=0, isolation_level=None)    con.execute("BEGIN IMMEDIATE")    con.execute("UPDATE counter SET value=value+1 WHERE id=1")    time.sleep(0.10)          # deliberate test hold    con.execute("COMMIT")    con.close()def writer_b():    con = sqlite3.connect(path, timeout=0.75, isolation_level=None)    t0 = time.monotonic()    con.execute("BEGIN IMMEDIATE")    waited = time.monotonic() - t0    con.execute("UPDATE counter SET value=value+1 WHERE id=1")    con.execute("COMMIT")    print("B waited", round(waited, 3), "seconds")    con.close()a = threading.Thread(target=writer_a)a.start(); time.sleep(0.02)b = threading.Thread(target=writer_b)b.start(); a.join(); b.join()check = sqlite3.connect(path)print(check.execute("SELECT value FROM counter WHERE id=1").fetchone()[0])# expected after the two successful increments: 2check.close()

BEGIN IMMEDIATE can make a busy failure easier to reason about

SQLite documents that SQLITE_BUSY can arise when a transaction starts, during writes, or at commit. If BEGIN IMMEDIATE succeeds, the connection has already established the write transaction, which moves ordinary competing-writer failure toward the start of the unit. This often simplifies application error handling compared with doing several reads and then discovering at the first write that another writer won.

Retries need a logical contract

A retry is safe only when repeating the operation cannot create a second business effect. Chapter 8 introduced stable request IDs. Bring that design into busy handling.

A bounded retry skeleton

Prefer a driver’s native busy timeout first. If the application adds higher-level retries, bound both attempts and total elapsed time, reuse the same request identity, and include jitter/backoff so many clients do not wake in lockstep.

deadline = monotonic_now() + 2.0attempt = 0while monotonic_now() < deadline and attempt < 4:    attempt += 1    try:        execute_same_idempotent_request(request_id)        return success    except SQLITE_BUSY:        sleep_with_bounded_backoff_and_jitter(attempt)return retryable_failure_to_caller

Do not copy this pseudocode without mapping your driver’s exceptions and transaction cleanup. A failed attempt must leave its connection in a known state before reuse.

When a larger timeout makes the system worse

If the real cause is a transaction that waits for user input or performs a slow network call, a long timeout can convert an immediate, diagnosable failure into a queue of blocked workers. That increases latency, memory/socket occupancy, and the chance of request pileups. Fix transaction scope first; then choose a timeout that matches the workload’s latency budget.

Two-writer lab checklist

1. Start with busy_timeout=0 and reproduce the collision.
2. Measure a finite timeout and record elapsed wait.
3. Reduce A’s transaction hold time; do not merely increase B’s timeout.
4. Repeat with BEGIN IMMEDIATE at the application boundary.
5. Log the resolved DB path, connection ID, transaction ID/request ID, BEGIN time, COMMIT/ROLLBACK time, and SQLite result code.
6. Close both connections and delete only the disposable test database.

Production judgment and bridge

Busy handling is not a throughput feature; it is a contention policy. Keep writes short, make retries bounded and idempotent, and retain actual result codes. Lesson 3 changes the journal architecture to WAL so readers can keep stable snapshots while a writer commits—but the single-writer rule remains.

Authoritative references

• SQLite — Result and Error Codes.
• SQLite C API — Set A Busy Timeout.
• SQLite — PRAGMA busy_timeout.
• SQLite — Transaction.