Retry the SQLite Transaction, Not Just the Busy Statement

A short SQLite read–modify–write path often looks retryable at the statement level: read qty=10, calculate 11, run an UPDATE, and, if Python raises OperationalError: database is locked, run that UPDATE again. In WAL mode, that can be the wrong recovery boundary. The important question is not whether the competing writer has finished. It is whether the losing connection is still reading from a snapshot that predates the competing commit.

The SQLite isolation reference documents exactly that case. A WAL reader keeps a stable snapshot for the life of its read transaction; if another connection commits after that snapshot was established, promoting the old reader to a writer can fail with the extended result SQLITE_BUSY_SNAPSHOT. To write, the reader must release the obsolete snapshot and start a new transaction.

This playbook makes that boundary observable with two CPython sqlite3 connections to one disposable local file per scenario. The supplied fixture was executed unchanged for this article: CPython 3.13.5, linked SQLite 3.46.1, Linux, WAL mode, autocommit=True, explicit SQL BEGIN/COMMIT/ROLLBACK, and deterministic single-process interleavings, with no threads and no sleeps. The acceptance object is not “the UPDATE eventually returned.” It is a ledger showing the reads that informed the decision, the competing commit, the extended error, transaction state, and the independently reconciled final row.

The operational answer is therefore precise: retry the complete transaction you own, reread every decision-making input, recompute, then reconcile the committed row. A statement-only retry is a repair candidate, not an accepted recovery.

Define the read–modify–write result that must survive contention

The application operation is intentionally small: row stock(id=1) starts with qty=10; connection A owns “read the current quantity, add one, write the result”; connection B is a competing writer. The row identity never changes. There is no UPSERT, cascade, schema change, generated ID, backup copy, or remote database involved.

That narrow contract matters because it tells us what “correct” means after contention. If A acts alone, 10 -> 11 is correct. If A reads 10 and B then commits 20 before A can write, A’s authorized retry must incorporate B’s committed state. The accepted result is therefore 20 -> 21, not 11. A successful statement that writes 11 after B committed 20 is a stale-value overwrite, even if it ran inside a newly opened transaction.

The acceptance evidence must cover five facts together: which value A actually read, whether B’s competing write completed, which SQLite code and symbolic name accompanied a failed promotion, whether A remained in a transaction after that failure, and what quantity was visible after the candidate recovery committed. The Python 3.13 sqlite3 reference documents sqlite_errorcode and sqlite_errorname on exceptions that originate in SQLite, while Connection.in_transaction exposes the low-level SQLite transaction state.

A return status alone cannot approve the operation. The same is true of a green diagnostic run: case S6 is supposed to succeed technically while proving that a “fresh transaction” can still contain a stale application decision. That case is a negative control, not a repair recipe.

For this playbook, ACCEPT means the complete authorized operation was rerun from refreshed inputs and the final row reconciles. REPAIR means the retry boundary or computation is wrong but ownership is known. RERUN means required evidence, especially extended code or transaction state, is missing. HOLD means transaction ownership or external effects are unknown, so an automated retry cannot safely decide what to repeat.

Freeze a disposable WAL environment

A concurrency result is useful only if the transaction mode and engine actually under test are explicit. Each scenario therefore creates a new temporary file, initializes stock(id=1, qty=10), executes PRAGMA journal_mode=WAL, and requires the pragma to read back wal. This is an on-disk test because the object under review is WAL snapshot behavior between two connections to the same local database file. It is deliberately not an in-memory convenience test.

That boundary is different from SQLite WAL backup completeness, where the question is whether a recovery artifact contains committed state. Here no database file is copied and no checkpoint policy is being evaluated. The object under review is a live read transaction whose snapshot may be older than the latest committed database state.

