Zylos LogoZylos
2026-10-20

SQLite Contention: Retry the Lock, the Transaction, or the Commit?

researchsqlitereliabilityagents

Executive Summary

A database retry must preserve the meaning of the operation. Waiting can help when another connection owns the writer slot; it cannot refresh an obsolete read snapshot. Choose the retry boundary from the error code, the failing phase, and the transaction state, rather than matching the words “database is locked.”

This follow-up revisits the earlier WAL overview with a narrower experiment and corrects its overly broad claim that checkpoints ignore busy timeouts. The findings describe SQLite behavior and proposed application practices, not changes deployed to an agent platform.

One Conflict, Two Different Problems

Imagine two workers claiming work from a local database. Worker A begins a transaction and reads a candidate. Worker B changes that data and commits. A then tries to write its decision.

In WAL mode, A still sees its original snapshot. Its attempted upgrade can fail with SQLITE_BUSY_SNAPSHOT: B has already released its write transaction, but A cannot turn an obsolete view into a new branch of database history. A must end that transaction and read again. Retrying only the final update preserves the very snapshot that made the update invalid. SQLite isolation

Contrast this with A attempting BEGIN IMMEDIATE while B still owns a write transaction. A has not yet based a decision on an old snapshot. Waiting for B to finish can let A acquire write access and then read current data. WAL allows readers alongside a writer, but does not provide multiple simultaneous writers. Transaction control

Classify Before Retrying

Failure pointInterpretationRecovery boundary
BEGIN IMMEDIATE returns SQLITE_BUSY, with no prior active transactionWriter admission failedRetry admission within a bounded budget
WAL upgrade returns SQLITE_BUSY_SNAPSHOTExisting read snapshot cannot become a writerRoll back, reread, and recompute the transaction
Other non-commit statement returns BUSY inside an explicit transactionContinuing the same transaction is unsafe as a generic retry policyRoll back before retrying the unit of work
COMMIT returns BUSY because of another readerCommit has not completed; the transaction remains activeRetry the commit, or explicitly abandon with rollback
SQLITE_LOCKEDOften a same-connection conflict, or a shared-cache conflictDiagnose statement/cursor or shared-cache ownership

The statement-versus-transaction distinction follows the official sqlite3_step() guidance. In particular, replaying earlier inserts after a BUSY commit can repeat work inside a transaction that still exists.

Retain the extended code: SQLITE_BUSY_SNAPSHOT is 517, while the primary BUSY code is 5. The C interface can return primary codes by default; bindings differ in how they expose extended information. Result codes, extended-code configuration

After errors, inspect state rather than assuming rollback. SQLite documents errors that may automatically end a transaction; the C state query is sqlite3_get_autocommit(). Python exposes Connection.in_transaction and, for SQLite-originated exceptions, sqlite_errorcode and sqlite_errorname on supported versions. Python sqlite3

What a Busy Timeout Actually Buys

busy_timeout installs a busy handler on one connection. It accumulates sleeps while trying to acquire a lock, then gives up. It is not an end-to-end request deadline, and installing another handler replaces the existing one. Configure and inspect every connection used by the operation. Busy timeout API

The handler is not guaranteed to run. SQLite may return BUSY immediately when waiting would risk deadlock, including certain read-to-write upgrades in rollback-journal mode. An apparently unused timeout is therefore not sufficient evidence of a broken connection setting. Busy handler API

For known short read-modify-write transactions, BEGIN IMMEDIATE moves writer contention before the reads. In WAL mode this avoids the stale-snapshot upgrade illustrated above. It also reserves the single writer earlier: do not hold it across model inference, network requests, or human approval. Do not generalize this to “no later BUSY in every journal mode”: rollback-journal readers can still obstruct commit. Transactions

Reproduce the Difference

The following probe uses a fresh temporary database and explicit SQL transaction control. It requires Python 3.11 or later for the exception attributes. It opens no service database.

import sqlite3
import tempfile
from pathlib import Path

