study › DDIA › Chapter 13 · A Philosophy of Streaming Systems

Retrying an "Atomic" Transfer Charges $22, Not $11

A reader predicts "the transfer is a single ACID transaction, so retrying after a timed-out COMMIT is safe." Run it on sqlite3 and the retry moves $22, not $11 — atomicity is not idempotence. Add a requests table with UNIQUE(request_id) and pass a client-generated id end-to-end, and the retry hits the constraint, rolls back, and the transfer happens exactly once.

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.

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.
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.
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.
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.
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. 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 these numbers came from Deterministic, so the balances reproduce exactly. Captured on 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). 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.

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?

What are the balances after one transfer?

-------------------------------------------------------------------- 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?

What does the retry do to the ledger?

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?

What does the first guarded attempt do?

-------------------------------------------------------------------- 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?

What happens when the same id is retried?

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

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.
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.

Sources: DDIA 2e, Ch. 13, §"The end-to-end argument for databases", §"Duplicate suppression", §"Uniquely identifying requests" (Examples 13-1, 13-2) · Saltzer, Reed & Clark, "End-to-End Arguments in System Design" (1984).