The supplied preparation record identifies CPython 3.13.5, SQLite 3.46.1, Linux 6.18.55-x86_64-with-glibc2.41, status PASS_9_CASES, and SHA-256 f7c2344748b567a079143b080e22ec12a3cc5650bb96dd25a69817b7f5141368. A fresh execution for this article used the exact supplied source bytes and reproduced the same source hash, interpreter identity, linked SQLite version, platform, and nine-case pass. It started at 2026-10-22T10:01:19.919462+00:00 and finished at 2026-10-22T10:01:20.091231+00:00. Those are observations from this run, not promises about another host.

Keep the Python wrapper identity separate from the embedded engine identity. A supplemental probe returned sqlite3.version == "2.6.0" and sqlite3.sqlite_version == "3.46.1"; Python’s 3.13 documentation explicitly says the former is a deprecated module/pysqlite-era value with no practical version meaning, while sqlite3.sqlite_version names the runtime SQLite library. The living Python 3.13 documentation consulted on October 22, 2026 displayed a 3.13.16 documentation build; that does not turn the executed interpreter into 3.13.16.

The exact fixture is reproduced below so a future owner can rerun the same schedule on an owned disposable resource:

from future import annotations
import hashlib, json, platform, sqlite3, sys, tempfile
from datetime import datetime, timezone
from pathlib import Path


def busy(con: sqlite3.Connection, sql: str, code: int) -> dict:
    try:
        con.execute(sql).close()
    except sqlite3.OperationalError as exc:
        result = dict(code=exc.sqlite_errorcode, name=exc.sqlite_errorname,
                      message=str(exc), in_transaction=con.in_transaction)
        assert result['code'] == code, result
        return result
    raise AssertionError('Expected a SQLite error, not a successful statement')


def value(con):
    return con.execute('SELECT qty FROM stock WHERE id=1').fetchone()[0]


def run(case: str, path: Path) -> dict:
    a = sqlite3.connect(path, autocommit=True, timeout=0)
    b = None
    try:
        assert a.execute('PRAGMA journal_mode=WAL').fetchone()[0] == 'wal'
        a.execute('CREATE TABLE stock(id INTEGER PRIMARY KEY, qty INTEGER NOT NULL)')
        a.execute('INSERT INTO stock VALUES(1,10)')
        b = sqlite3.connect(path, autocommit=True, timeout=0)
        row = dict(case=case, journal_mode='wal', events=[])
        if case == 'S1':
            a.execute('BEGIN')
            old = value(a)
            a.execute('UPDATE stock SET qty=? WHERE id=1', (old+1,))
            a.execute('COMMIT')
            assert value(b) == 11
        elif case in {'S2','S3','S4','S5','S6'}:
            a.execute('BEGIN')
            old = value(a)
            b.execute('UPDATE stock SET qty=20 WHERE id=1')  # Autocommit finishes here.
            assert not b.in_transaction
            assert value(a) == 10 and value(b) == 20
            if case == 'S4':
                a.execute('PRAGMA busy_timeout=250')
                assert a.execute('PRAGMA busy_timeout').fetchone()[0] == 250
            row['events'].append(busy(a, 'UPDATE stock SET qty=11 WHERE id=1', 517))
            assert a.in_transaction and value(a) == 10
            row['writer_finished'] = not b.in_transaction
            if case == 'S3':
                row['events'].append(busy(a, 'UPDATE stock SET qty=11 WHERE id=1', 517))
            if case in {'S5','S6'}:
                a.execute('ROLLBACK')
                a.execute('BEGIN')
                fresh = value(a)
                chosen = fresh if case == 'S5' else old
                a.execute('UPDATE stock SET qty=? WHERE id=1', (chosen+1,))
                a.execute('COMMIT')
                row.update(old_read=old, fresh_read=fresh, calculation_input=chosen)
                assert value(b) == (21 if case == 'S5' else 11)
            else:
                a.execute('ROLLBACK')
                assert value(b) == 20
        elif case == 'S7':
            b.execute('BEGIN IMMEDIATE')
            b.execute('UPDATE stock SET qty=20 WHERE id=1')
            row['events'].append(busy(a, 'BEGIN IMMEDIATE', 5))
            b.execute('COMMIT')
            a.execute('BEGIN IMMEDIATE')
            fresh = value(a)
            a.execute('UPDATE stock SET qty=? WHERE id=1', (fresh+1,))
            a.execute('COMMIT')
            assert value(b) == 21
        elif case == 'S8':
            a.execute('BEGIN IMMEDIATE')
            old = value(a)
            row['events'].append(busy(b, 'UPDATE stock SET qty=qty+10 WHERE id=1', 5))
            a.execute('UPDATE stock SET qty=? WHERE id=1', (old+1,))
            a.execute('COMMIT')
            b.execute('UPDATE stock SET qty=qty+10 WHERE id=1')
            assert value(a) == 21
        elif case == 'S9':
            a.execute('BEGIN')  # No SELECT has established a read snapshot yet.
            b.execute('UPDATE stock SET qty=20 WHERE id=1')
            fresh = value(a)
            assert fresh == 20
            a.execute('UPDATE stock SET qty=? WHERE id=1', (fresh+1,))
            a.execute('COMMIT')
            assert value(b) == 21
        row['final_qty'] = value(b)
        row['approved_update'] = case in {'S1','S5','S7','S8','S9'}
        return row
    finally:
        for con in (b, a):
            if con is not None:
                if con.in_transaction:
                    con.execute('ROLLBACK')
                con.close()