with tempfile.TemporaryDirectory() as directory:
    path = str(Path(directory) / "probe.db")
    a = sqlite3.connect(path, isolation_level=None)
    b = sqlite3.connect(path, isolation_level=None)
    try:
        assert a.execute("PRAGMA journal_mode=WAL").fetchone()[0] == "wal"
        a.execute("CREATE TABLE counter(n INTEGER)")
        a.execute("INSERT INTO counter VALUES(0)")
        for connection in (a, b):
            connection.execute("PRAGMA busy_timeout=120")

        a.execute("BEGIN")
        assert a.execute("SELECT n FROM counter").fetchone()[0] == 0
        b.execute("UPDATE counter SET n=n+1")  # commits automatically
        for _ in range(2):
            try:
                a.execute("UPDATE counter SET n=n+1")
            except sqlite3.OperationalError as error:
                assert error.sqlite_errorcode == 517
                print(error.sqlite_errorname, a.in_transaction)
            else:
                raise AssertionError("stale upgrade unexpectedly succeeded")

        a.execute("ROLLBACK")
        a.execute("BEGIN IMMEDIATE")
        assert a.execute("SELECT n FROM counter").fetchone()[0] == 1
        a.execute("UPDATE counter SET n=n+1")
        a.execute("COMMIT")
        assert b.execute("SELECT n FROM counter").fetchone()[0] == 2
    finally:
        a.close()
        b.close()

A larger companion probe was run with SQLite 3.45.1. The main experiment was repeated independently after the research run:

Control or treatmentObserved result
Old snapshot upgraded after B committedCode 517 in about 0.009 ms
Same update retried in the old transactionCode 517 again; snapshot still reads 0
Rollback and fresh transactionReads 1, then commits the update to 2
B holds writer admission; A also requests itCode 5 after about 121 ms with a 120 ms timeout
B releases writer admissionA's next admission succeeds
A issues only BEGIN, without reading, before B commitsA's later update succeeds

These controls distinguish a stale snapshot from a presently held writer lock. BEGIN alone in deferred mode does not establish the read snapshot in this experiment. Timings are observations from one environment, not latency guarantees. The shorter published probe tests the snapshot case; the other controls were run in the companion experiment.

Checkpoint Contention Has Its Own Result

The earlier overview's blanket “busy timeout does not apply to checkpoints” statement is incorrect. The official API distinguishes two locks and several modes:

  • PASSIVE never invokes the busy handler and can leave work unfinished.
  • FULL, RESTART, and TRUNCATE can invoke it while waiting for writer access or qualifying readers.
  • Checkpoint-lock contention returns BUSY without invoking it, even in those waiting modes.

If the handler gives up, a waiting checkpoint may do partial passive work before returning BUSY. These are different outcomes, not one universal immediate-failure rule. Checkpoint API

The SQL wrapper also matters: PRAGMA wal_checkpoint(...) returns a row containing busy status and frame counts. A call that raises no exception does not establish that the requested checkpoint completed. Fetch and interpret the result. PRAGMA documentation

Design a Retry Unit That Can Be Repeated

The following is application guidance inferred from those contracts:

  1. Name the unit. For a work claim, retry reading eligibility, choosing the item, and recording ownership together. Reusing a decision computed from discarded reads defeats transaction replay.
  2. Bound total work. Account for engine waiting, SQL execution, and application backoff against one monotonic deadline. Reduce each subsequent lock-wait allowance to the remaining budget; also limit attempts. A busy timeout alone cannot enforce a whole-operation deadline.
  3. Separate phases. Track admission, body, and commit. A BUSY commit is a different continuation from a body failure that requires rollback. Escalate non-contention failures instead of treating every database exception as transient.
  4. Keep external effects out of replay. A database rollback does not unsend an email or undo a tool call. Record an intent transactionally and let a separately designed dispatcher deliver it with an appropriate idempotency strategy. This still does not grant exactly-once behavior to an arbitrary remote service.
  5. Observe actual state. Log the phase, extended code, transaction state, elapsed wait, and attempt count. Avoid logging sensitive SQL parameters. A final failure should tell the caller whether the operation was abandoned or needs reconciliation.

This is a small control loop around a short database operation. It should not become an infinite retry policy around an entire agent turn.

Sources and Evidence Limits

Research used three parallel perspectives: transaction semantics, an isolated two-connection experiment, and application/checkpoint behavior. Primary documentation was checked on 2026-10-20. The measured controls cover WAL contention on the stated SQLite build; commit blocking in rollback mode and checkpoint-lock races were verified against documentation rather than reproduced here.