Transaction Design, Idempotency, and Failure Injection

Learning outcomes

Correct SQL does not automatically produce a correct transactional application. A production transaction should map to a business invariant, be short enough to reduce contention, behave predictably when retried, and acknowledge that SQLite cannot roll back side effects in the filesystem or network.

Start with the invariant, not with BEGIN

A transaction boundary should surround the smallest set of database state changes that must be observed together. For FieldNotes, consider “consume one spare part and record the maintenance action exactly once.” The invariant is stronger than “both INSERT statements are syntactically valid.”

DROP TABLE IF EXISTS command_receipt;DROP TABLE IF EXISTS part_consumption;DROP TABLE IF EXISTS service_log;DROP TABLE IF EXISTS spare_part;CREATE TABLE spare_part(  device_code TEXT NOT NULL,  sku         TEXT NOT NULL,  qty         INTEGER NOT NULL CHECK(qty >= 0),  PRIMARY KEY(device_code, sku)) WITHOUT ROWID;CREATE TABLE service_log(  service_id  INTEGER PRIMARY KEY,  request_id  TEXT NOT NULL UNIQUE,  device_code TEXT NOT NULL,  summary     TEXT NOT NULL);CREATE TABLE part_consumption(  service_id INTEGER NOT NULL REFERENCES service_log(service_id) ON DELETE CASCADE,  sku        TEXT NOT NULL,  qty        INTEGER NOT NULL CHECK(qty > 0),  PRIMARY KEY(service_id, sku)) WITHOUT ROWID;CREATE TABLE command_receipt(  request_id TEXT PRIMARY KEY,  status     TEXT NOT NULL CHECK(status IN ('committed')),  service_id INTEGER NOT NULL UNIQUE REFERENCES service_log(service_id));INSERT INTO spare_part VALUES('PUMP-007','SEAL-KIT',2);

Transaction duration is an operational decision

Open transactions retain snapshots, locks, dirty state, journal/WAL resources, and application assumptions. Long transactions increase the window in which other work can conflict or be delayed. Keep user think-time and unpredictable external latency outside the database transaction whenever the invariant allows it.

Idempotency: retrying the same request should not duplicate the business effect

Failures are ambiguous. A client can lose its connection after the database committed but before it received the success response. If it retries blindly with a new operation identity, stock may be consumed twice. An idempotency key or stable request ID lets the database recognize the same logical command.

BEGIN IMMEDIATE;-- 1. If this request already committed, application should return the saved result.SELECT service_idFROM command_receiptWHERE request_id='REQ-9001';-- 2. Only if no receipt exists, verify and consume stock.UPDATE spare_partSET qty = qty - 1WHERE device_code='PUMP-007'  AND sku='SEAL-KIT'  AND qty >= 1;SELECT changes() AS stock_rows; -- must be exactly 1-- 3. Record the service command using the same stable request ID.INSERT INTO service_log(request_id,device_code,summary)VALUES('REQ-9001','PUMP-007','Replace mechanical seal');-- application captures service_id using RETURNING or driver generated-key API-- suppose the returned service_id is 1 for this disposable labINSERT INTO part_consumption(service_id,sku,qty)VALUES(1,'SEAL-KIT',1);INSERT INTO command_receipt(request_id,status,service_id)VALUES('REQ-9001','committed',1);COMMIT;

In production, do not hard-code service_id=1; capture it with RETURNING or your driver API as Chapter 6 taught. The schema’s UNIQUE/PRIMARY KEY constraints make duplicate request IDs observable instead of silently duplicating state.

Failure injection: test the points where assumptions break

Create a fresh disposable database for each scenario. Before running it, predict the final stock quantity, service row count, consumption row count, and receipt row count.

Executable failure-injection harness

This compact Python harness uses explicit autocommit mode so every transaction boundary is visible in SQL. Run it only against the disposable lab database.

import sqlite3con = sqlite3.connect("txn-design-lab.db", isolation_level=None)con.execute("PRAGMA foreign_keys=ON")try:    con.execute("BEGIN IMMEDIATE")    cur = con.execute(        """UPDATE spare_part           SET qty=qty-1           WHERE device_code=? AND sku=? AND qty>=1""",        ("PUMP-007", "SEAL-KIT"),    )    if cur.rowcount != 1:        raise RuntimeError("stock precondition failed")    # Failure injection point: database change exists only inside this transaction.    raise RuntimeError("simulated application failure after decrement")    # Unreachable writes would follow here.    con.execute("COMMIT")except Exception:    con.execute("ROLLBACK")qty = con.execute(    "SELECT qty FROM spare_part WHERE device_code=? AND sku=?",    ("PUMP-007", "SEAL-KIT"),).fetchone()[0]print(qty)  # expected original quantity: 2con.close()

SQLite atomicity ends at the database boundary

                one SQLite transaction
        +-------------------------------------+
        | stock row                           |
        | service_log row                     |
        | part_consumption row                |
        | command_receipt row                 |
        +-------------------------------------+
                 all commit / rollback

   file rename       HTTP request       email/SMS
       |                  |                |
       +------------------+----------------+
              NOT automatically rolled back by SQLite

If a workflow needs a database change and an external action to behave reliably together, store a durable intent inside the database transaction, commit, then let another component perform/retry the external action. This is the basic motivation for an outbox pattern; full distributed consistency is beyond this SQLite chapter.

Large batches: atomicity versus contention versus recoverability

“One enormous transaction is fastest” is not a universal design rule. Batching can reduce per-transaction overhead, but very large transactions can hold writer status longer, grow rollback/WAL resources, increase recovery work, and make retry granularity coarse. Measure realistic workloads later in Chapter 18.

Retry design: retry conditions, not arbitrary SQL

Chapter 9 will cover SQLITE_BUSY policies. Even before that, distinguish a retryable transport/contention event from a failed business precondition. Retrying “stock quantity was already zero” does not make it valid. Retrying an ambiguous post-COMMIT network response should use the same request ID and read the receipt.

Transaction-design checklist

Use this checklist in Chapters 9, 15, 17, 18, and the capstone:

1. Invariant: What must never be observed half-done?
2. Scope: Which database rows/tables must change together?
3. Preconditions: Which predicates/counts prove the command is still valid?
4. BEGIN mode: Is DEFERRED acceptable, or should writer contention be discovered with IMMEDIATE?
5. Duration: Can user input, network I/O, sleeps, or expensive computation move outside?
6. Error path: For every failed statement/exception, what explicit rollback/recovery path runs?
7. Idempotency: What stable request identity prevents duplicate effects after ambiguous failures?
8. External effects: Which files/messages/API calls are outside SQLite atomicity?
9. Verification: What row counts/RETURNING values/invariants are checked before commit?
10. Failure injection: Have you tested before commit, during the unit, after commit-before-response, and restart/retry?

End-of-chapter verification

Production judgment and Chapter 9 bridge

You now have the transaction model needed for concurrency: explicit atomic boundaries, write-intent timing, rollback-mode crash safety, savepoint recovery, idempotent retry identity, and bounded transaction duration. Chapter 9 adds multiple processes, lock contention, busy handling, WAL readers/writers, checkpoints, and the production history behind modern WAL reliability.

Authoritative references

• SQLite — Transaction.
• SQLite — Savepoints.
• SQLite — Atomic Commit.
• SQLite — Result and Error Codes.
• SQLite — PRAGMA reference.