def main():
    start = datetime.now(timezone.utc).isoformat()
    with tempfile.TemporaryDirectory(prefix='refonte-wal-') as folder:
        rows = [run(f'S{i}', Path(folder)/f's{i}.db') for i in range(1,10)]
    print(json.dumps(dict(status='PASS_9_CASES', started=start,
        finished=datetime.now(timezone.utc).isoformat(), python=sys.version,
        sqlite=sqlite3.sqlite_version, platform=platform.platform(),
        source_sha256=hashlib.sha256(Path(__file__).read_bytes()).hexdigest(), cases=rows), indent=2))

if name == '__main__':
    main()

Build a transaction ledger before introducing a competitor

S1 is the control that proves the fixture can perform the intended operation without contention. A executes SQL BEGIN, reads 10, calculates 11, updates the known row, and executes SQL COMMIT. B then reads 11. There is no busy event. The final quantity is 11, so S1 is ACCEPT baseline.

The useful habit is to log the operation as a transaction ledger rather than as an exception trace. For S1 the ledger is: initial committed value 10; A transaction opened; A decision read 10; A calculation input 10; A write candidate 11; A commit completed; independent B observation 11. That makes the later conflict cases comparable. Without the baseline, a final value by itself would not reveal whether the test was exercising the intended row or transaction boundary.

SQLite’s transaction control reference says reads and writes occur within transactions, and a manually started transaction normally persists until explicit COMMIT or ROLLBACK. It also distinguishes read transactions from write transactions: a SELECT starts a read transaction; a later write attempts to upgrade it. The fixture uses that documented transition directly instead of hiding it behind an ORM or helper.

The independent final read is important. A successful UPDATE call demonstrates only that the statement completed. A completed COMMIT demonstrates a transaction outcome, but the application still needs to verify the state it intended to produce when that state is the acceptance object. In this lab, B’s post-commit read is the reconciler. In production, the corresponding check might be a fresh read on the same or another connection, depending on the application’s ownership and consistency protocol.

A Python transaction setting is not an SQL transaction event

With autocommit=True, CPython puts the underlying SQLite connection in SQLite autocommit mode. Python documents that Connection.commit() and Connection.rollback() have no effect in this mode; explicit SQL can still establish and end transactions, and in_transaction reports the low-level state.

That is why this fixture deliberately issues BEGIN, COMMIT, and ROLLBACK as SQL. Substituting Python method calls would change the evidence boundary. The goal is not to recommend one transaction API for every application; it is to make every boundary in this specific test visible and deterministic.

Reproduce the stale-reader promotion failure

