# DDIA Ch. 13: retrying an "atomic" transfer charges $22, not $11 ## Concept A reader reasons: "the transfer is a single ACID transaction, so retrying it after a timed-out COMMIT is safe." Run it on `sqlite3` and watch the retry move **$22** instead of $11. Atomicity guarantees each transaction is all-or-nothing — but it says *nothing* about running that transaction twice. When the client never hears back from the first COMMIT and retries, the second, equally-atomic transaction moves the money again: payer 100→78, payee 0→22. Then add a `requests` table with a `UNIQUE(request_id)` constraint and pass a client-generated id end-to-end (DDIA Example 13-2): the retry's INSERT hits the uniqueness constraint, the whole transaction rolls back, and the transfer happens **exactly once** — payer 100→89, payee 0→11. Same transfer, same retry; the only difference is whether the operation itself can recognize a duplicate. ## Provenance *Designing Data-Intensive Applications*, 2nd ed. (Kleppmann & Riccomini, 2025), Chapter 13 — *The Future of Data Systems* (§"The end-to-end argument for databases", §"Duplicate suppression", §"Uniquely identifying requests", Examples 13-1 and 13-2): > Just because a transaction is atomic doesn't mean it is idempotent [...] To > make the operation idempotent, we can attach a unique identifier to the > request and make sure it is only executed once. The book uses a bank transfer to separate two properties that sound interchangeable: *atomicity* (each run is all-or-nothing) and *idempotence* (running it twice equals running it once). TCP retransmission and the database's own transaction handling both suppress duplicates — but only within a single connection. The moment a request crosses that boundary and is retried, the duplicate is a fresh, legitimate transaction. Only an identifier the *operation* checks can close the gap. Here you make the double-charge happen, then close it. ## Prerequisites The surprise lives in the gap between "atomic" and "idempotent." Each line notes where it shows up. 1. **ACID atomicity** — a transaction commits all its writes or none; there is no half-applied transfer. It shows up as the two UPDATEs plus COMMIT in `transfer()`, which always move $0 or the full $11 together. 2. **Idempotence** — an operation is idempotent when applying it twice has the same effect as applying it once. It shows up (by its *absence*) as the second `transfer()` call moving another $11. 3. **At-least-once retries** — a client that times out waiting for an ack can't tell "COMMIT succeeded, ack lost" from "COMMIT failed," so a correct client *must* retry — sometimes delivering the request twice. It shows up as the second `transfer()` call standing in for that retry. 4. **The end-to-end argument** — a correctness property (here: at-most-once money movement) must be enforced at the communication endpoints that own it, because lower layers (TCP, the DB connection) can't. It shows up as the fix living in the `requests` table the operation checks, not in the transport. 5. **Deduplication via a unique request id** — a client-generated id, threaded end-to-end and stored under a uniqueness constraint, lets the operation recognize its own duplicate. It shows up as `UNIQUE(request_id)` and the caught `sqlite3.IntegrityError`. ### Where to learn the prerequisites - **Atomicity ≠ idempotence, and the request-id fix (#1, #2, #5):** DDIA §"The end-to-end argument for databases", §"Duplicate suppression", §"Uniquely identifying requests" (Examples 13-1, 13-2). This is the pair that carries the whole result — hold onto the distinction and everything else follows. - **At-least-once delivery (#3):** DDIA Ch. 11 §"Exactly-once message processing" — why a retrying sender is the honest default and exactly-once is built *on top of* at-least-once, not instead of it. - **The end-to-end argument (#4):** Saltzer, Reed & Clark, "End-to-End Arguments in System Design" (1984) — the original; DDIA quotes it directly. If only one idea is new, make it **atomicity ≠ idempotence** — it is the entire reason a retried ACID transaction can still corrupt the ledger. ## Environment Deterministic, so the balances reproduce exactly: - macOS 26.5.2 (Darwin 25.5.0), arm64 - Python 3.15.0a8, standard library only (`sqlite3`) — no Docker, no deps - No real network: the lost COMMIT ack and the retry are simulated by calling `transfer()` a second time in one process - Fixed workload: transfer $11, payer seeded $100, payee $0; request id `req-6f1a2b3c-0001` (fixed, not random) so the run is deterministic - Balances are whole dollars (integers) ## Mental model Same transfer, same retry — only whether the operation can spot a duplicate differs. - **Scenario 1 — the "atomic" transfer.** `transfer($11)` runs two UPDATEs (payer −11, payee +11) and COMMITs, as one atomic transaction. Atomicity means each *run* is clean: all-or-nothing. But a retry is a *second run*, and two clean runs move $22. Nothing in the transaction remembers it already happened. - **Scenario 2 — the transfer keyed by a request id.** `transfer_once(id, $11)` first INSERTs the client's `request_id` into a `requests` table with a `UNIQUE` constraint, *inside the same transaction* as the money move. The retry carries the *same* id; its INSERT fails the constraint (`sqlite3.IntegrityError`) before any money moves, the transaction rolls back, and the call is a no-op. The id, checked by the operation itself, is what makes the retry idempotent. Both live in one script, `code/retry_double_charge.py`, each against a fresh in-memory database seeded payer $100 / payee $0. ## Setup ``` cd study/ddia/ch13/code python3 retry_double_charge.py ``` Do **not** read the output yet — make each prediction first. ## Steps ### Step 1 — the atomic transfer, run once **Predict.** `transfer($11)` runs `UPDATE payer −11; UPDATE payee +11; COMMIT` against a ledger seeded payer $100, payee $0. After one call, what are the two balances? **Observe.** ``` -------------------------------------------------------------------- Scenario 1 -- one 'atomic' transfer, retried after a lost COMMIT ack -------------------------------------------------------------------- start: payer(alice)=100 payee(bob)= 0 after 1st transfer: payer(alice)= 89 payee(bob)= 11 ``` Payer $89, payee $11 — no surprise. One clean, all-or-nothing transfer. The surprise is what a *retry* does to it. ### Step 2 — the retry (the double-charge) **Predict.** The client never received the COMMIT ack, so it retries the identical `transfer($11)`. The reader's claim is "it's one ACID transaction, so retrying is safe." After the retry, what are the balances — and how much moved? **Observe.** ``` after retry: payer(alice)= 78 payee(bob)= 22 => moved $22 instead of $11: the retry DOUBLE-CHARGED. ``` **Payer $78, payee $22 — $22 moved, not $11.** The claim is wrong. The second transaction was just as atomic as the first, and atomicity has nothing to say about running it twice. TCP and the DB connection *do* suppress duplicates — but only within one connection; a client-level retry is a brand-new, fully valid transaction. Nothing errored. The ledger is simply $11 short. ### Step 3 — the fix: first attempt with a request id **Predict.** New ledger (payer $100, payee $0), plus a `requests` table with `UNIQUE(request_id)`. `transfer_once(id, $11)` INSERTs the id and moves the money in one transaction. On the *first* attempt with id `req-6f1a2b3c-0001`, what does it return and what are the balances? **Observe.** ``` -------------------------------------------------------------------- Scenario 2 -- same transfer keyed by a client-generated request id -------------------------------------------------------------------- start: payer(alice)=100 payee(bob)= 0 request id: req-6f1a2b3c-0001 1st attempt applied=True: payer(alice)= 89 payee(bob)= 11 ``` `applied=True`, payer $89, payee $11 — the id was new, so the INSERT succeeded and the transfer went through exactly like Scenario 1's first call. ### Step 4 — the retry, deduplicated **Predict.** The retry carries the **same** id `req-6f1a2b3c-0001`. Its first act is to INSERT that id — which already exists under a `UNIQUE` constraint. What happens to the INSERT, and what are the final balances? **Observe.** ``` caught sqlite3.IntegrityError: UNIQUE constraint failed: requests.request_id retry applied=False: payer(alice)= 89 payee(bob)= 11 => moved $11 exactly once: the retry was recognized and skipped. ``` **`applied=False`, balances unchanged at $89 / $11 — exactly $11 moved.** The duplicate INSERT raised `sqlite3.IntegrityError` *before* any UPDATE took effect; the transaction rolled back and the call became a no-op. The operation recognized its own retry and skipped it. ## What you should see - One transfer moves $11: payer **89**, payee **11**. - The retried "atomic" transfer moves **$22**: payer **78**, payee **22** — a silent double-charge, no error. - With a request id, the first attempt returns **True** and moves $11 (payer 89, payee 11); the retry raises **`sqlite3.IntegrityError`**, returns **False**, and leaves the balances **unchanged** — exactly once. ## Why The client cannot distinguish "COMMIT succeeded but the ack was lost in the network" from "COMMIT never happened." Those two states look identical from the outside — silence — yet demand opposite actions (do nothing vs. retry). A client that wants the transfer to *eventually* happen has no choice but to retry, which means the server must be prepared to receive the same request twice. This is at-least-once delivery, and it is the honest default for anything crossing a network. Atomicity doesn't help, because it is a property of a *single* transaction, not of the pair. Each run of `transfer()` is flawless — both UPDATEs commit together, never half. But "all-or-nothing, twice" is still two transfers. The bug isn't in the transaction; it's that the transaction has no memory of having run. TCP's sequence numbers and the database's own duplicate handling would catch a retransmitted packet or a replayed statement *on the same connection* — but the retry arrives on a new connection, as a request the lower layers have every reason to treat as new. Duplicate suppression at the transport layer stops at the connection boundary; the money move lives above it. That is exactly the end-to-end argument: a correctness property can only be guaranteed by the endpoints that fully understand it. Here the property is "apply this transfer at most once," and the only component that knows what "this transfer" means is the operation itself. So the fix pushes an identifier all the way down to it. The client generates a request id once, reuses it across every retry, and the server records it under a uniqueness constraint *in the same transaction* as the money move. Now the second attempt's INSERT collides, the transaction aborts before any balance changes, and atomicity — the very property that couldn't prevent the double-charge — is what guarantees the rolled-back retry leaves nothing behind. The id turns a non-idempotent operation into an idempotent one by giving it the one thing it lacked: a way to recognize itself. **The boundary — you only need this when a non-idempotent effect meets a retry.** Two escape hatches make it unnecessary. First, a *naturally idempotent* operation — `SET balance = 89` rather than `balance = balance - 11` — is safe to replay, because the second application writes the same value; no id required. Second, a call that is *never retried* can't double-apply, but on a real network you can't promise that. The double-charge needs both halves: an effect that compounds when repeated (a relative debit) *and* a delivery model that repeats it (at-least-once). Remove either and the request id buys you nothing. ### Go deeper 1. **Idempotency keys in payment APIs.** Stripe's `Idempotency-Key` header is this exact construction in production: the client sends a UUID, Stripe stores it, and a replay returns the original result instead of charging again. Trace how their docs describe the key's lifetime and scope, and compare it to the `UNIQUE(request_id)` row here. 2. **Exactly-once across shards.** One `UNIQUE` constraint works because the id and the money live in one database. When the debit and credit are on *different* shards, a single constraint can't span them — you need a two-phase construction (or a transactional outbox) to get the same at-most-once guarantee. That is a sibling Ch. 13 exercise.