SQLite / Advanced: concurrency, recovery, and application boundaries / Lesson 18

Resilient embedded application contracts

Combine connection policy, schema readiness, guarded changes, durable request identity, and observable failure into a small SQLite application boundary.

About 5 min readContent reviewed 2026-10-25

On this page

Lesson goals

Define one connection/transaction ownership policy.
Reconcile retries with durable operation identity.
Verify readiness beyond a successful open call.

Make storage behavior part of the application API

An embedded data boundary should state file identity, supported runtime, connection options, schema version, transaction ownership, parameter binding, authorization, resource limits, and error mapping. A function that simply returns an open connection leaves many of those responsibilities unresolved. Different callers should not silently select different foreign-key or transaction policies for the same business data.

Keep connections scoped to a documented unit and respect the host driver's thread rules. Disabling a wrapper's same-thread check does not invent correct synchronization or transaction ownership. SQLite's configured threading mode and the host integration both matter. Test the application's actual ownership model rather than assuming that “SQLite is threadsafe” gives unrestricted concurrent use of one handle.

Inspect schema metadata and actual integrity separately

The first script establishes foreign-key policy, a small constrained schema, and application version metadata. It then reports the active setting, marker, relational check, and quick structural check. Empty foreign_key_check output means no violations found in this synthetic database, not proof that every business rule is satisfied. user_version is a useful gate but must be reconciled with actual expected schema and migrations.

Readiness also includes storage permissions, available capacity, required extensions/functions, and compatibility of all consumers. A successful open can even create a new empty file under some host APIs, so do not confuse it with opening the intended existing database. Use explicit path/mode policy and a known application identity where required. Never probe configured files through a training script.

sql Separate connection policy, metadata, and checks

PRAGMA foreign_keys=ON;
CREATE TABLE customer(id INTEGER PRIMARY KEY) STRICT;
CREATE TABLE purchase(id INTEGER PRIMARY KEY,customer_id INTEGER NOT NULL REFERENCES customer(id)) STRICT;
INSERT INTO customer VALUES(1);
INSERT INTO purchase VALUES(10,1);
PRAGMA user_version=1;
PRAGMA foreign_keys;
PRAGMA user_version;
PRAGMA foreign_key_check;
PRAGMA quick_check;

Expected output

foreign_keys
1

user_version
1

table|rowid|parent|fkid
(no rows)

quick_check
ok

Enforcement and user_version report1; the relational check has no rows, and the synthetic structural check reports ok.

Give retries the identity of the same intended request

A durable unique request key can represent one intended operation across retries. Store its validated payload and result in the same transaction as the guarded inventory change. On a repeated key, verify payload equivalence and return the original result; reject reuse for a different operation. A preliminary existence check without uniqueness and transaction coordination is insufficient under concurrency.

The second program owns its transaction and uses BEGIN IMMEDIATE for this bounded write-oriented operation. It accepts the first two-unit request, returns the same result on replay, and rejects a different quantity under the same key. This is a single-product synthetic contract; a real payload identity must also include product, trusted tenant scope, and every other operation-defining field.

python A transaction-owned request replay contract

import sqlite3
from contextlib import closing

def reserve(db, request_key, quantity):
    if type(quantity) is not int or quantity <= 0:
        raise ValueError("quantity")
    db.execute("BEGIN IMMEDIATE")
    try:
        prior = db.execute("SELECT quantity,result FROM request_log WHERE request_key=?", (request_key,)).fetchone()
        if prior is not None:
            if prior[0] != quantity:
                raise ValueError("request payload differs")
            db.execute("COMMIT")
            return prior[1]
        row = db.execute("UPDATE stock SET quantity=quantity-? WHERE id=1 AND quantity>=? RETURNING quantity",
                         (quantity, quantity)).fetchone()
        if row is None:
            raise ValueError("insufficient stock")
        db.execute("INSERT INTO request_log VALUES(?,?,?)", (request_key, quantity, row[0]))
        db.execute("COMMIT")
        return row[0]
    except Exception:
        if db.in_transaction:
            db.execute("ROLLBACK")
        raise

with closing(sqlite3.connect(":memory:", isolation_level=None)) as db:
    db.executescript("CREATE TABLE stock(id INTEGER PRIMARY KEY,quantity INTEGER NOT NULL CHECK(quantity>=0)); INSERT INTO stock VALUES(1,5); CREATE TABLE request_log(request_key TEXT PRIMARY KEY NOT NULL,quantity INTEGER NOT NULL,result INTEGER NOT NULL);")
    print("first", reserve(db, "training-1", 2))
    print("replay", reserve(db, "training-1", 2))
    try:
        reserve(db, "training-1", 3)
    except ValueError:
        print("conflict rejected")
    print("stock", db.execute("SELECT quantity FROM stock").fetchone()[0])
    print("requests", db.execute("SELECT COUNT(*) FROM request_log").fetchone()[0])

Expected output

first 3
replay 3
conflict rejected
stock 3
requests 1

The stock falls once from5 to3; replay returns3, a mismatched payload is rejected, and only one request row is stored.

Do not treat every lost response as a rollback

If confirmation is lost around commit, the database may have committed even though the caller observed an error. Reconcile using the original operation key rather than creating a fresh key and repeating the business action. A confirmed rollback from a rejected transaction and an unknown completion are different outcomes. Retry policy must distinguish them and retain appropriate bounded attempts.

External effects cannot be undone by SQLite rollback. An outbox row committed with the business state can coordinate later delivery, while consumers need deduplication because delivery can repeat. Do not charge, notify, or mutate another service inside a retried database function without a reviewed coordination contract. The examples perform no external effects and claim no universal exactly-once network guarantee.

Test the complete application boundary

Verify normal mutation, duplicate replay, mismatched payload, insufficient stock, constraint rejection, busy failure, cancellation, migration mismatch, and clean resource closure. Keep exact output tests for deterministic local computations and controlled schedules for concurrency. A result parser should not depend on unstable EQP text or assume that a particular rowid reveals operation success.

Record runtime feature and patch requirements, backups, recovery drills, and the data-protection policy with the application. Logs should preserve operation identity and restricted causes without exposing credentials or whole personal records. A resilient SQLite integration explains what is stored, who may change it, when it becomes durable, how it is restored, and how the caller learns a truthful outcome.

Common errors and fixes

A retry reserved stock twice.

Why it happens: An uncertain completion was repeated as a new operation.
How to fix it: Reconcile the original durable request key and payload.
Prevent it next time: Test lost confirmation and matching replay.

A version marker was treated as full readiness.

Why it happens: Actual schema, policy, or integrity was not checked.
How to fix it: Validate the expected objects and operational requirements.
Prevent it next time: Test wrong-file and migration-mismatch cases.

Disabling a thread check caused confusing transaction errors.

Why it happens: A wrapper guard was removed without an ownership/synchronization design.
How to fix it: Use a documented connection-per-unit or reviewed shared-handle policy.
Prevent it next time: Test the real host threading mode.

Check your understanding

1. A repeated key arrives with a different quantity. Why should the example reject rather than return the stored result?

Show worked solution

The key is supposed to identify the same intended operation. A different payload conflicts with that promise and could hide a caller bug or another request. Validate every operation-defining field before treating a duplicate as an idempotent replay.

2. A write times out while commit confirmation is uncertain. Should the caller generate a new request key?