S2 establishes the failure the retry policy must recognize. A executes BEGIN and then SELECT, reading qty=10. That first read establishes A’s WAL snapshot. B, on its separate connection, executes UPDATE stock SET qty=20 WHERE id=1. Because B is in SQLite autocommit mode and no explicit transaction surrounds that statement, its write finishes before control returns to the fixture; the assertion confirms b.in_transaction is false.

Now the two connections disagree by design. A still reads 10 because its read transaction retains the earlier snapshot. B reads 20 because B sees the new committed state. SQLite documents this WAL snapshot isolation behavior: commits made after a reader’s snapshot begins remain invisible to that reader until its transaction ends.

A then attempts UPDATE stock SET qty=11 WHERE id=1. The fresh execution for this article recorded:

The message is intentionally the least useful part of that record. SQLite’s result and error code reference defines 517 as SQLITE_BUSY_SNAPSHOT, an extended SQLITE_BUSY result for WAL databases when a connection tries to promote a read transaction after another connection has written and invalidated the prior reads. Python’s error attributes preserve the numeric and symbolic evidence needed to distinguish that case from a plain primary code 5.

This is the live-transaction counterpart to other Python SQLite transaction boundaries: the linked article asks whether executescript() crosses a pending transaction boundary under specific driver modes, whereas this fixture has explicit SQL boundaries and asks whether an old WAL reader can still become a writer.

The writer can be finished while the snapshot is still unusable for writing

S2 rules out a common misdiagnosis: “database is locked” does not necessarily mean another writer is still holding the write position. B is already idle when A gets 517. The obstacle is A’s own obsolete snapshot, not a still-running B transaction.

SQLite’s isolation reference is explicit: after X reads, Y writes, and X then tries to write, X gets SQLITE_BUSY_SNAPSHOT; X must release the snapshot, for example with ROLLBACK, and start a new transaction before it can write. That is the reason a longer wait cannot, by itself, turn this particular snapshot into a current one.

Test statement retries and the busy-timeout assumption

S3 repeats A’s failed UPDATE inside the same transaction. Nothing else changes: B has already committed, A still holds the same read transaction, and the application value used by the statement is still 11. The first attempt returns 517 with in_transaction=true; the second attempt returns 517 again with in_transaction=true; after explicit rollback, the database remains at 20. The correct decision is REPAIR statement-only retry.

This case answers the “does retry recompute from a fresh read?” question with a firm no. Re-executing the UPDATE statement does not rerun the earlier SELECT; it does not end the transaction; and it does not rebuild the application calculation. The fixture passes because it proves those two retries are still wrong. A generic loop around only the failing statement would simply keep asking the obsolete reader to perform a promotion SQLite has already rejected.

S4 tests a different assumption. After establishing the same stale snapshot, A executes PRAGMA busy_timeout=250 and reads it back as 250. A then attempts the same UPDATE. The observed result remains 517 / SQLITE_BUSY_SNAPSHOT, A remains in the transaction, A still reads 10, and the final row after rollback remains 20. The 250 ms value is configuration input only; this test does not claim that the call slept for exactly 250 ms or that every busy path invokes a busy handler.

SQLite’s busy-timeout API configures a busy handler that sleeps and retries when appropriate until its configured sleep budget is reached, after which the SQLite operation returns SQLITE_BUSY. The contract describes contention handling; it does not say that a timeout creates a new read snapshot. The transaction documentation and isolation reference separately say an active read continues to see its historic snapshot until the transaction ends. Combining those contracts leads to the operational rule: timeout policy and snapshot-generation policy are different controls.

Also keep the primary and extended result spaces separate. SQLite documents primary SQLITE_BUSY as code 5 and SQLITE_BUSY_SNAPSHOT as extended code 517. It also distinguishes SQLITE_BUSY from SQLITE_LOCKED; the latter usually concerns a conflict within the same connection or shared-cache context, not this two-connection WAL promotion case. A handler that catches every OperationalError, matches the message text “database is locked,” and retries indefinitely is therefore missing the evidence needed to choose a safe recovery boundary.

