Lesson 11: Idempotency & Safe Retries

Lesson 10 concluded that the strongest way to avoid a lock is to make repetition harmless. This lesson is how you do that — and it is also the answer to a problem every distributed system has to face: when a client receives a timeout, it cannot know whether the request was processed.

This lesson's lab fires 78,774 concurrent requests carrying the same idempotency key at a charging endpoint. The result: exactly one row in the database, exactly one 201 Created, and the balance debited exactly once. Turn idempotency off and run the same load: 14,236 rows, 1,423,600 debited — an account 423,600 in the red.

11.1 Why "exactly-once" is an illusion

A client sends a charge request. It waits, and receives a timeout. The question: did the server charge the money or not? The client has no way to know. The following three situations produce exactly the same experience on the client side:

This is the Two Generals problem: no protocol lets two parties reach absolute certainty that a message arrived, when the channel can lose messages. The consequence: exactly-once delivery is impossible.

But there is a combination that achieves what people actually want: at-least-once delivery + idempotent processing. Keep resending until you are sure it arrived (at-least-once), and design things so that receiving it many times has the effect of receiving it once. From the outside the result looks like exactly-once, even though no step in it is exactly-once.

11.2 Idempotency keys and the dedup table

The mechanism is compact: the client generates a unique key for its intent — not for each attempt. The server stores the key → result mapping in a dedup table (dedup = deduplication: a table whose only job is to remember "this key has been processed, and here is the result"). On seeing a key it already has, the server returns the stored result instead of processing again.

11.3 The dedup write and the business logic must be in the SAME transaction

This is the most important section of the lesson. The most intuitive implementation — and the wrong one — looks like this:

// ===== WRONG: check-then-act — the classic race condition =====
const seen = await db.query(`SELECT * FROM charges WHERE idem_key = $1`, [key]);
if (seen.rows.length) return seen.rows[0].response;   // (1) check

await db.query(`UPDATE balances SET balance = balance - $1 WHERE id = 1`, [amount]);
await db.query(`INSERT INTO charges (idem_key, response) VALUES ($1, $2)`,
  [key, resp]); // (2) write

// The window between (1) and (2): TWO concurrent requests both see "not there" at (1),
// so BOTH proceed and BOTH charge. Exactly what we set out to prevent.

The fix is not to add a lock (see Lesson 10) but to let the database enforce uniqueness itself, via a UNIQUE constraint, and to do both jobs in one statement:

-- CTE: `ins` tries to write the dedup row. ON CONFLICT DO NOTHING means that if the
-- key already exists nothing is written and `ins` is EMPTY. `upd` only debits the
-- balance WHEN `ins` produced a row — so only the FIRST time has a business effect.
-- The final SELECT returns either the new result (created=true) or the STORED one.
--
-- It is ONE statement => Postgres runs it in an implicit transaction => there is NO
-- window for two concurrent requests to slip through.
WITH ins AS (
  INSERT INTO charges (idem_key, amount, response)
  VALUES ('order-999', 100, 'charge-ok-100')
  ON CONFLICT (idem_key) DO NOTHING
  RETURNING id, amount, response
), upd AS (
  UPDATE balances SET balance = balance - 100
  WHERE id = 1 AND EXISTS (SELECT 1 FROM ins)
  RETURNING balance
)
SELECT id, amount, response, 'true' AS created FROM ins
UNION ALL
SELECT c.id, c.amount, c.response, 'false' FROM charges c
WHERE c.idem_key = 'order-999' AND NOT EXISTS (SELECT 1 FROM ins);

The charges table has idem_key TEXT NOT NULL UNIQUE. That constraint is what does the real work: it turns duplicate prevention from a piece of code that can be wrong into an invariant the database will not allow to be violated.

Measured for real: 78,774 concurrent requests on one key

64 connections, 10 seconds, one and the same idem_key, against the same load without an idempotency key:

The "status codes" row is the most compact evidence: out of 78,774 concurrent requests all aimed at one key, exactly one received 201 Created. Every other one received 200 with the stored result itself — the same chargeId, the same response. No request failed (0 errors).

11.4 HTTP semantics, and two properties that get conflated

Two properties get conflated constantly, and they are independent of each other:

Note that the last two rows are opposites: SET is idempotent but not commutative; += 1 is commutative but not idempotent. In a distributed system you usually need both, and the way to get there is addition plus deduplication — += 1 for commutativity, the dedup table for idempotency. This also explains the hint from Lesson 9 section 9.5: balance = balance + 50 merges cleanly while SET balance = 150 does not.

11.5 Idempotency at the data layer and for consumers

You do not always need a separate dedup table. Often idempotency can live in the way you design the data itself:

Reproduce the measurements yourself

cd blog/sysdesign/sysdesign-lab
docker compose --profile replica up -d      # PostgreSQL is needed

psql_() { docker compose exec -T postgres psql -U lab -d lab -tAc "$1"; }
reset() { psql_ "TRUNCATE charges; UPDATE balances SET balance = 1000000 WHERE id = 1;"; }

# --- WITH an idempotency key: fire THE SAME key every time ---
reset
docker compose run --rm loadgen loadgen.js \
  --url "http://lb:8080/charge?key=order-999&amount=100&idem=1" -c 64 -d 10 -w 2 --json
psql_ "SELECT count(*) AS rows FROM charges;"               # 1
psql_ "SELECT balance FROM balances WHERE id=1;"            # 999900  (debited exactly 100)

# --- WITHOUT an idempotency key: same load, same duration ---
reset
docker compose run --rm loadgen loadgen.js \
  --url "http://lb:8080/charge?amount=100&idem=0" -c 64 -d 10 -w 2 --json
psql_ "SELECT count(*) AS rows FROM charges;"               # 14236
psql_ "SELECT balance FROM balances WHERE id=1;"            # -423600  (!)

# Look closely at the statusCodes field of the idempotent run: {"200":78773,"201":1}
# Exactly ONE request received 201 Created out of 78,774 concurrent requests.

In summary

Exactly-once delivery is impossible — the Two Generals problem says a client that receives a timeout cannot distinguish three different situations. What is possible, and what people actually want, is at-least-once + idempotent processing, and from the outside that combination looks like exactly-once.

An idempotency key must be a key for the intent, generated by the client and kept unchanged across every attempt. The server must store the complete result rather than just a "processed" flag, and return that same result when it meets a duplicate key.

The most important part is section 11.3: the dedup write and the business logic must be in the same transaction. No ordering is safe once you split them — dedup first and the request vanishes silently; business logic first and the retry charges again. And do not use the "check then write" pattern: two concurrent requests both see "not there". The cheapest and most certain approach is to let a UNIQUE constraint enforce uniqueness, in a single statement.

Measured: 78,774 concurrent requests on one key → exactly 1 row, exactly 1 status 201, exactly 100 debited, and not a single error. With idempotency off: 14,236 rows and a balance of −423,600.

Lesson 12 is where all of the above stops being a choice and becomes a requirement: message queues with at-least-once semantics. When a job can be redelivered at any moment, the consumer must be idempotent — otherwise every redelivery is another side effect.