Waiting does not create a new read snapshot

For this failure class, time is not the missing ingredient. Transaction renewal is. As long as A keeps the same read transaction open, SQLite documents that A continues to see the historic snapshot.

A timeout can be useful for ordinary writer contention, as S7 will show conceptually, but it should not be sold as a stale-snapshot repair. The retry policy must first classify the error, then decide whether to wait for a lock, restart an owned transaction, or stop because ownership/effects are unclear.

Restart the whole owned transaction and reread

S5 is the accepted recovery path. It begins with the same losing schedule: A reads 10, B commits 20, A’s attempt to write 11 receives 517 / SQLITE_BUSY_SNAPSHOT, and A remains inside its old transaction. At that point the repair does not retry the statement. It explicitly ends A’s owned unit with SQL ROLLBACK.

Only then does A start a new BEGIN and rerun the decision read. The new read returns 20. The application calculation is performed again using 20, producing 21. A updates the row to 21, commits, and B independently reads 21. The ledger records old_read=10, fresh_read=20, calculation_input=20, and final_qty=21. That combination, rather than the successful commit alone, is why S5 is ACCEPT bounded retry.

SQLite’s isolation documentation says a reader that wants to see a later writer’s changes must end the old read transaction and start a new one; its SQLITE_BUSY_SNAPSHOT example likewise says to release the snapshot and begin again before writing. The transaction documentation explains why: while a read transaction is active, later changes by other connections remain outside that historical view.

A production retry wrapper should therefore surround the whole read–decide–write unit, not merely execute(update_sql). Its conceptual shape is:

for bounded attempt:
    BEGIN
    read all decision inputs
    compute from those reads
    attempt writes
    COMMIT
    reconcile required committed state

on recognized retryable transaction conflict:
    ROLLBACK only the transaction this code owns
    retry from BEGIN

That outline deliberately leaves policy-specific details, including attempt count, backoff, deadline, and which result codes qualify, to the application owner. It does not authorize retrying every OperationalError. It also does not say that rollback is safe when the calling layer does not own the transaction. Ownership is part of the recovery precondition.

The word “reread” is essential. Starting a new transaction only creates the opportunity to observe a new snapshot. The application must actually execute the reads again and feed those values back into the decision. S6 demonstrates why.

Expose the successful retry that still loses the update

S6 is the most important negative control because every SQLite call in the second transaction succeeds. The first half is familiar: A reads 10, B commits 20, and A’s attempt to write 11 gets 517. A then rolls back and begins a new transaction. The fresh read correctly returns 20.

The defect is in application state. Instead of calculating from fresh, the fixture deliberately reuses old, the Python variable that still contains 10. chosen = old; the update therefore writes 11. SQLite accepts it because the new transaction is current and can write. COMMIT succeeds. The final independent read is 11.

This case is REJECT stale recomputation even though the database accepted every statement in the retry transaction. The expected application result after B’s committed 20 is 21. Ending the old snapshot solved the database-level promotion problem but did not solve the application-level stale-input problem.

That distinction is why a retry helper should be designed around a callable that performs reads and calculations inside each attempt, rather than around cached values prepared before the retry loop. Inputs such as row values, authorization state, version columns, limits, and derived amounts can all become stale across a rollback/retry boundary. Which inputs must be refreshed is an application contract, not something SQLite can infer.

This is also where SQLite connection-state validation offers a useful adjacent lesson without proving this case: the linked article validates a connection setting and transaction state before admitting business work. Here, connection state is only one part of the evidence; the retried business decision must also be regenerated. That article focuses on foreign-key pragma timing, a separate admission check from the stale-snapshot retry examined here.

A new transaction can still contain an old application decision

“Fresh transaction” and “fresh computation” are separate predicates. S6 has the first and fails the second. The retry boundary must therefore invalidate or reconstruct any cached decision that depended on the prior read generation.

The safest review question is concrete: show me the read value used by the successful write. In S5 that value is 20. In S6 it is 10. Both retries contain a fresh read of 20, but only one actually uses it. That is why the ledger records calculation_input explicitly instead of assuming that a SELECT somewhere in the retry proves recomputation.

Compare ordinary writer contention and BEGIN IMMEDIATE

S7 and S8 separate stale-reader promotion from ordinary writer contention. SQLite supports only one simultaneous write transaction. A plain SQLITE_BUSY can therefore mean another connection currently owns the write position, which is different from a 517 stale snapshot after the competing writer has already finished. SQLite documents primary code 5 for this kind of concurrent activity and notes that BEGIN IMMEDIATE itself can return SQLITE_BUSY when another write transaction is active.

In S7, B executes BEGIN IMMEDIATE, updates the row to 20, but does not commit yet. A then executes BEGIN IMMEDIATE. The observed error is 5 / SQLITE_BUSY, and A’s in_transaction is false because A never acquired the transaction. That is ordinary acquisition contention, not SQLITE_BUSY_SNAPSHOT. After B commits, A retries the acquisition, succeeds, reads 20, writes 21, commits, and the final row is 21. The accepted operation is the later bounded transaction, not the failed acquisition.

S8 reverses the order. A executes BEGIN IMMEDIATE before reading, so A obtains the write transaction up front and then reads 10. B’s competing autocommit update receives primary SQLITE_BUSY code 5 and is not left in a transaction. A writes 11 and commits. B then retries serially, applies its qty=qty+10, and the final quantity is 21. This case is ACCEPT bounded acquisition strategy for the stated schedule.

SQLite’s isolation reference recommends BEGIN IMMEDIATE when a transaction knows it will eventually write and wants to avoid the read-then-promotion SQLITE_BUSY_SNAPSHOT path; successful BEGIN IMMEDIATE starts the write transaction before the read. The result-code reference further states that once BEGIN IMMEDIATE succeeds, subsequent operations on the same database through that transaction will not return SQLITE_BUSY. Keep that guarantee scoped exactly as written: it is about SQLite BUSY results in that acquired transaction, not a promise that an application cannot deadlock on its own mutexes, block on external services, violate a constraint, or fail for unrelated reasons.

Choosing deferred BEGIN plus transaction restart versus BEGIN IMMEDIATE is an ownership and contention-policy decision. BEGIN IMMEDIATE moves writer acquisition earlier, which can reduce the stale-upgrade window but also holds the single writer position while the transaction performs its reads and calculations. The fixture is not a throughput benchmark and supplies no latency percentage. Use it to select semantics, not to invent performance claims.

Identify when the deferred read snapshot actually begins

S9 prevents another easy misconception: SQL BEGIN by itself does not necessarily freeze the WAL snapshot. SQLite documents default BEGIN as DEFERRED. A deferred BEGIN primarily disables the automatic commit behavior; the actual transaction starts when the database is first accessed. If the first access is a SELECT, that starts the read transaction.

The fixture makes the schedule explicit. A executes BEGIN but performs no SELECT. B then updates the row from 10 to 20 and completes. Only after B’s commit does A perform its first read. That read returns 20, not 10. A calculates 21, updates, commits, and the final independent value is 21. S9 is therefore ACCEPT boundary control.

This does not mean “BEGIN never matters.” It means a deferred BEGIN is not the read barrier. Once A actually reads, the snapshot rules apply. The distinction is important when reviewing code that logs “transaction started” at the BEGIN call and then assumes every later read is pinned to that exact wall-clock point. The engine contract is more specific: first database access determines when the deferred transaction becomes a real read or write transaction.

Place the read barrier in the schedule explicitly

Concurrency tests are much easier to reason about when they control logical events rather than elapsed time. The nine cases need no sleeps because every transition is sequenced in one process: A reads; B writes; B’s state is asserted; A attempts promotion. Or A acquires IMMEDIATE; B attempts a write; A commits; B retries.

That deterministic schedule tells a future maintainer exactly which side of the read barrier each commit occurred on. Replacing those event boundaries with “sleep 100 ms and hope B committed” would make a failure ambiguous between database semantics and scheduler timing. For this problem, transaction-generation evidence is more useful than timing noise.

Reconcile the final row independently of the UPDATE

The complete run is easiest to review as one ledger. “Diagnostic pass” and “approved write” are intentionally different columns.

Every case also runs cleanup in finally: if either connection still owns a transaction, the fixture issues SQL ROLLBACK before closing. Because each case owns a new temporary database, the destructive S6 counterexample cannot contaminate another case. That isolation is a laboratory property, not permission to try stale overwrites against production data.

The reconciliation principle matches the adjacent discipline in SQLite write-contract reconciliation: do not infer correctness from a successful write primitive alone; compare the resulting data with the declared contract. The linked article applies that principle to REPLACE/UPSERT and child-row preservation. This one applies it to a single ordinary UPDATE on one row and deliberately excludes cascades.

For the retry problem, the final row carries special weight because a technically successful retry can still be wrong. S6 is proof. The correct acceptance question is not “did the second UPDATE execute?” but “did the complete retried operation recompute from the fresh committed state and leave the row at the authorized value?”

Set safe retry and side-effect boundaries

A bounded database retry needs a clearly owned unit. The retrying code must know which BEGIN it is responsible for, which reads feed the decision, which writes belong to that same application operation, and which ROLLBACK it is authorized to issue. If the connection arrives from a caller with an unknown transaction already active, an inner helper must not casually roll back the caller’s work. That is a HOLD transaction ownership condition until the API contract makes ownership explicit.

Attempt limits are equally important. SQLite’s documented result taxonomy gives you evidence for classification; it does not authorize an infinite loop. A production policy might combine a maximum attempt count with a request deadline and telemetry. The exact numbers depend on service objectives and workload, so this fixture intentionally does not invent them. Exhaustion should surface a controlled failure with the ledger needed for diagnosis, not silently keep the request alive forever.

For SQLITE_BUSY_SNAPSHOT, the repair boundary is the whole owned read–modify–write transaction because the old read generation is the problem. For plain code 5 at BEGIN IMMEDIATE, the transaction has not started on the losing connection, so a bounded acquisition retry can be appropriate once the current writer finishes. Those are different controls even though both belong to the SQLITE_BUSY family. SQLite also warns that additional extended result codes can appear in future releases, while existing result-code numbers and names remain fixed. Unknown or incomplete evidence should therefore stop at RERUN/HOLD, not fall through to “retry all busy-looking errors.”

The retry function should also regenerate all database-dependent decisions. That can include more than one row. If the operation reads inventory, account status, a version number, and a limit, all four may need a new read after rollback. The owner must define the input set. Merely rerunning the final update does not reconstruct the decision; S3 proves that, and S6 proves that even executing a fresh read is insufficient if the old local value still drives the write.

Keep irreversible actions out of blind retries

The lab has no email, payment, queue publish, HTTP mutation, filesystem rename, or other external side effect. It therefore proves nothing about retrying such effects safely.

If a transaction attempt can trigger a non-idempotent action outside SQLite, an automatic rollback-and-repeat may duplicate that action even though the database part is correct on the second attempt. Keep irreversible effects outside the blind retried unit, or coordinate them with an explicit application protocol such as durable intent/outbox records, idempotency keys, or reconciliation. The specific mechanism depends on the surrounding system; this article does not claim one universal pattern.

When external effects are unknown, choose HOLD. Database evidence can tell you what SQLite committed. It cannot infer whether an email was sent or a payment API accepted a request unless the application records and reconciles that fact under its own protocol.

Choose ACCEPT, REPAIR, RERUN or HOLD

A practical retry gate can be compact as long as it preserves the evidence that matters:

The classification rests on documented distinctions. SQLITE_BUSY_SNAPSHOT is extended code 517 for an obsolete WAL read being promoted; ordinary SQLITE_BUSY is primary code 5 for concurrent activity such as another writer; SQLITE_LOCKED is a different result class. Python exposes the extended numeric and symbolic fields directly on SQLite-originated exceptions.

Do not downgrade that evidence to string matching. The phrase “database is locked” appeared in both the 517 and code-5 observations in this run. The human-readable message did not encode the recovery boundary; the code, transaction state, schedule, and reads did.

Make contention recovery a maintained regression test

A retry policy can regress when connection-pool behavior changes, a helper starts a transaction earlier than before, a driver policy changes, or decision logic is refactored outside the retry callable. Preserve the nine-case fixture as a regression test alongside the application’s transaction wrapper rather than treating it as a one-time investigation.

For every qualification run, keep the evidence unit together: source hash; interpreter version; Python wrapper/driver metadata where meaningful; linked SQLite runtime version; platform; start and finish timestamps; journal_mode readback; case rows; numeric and symbolic error values; transaction state; final quantities; assertions; and cleanup outcome. Python documents sqlite3.sqlite_version as the runtime library version, which is why it should be recorded separately from Python’s own version.

The current canonical source hash is f7c2344748b567a079143b080e22ec12a3cc5650bb96dd25a69817b7f5141368. The supplied preparation run and the fresh article verification both matched it and both reported PASS_9_CASES on CPython 3.13.5 with SQLite 3.46.1. A future run on a different engine should be reported as that future run, not retroactively described as the same observation.

Maintain the SQL schedule too. “Two threads raced” is much weaker evidence than “A read 10; B committed 20; B was out of transaction; A attempted promotion and got 517 while still active.” The deterministic schedule is what turns a flaky concurrency symptom into a reviewable contract test.

The adjacent article on SQLite batch evidence rather than status alone applies the same evidence discipline to generated batch identifiers: an API call completing is not sufficient when the caller’s contract requires a fully reconciled mapping. This fixture has no batches and no ID inference, but the reliability method is analogous: define the acceptance object first, then collect evidence that actually proves it.

Treat documentation and execution as separate evidence classes. The SQLite isolation, result-code, transaction, and busy-timeout pages are living references consulted on October 22, 2026; they document contracts and meanings, not which library was linked into this test process. Conversely, a successful local fixture run demonstrates this bounded environment; it does not establish behavior for every SQLite build, VFS, filesystem, application wrapper, or future release.

Practice database reliability with Database Administrator Essentials

The engineering habit behind this playbook is broader than one SQLite error: define a transaction contract, distinguish engine state from application state, capture enough evidence to classify a failure, bound recovery work, and reconcile the final data instead of approving a successful API call by itself.

Refonte Learning’s Database Administrator Essentials page lists a three-month program with a stated commitment of 12–14 hours per week and recommends basic programming knowledge. Its published competency list includes database design and architecture, SQL query optimization, backup and recovery, performance tuning, security, migration and integration, disaster recovery, role-based access control, and monitoring and maintenance. The page also describes potential internship participation and states that successful completion is associated with a Training Certificate and a Certificate of Internship.

Those program details are a reasonable skills bridge for engineers who want structured practice in database operations and reliability. The page does not establish that this exact SQLite WAL snapshot lab, SQLITE_BUSY_SNAPSHOT handling, or extended-result-code retry design is part of the curriculum, so this article makes no such claim.

For the incident class covered here, keep the operating rule narrow and testable: when a WAL reader loses promotion with 517 after another connection has committed, do not keep hammering the same UPDATE. End only the transaction you own, begin again, reread the data that controls the decision, recompute from those fresh values, commit, and reconcile the final row. If the evidence cannot prove that boundary, or cannot prove what else the attempt already did, stop at RERUN or HOLD rather than turning uncertainty into an unbounded retry loop.