Idempotency series: 1. Concepts, APIs and the database · 2. Kafka and payment-gateway IPNs
At every hop of a distributed system, a sender that gets no acknowledgement sends again, so the same piece of work can arrive two or three times (at-least-once). Idempotency is the property that makes “arrived several times” behave like “arrived once”. By the end you’ll be able to choose how to deduplicate at the API and database layers, and build an Idempotency-Key that survives concurrent requests.
Part 2 covers Kafka and payment-gateway IPNs. The API contract (headers, status codes) is in Async jobs, Idempotency-Key and API docs.
1. Why there is no free “exactly-once”
Key idea: the sender can’t know whether the request or only the response was lost, so it has to retry, and retries can duplicate. The only way out is for the receiver to recognise the second attempt and not redo it.
| Guarantee | How | Consequence |
|---|---|---|
| At-most-once | Send once, never retry | Can be lost |
| At-least-once | Retry until acknowledged | Can be duplicated |
| Exactly-once | At-least-once + receiver-side deduplication | You have to build it |
sequenceDiagram
participant C as Client
participant S as Payment service
participant D as Database
C->>S: POST /payments (attempt 1)
S->>D: debit, COMMIT
S--xC: response lost (timeout)
Note over C: Was the account debited or not?
C->>S: POST /payments (retry)
S->>D: debit again?
That’s why people say “exactly-once processing” (or effectively-once) rather than “exactly-once delivery”.
On the sending side, how to retry without causing a retry storm (retry only transient errors, cap the attempts, back off with jitter, open a circuit breaker once the other side is clearly down) is in Timeouts, retries and circuit breakers. From the protocol angle, synchronous communication with REST and gRPC uses the same delivery-guarantee table and enables gRPC retries only for idempotent methods.
Retries from the reverse proxy, and the limits of Kafka’s exactly-once
Reverse proxies retry too: Nginx with proxy_next_upstream passes a request to the next backend when the first one fails to connect or times out. Since 1.9.13 it won’t retry a POST/PATCH that was already sent.
But if someone enables proxy_next_upstream error timeout non_idempotent;, a POST /orders that already wrote to the database and only timed out while sending the response gets sent again and creates a second order, without the client ever retrying. Details, and how to cap the number of tries, are in Reverse proxying with Nginx.
2. Three ways to make an operation idempotent
Key idea: write “set the target state” instead of “add to it”; let the database’s unique constraint referee; or record “processed” in a dedupe table in the same transaction as the business change.
2.1. Naturally idempotent: write “set the value”, not “change the value”
-- Not idempotent: running it again adds the money again
UPDATE account SET balance = balance + 100000 WHERE id = 'A';
-- Idempotent: set the target state, guarded by the current state
UPDATE booking SET status = 'PAID', paid_at = now()
WHERE id = 981 AND status = 'PENDING_PAYMENT';
-- Second run: 0 rows affected, nothing changes
The second statement is a conditional update driven by a state machine (it only allows PENDING_PAYMENT → PAID). The affected row count (0 or 1) tells you whether this was the first time.
2.2. Unique constraints and upserts
The database sees every concurrent request, so let it referee. An upsert means “insert, and if the row already exists do something else (here: nothing)”.
-- PostgreSQL
INSERT INTO booking (user_id, request_id, homestay_id, checkin, checkout)
VALUES (42, 'b1c9...', 7, '2026-12-20', '2026-12-23')
ON CONFLICT (user_id, request_id) DO NOTHING
RETURNING id; -- no row returned means it already existed
-- MySQL
INSERT INTO booking (user_id, request_id, homestay_id, checkin, checkout)
VALUES (42, 'b1c9...', 7, '2026-12-20', '2026-12-23')
ON DUPLICATE KEY UPDATE id = id; -- no-op, affected rows = 0 if it already existed
MySQL: why not to use INSERT IGNORE to skip duplicates
The cart and checkout post uses exactly this for the order button: a unique (user_id, request_id) on orders blocks double clicks, in the same transaction as the stock decrement.
2.3. A deduplication store
With no natural key, record “already processed” in a separate table: idempotency keys for APIs, processed_message for consumers. The dedupe row and the business change must be written in the same transaction, otherwise there’s a gap. A short-lived variant appears in the Discord-style chat design: the client attaches a nonce to each message, the server remembers (user_id, nonce) → message_id for a few minutes, and a resend returns the existing message instead of creating a new one.
3. An Idempotency-Key at the API: implementation and test run
Key idea: in one transaction: claim the key with INSERT ... ON CONFLICT DO NOTHING → run the business logic → store the response on the same row. If the key already exists, return the stored response, or 422 if the body differs.
The same need shows up in the URL shortener design: the POST that creates a link needs an idempotency key so one click doesn’t create two links.
The key table (PostgreSQL):
CREATE TABLE idempotency_record (
client_id text NOT NULL,
idem_key text NOT NULL,
request_hash char(64) NOT NULL, -- SHA-256 of the canonicalised body
status text NOT NULL CHECK (status IN ('IN_PROGRESS', 'COMPLETED')),
http_status int,
response_body text, -- text, not jsonb (see the pitfall below)
created_at timestamptz NOT NULL DEFAULT now(),
expires_at timestamptz NOT NULL DEFAULT now() + interval '24 hours',
PRIMARY KEY (client_id, idem_key)
);
CREATE INDEX idempotency_record_expires ON idempotency_record (expires_at); -- for the cleanup job
The handler’s three branches:
- Key claimed (1 row inserted): run the business logic, store
http_status+response_body, markCOMPLETED, commit. - Key exists, same hash: a replay, return the original response.
- Key exists, different hash: the client misused the key, return
422 IDEMPOTENCY_KEY_REUSED.
A business failure (insufficient balance) is an outcome too, and gets stored. An infrastructure failure rolls back, the key goes with it, and a retry starts fresh.
Test run: 20 requests with the same key at once created a single payment, and all twenty got the same response.
Full Java code and the 20-concurrent-request run
The core (plain JDBC so it’s easy to run; in Spring it’s an @Transactional method using JdbcTemplate):
ApiResponse pay(String clientId, String key, PaymentRequest req) throws Exception {
String hash = sha256(canonicalJson(req));
try (Connection c = dataSource.getConnection()) {
c.setAutoCommit(false);
try {
int claimed = update(c, """
INSERT INTO idempotency_record (client_id, idem_key, request_hash, status)
VALUES (?, ?, ?, 'IN_PROGRESS')
ON CONFLICT (client_id, idem_key) DO NOTHING""", clientId, key, hash);
// If another transaction holds the same key, this INSERT WAITS until it commits/rolls back
if (claimed == 0) { // key exists: a replay or a misused key
StoredRecord r = findRecord(c, clientId, key);
c.commit();
if (!r.requestHash().equals(hash)) return new ApiResponse(422, "{\"error\":\"IDEMPOTENCY_KEY_REUSED\"}");
return new ApiResponse(r.httpStatus(), r.responseBody()); // return the original response
}
ApiResponse result;
int debited = update(c, "UPDATE account SET balance = balance - ? WHERE id = ? AND balance >= ?",
req.amount(), req.from(), req.amount());
if (debited == 0) {
result = new ApiResponse(422, "{\"error\":\"INSUFFICIENT_BALANCE\"}"); // a business outcome: stored
} else {
update(c, "UPDATE account SET balance = balance + ? WHERE id = ?", req.amount(), req.to());
long paymentId = insertPayment(c, req);
result = new ApiResponse(201, "{\"payment_id\":" + paymentId + "}");
}
update(c, """
UPDATE idempotency_record SET status = 'COMPLETED', http_status = ?, response_body = ?
WHERE client_id = ? AND idem_key = ?""", result.status(), result.body(), clientId, key);
c.commit();
return result;
} catch (Exception e) {
c.rollback(); // infrastructure failure: the key disappears too, so a retry starts fresh
throw e;
}
}
}
The full version ran on Java 21, PostgreSQL 16, driver 42.7. Account A held 1,000,000; 20 requests with the same key fired at once on virtual threads, each transferring 300,000:
distinct responses = [ApiResponse[status=201, body={"payment_id":1}]]
same key, other amount -> ApiResponse[status=422, body={"error":"IDEMPOTENCY_KEY_REUSED"}]
new key -> ApiResponse[status=201, body={"payment_id":2}]
payments = 2, balance A = 400000
Twenty requests created a single payment, and all twenty got the same response. Reusing the key with a different amount was rejected; a new key created the second payment. The final balance of 400,000 is exactly two debits.
4. Case study: duplicate bookings because request_id isn’t UNIQUE
Key idea: the table has a request_id column but no unique constraint, so a retry of a booking that already succeeded runs the whole flow again. The fix is UNIQUE (user_id, request_id), claimed before locking the rooms.
4.1. Context
The course’s sample homestay project has POST /api/v1/bookings whose body carries a request_id. The booking table has a request_id text NOT NULL column, but no unique constraint. The booking flow, in one transaction: SELECT ... FOR UPDATE the homestay_availability rows (one per night) still AVAILABLE; if there are enough nights, flip them to BOOKED and insert the booking.
4.2. What happens when the client retries
Attempt 1 commits but its response is lost. Attempt 2 arrives with the same request_id:
- Nothing checks
request_id, so attempt 2 runs the whole flow again. - The nights are already
BOOKED, so theSELECT ... FOR UPDATEfiltered onstatus = 'AVAILABLE'returns fewer rows than nights: the service answers HOMESTAY_BUSY. - The user sees “this place is already booked” right after booking it. They book elsewhere: two real bookings, one unintended charge.
The pessimistic lock did prevent double-booking the same nights, so the bug shows up as a wrong error for an operation that succeeded. For non-exclusive resources (orders, wallet top-ups), the same hole creates duplicate rows directly.
4.3. The fix
ALTER TABLE booking ADD CONSTRAINT booking_user_request_uq UNIQUE (user_id, request_id);
At the start of the transaction, INSERT ... ON CONFLICT (user_id, request_id) DO NOTHING RETURNING id. If no row comes back, return the existing booking as if the first attempt had just succeeded (same booking_id, 201 or 200). Claim the request_id before locking availability, so a retry never reaches the “is it still free?” logic.
The same code also sends the email and the Kafka event inside the booking transaction (fixed with a transactional outbox).
A related bug: email and Kafka event inside the transaction, fixed with an outbox
The email and the event go out inside the transaction. If the commit fails, the “booking confirmed” email is already out; if the process crashes after the commit, the event is lost. The fix is a transactional outbox: write the event to an outbox table in the same transaction, and have a separate process read and publish it. An outbox is at-least-once, so downstream consumers must be idempotent (see part 2).
Code review: two worked reviews reviews this same flow and writes the bugs above (no UNIQUE on request_id, email and events inside the transaction) as labelled review comments, each with its consequence and a suggested fix.
5. Locks don’t replace idempotency
Key idea: a lock only prevents two runs at the same time; a retry that arrives after the first attempt released the lock still redoes everything. Idempotency needs a durable record that the work is done.
The Redis lock code in the course repo uses SET key 1 NX PX ttl:
Boolean ok = redisTemplate.opsForValue().setIfAbsent(lockKey, "1", ttl); // constant value "1"
// ... do the work ...
redisTemplate.delete(lockKey); // DEL without checking the owner
Exercises
Exercise 1: Wallet top-ups via a bank webhook
Task (paraphrased): the bank calls a POST /topups webhook whenever money comes in; it may call again several times, occasionally twice almost simultaneously. Design the table and handler so the wallet is credited exactly once.
Solution to Exercise 1: a topup table keyed by bank_txn_id
CREATE TABLE topup (
bank_txn_id text PRIMARY KEY, -- the bank's transaction id: a natural key
wallet_id bigint NOT NULL,
amount bigint NOT NULL CHECK (amount > 0),
received_at timestamptz NOT NULL DEFAULT now()
);
-- In one transaction:
INSERT INTO topup (bank_txn_id, wallet_id, amount) VALUES (:txn, :wallet, :amount)
ON CONFLICT (bank_txn_id) DO NOTHING;
-- only if the INSERT returned 1 row:
UPDATE wallet SET balance = balance + :amount WHERE id = :wallet;
COMMIT;
A second webhook with the same bank_txn_id waits on the unique index, then gets 0 rows, skips the credit and still returns 200.
A senior would add: verify the webhook signature, check that amount matches on a duplicate (a mismatch is an incident worth alerting on, not something to skip silently), and reconcile against the bank statement.
Remember
- Explain why retries are mandatory, and why you get exactly-once processing but not exactly-once delivery.
- Know when to use a conditional update, a unique constraint or a dedupe table, and the “same transaction” rule.
- Redraw the three branches of an Idempotency-Key handler, and explain why concurrent duplicates wait on the unique index.
- Know when you must switch to two phases
IN_PROGRESS→COMPLETED, and what it costs. - Explain why a distributed lock can’t replace idempotency.
Interview questions
Junior
What is idempotency and why do distributed systems need it?
An idempotent operation reaches the same final state whether it runs once or many times; distributed systems need it so retries are safe.
A sender can’t distinguish “the request was lost” from “the response was lost”, so it has to retry, and retries make every channel at-least-once. An idempotent receiver makes those retries harmless.
Why isn’t checking “does it exist yet?” in code before inserting enough to prevent duplicates?
Because it’s a check-then-act race: two concurrent requests both see “not there” and both insert.
Only a unique constraint in the database sees every parallel request and can stop the second row.
Mid
What’s the difference between at-most-once, at-least-once and exactly-once?
At-most-once can lose, at-least-once can duplicate, and exactly-once is really at-least-once plus receiver-side deduplication.
At-most-once: no retries. At-least-once: retry until acknowledged. Exactly-once delivery isn’t achievable over an unreliable network; what you can build is exactly-once processing (idempotent consumers, unique constraints, a dedupe store).
Can a distributed lock replace idempotency?
No. A lock only prevents two runs at the same time; a retry that arrives after the first attempt released the lock still runs again.
The lock can also expire before the first attempt finishes. Idempotency needs a durable record that the work was done (a unique constraint, a dedupe table); a lock is only an optimisation to reduce contention.
Senior
When do you claim the idempotency key in one transaction, and when do you use two phases?
One transaction when the whole operation lives in one database and is short; two phases when there’s an external call.
- One transaction: key, change and response commit together, and concurrent duplicates simply wait on the unique index.
- Two phases (commit IN_PROGRESS, call out, commit COMPLETED): because you can’t keep a transaction open while waiting on HTTP. Handle stuck keys with an expiring lease, return 409 to in-flight duplicates, and pass the key down so the external call is itself idempotent.
Bug report: a user is told “this place is already booked” right after successfully booking it. How do you investigate?
I’d suspect a retry of a request that had already succeeded: the second attempt ran the whole flow and found the nights BOOKED by attempt 1.
Check the logs by request_id for two requests a few seconds apart, and check whether the table has a unique constraint on request_id. Fix: unique (user_id, request_id), claim it before locking availability, and return the existing booking to the repeat.
Useful phrases for interviews
- “Every hop retries on timeout, so I assume at-least-once everywhere and make the receiver idempotent.”
- “Exactly-once delivery isn’t really a thing; what we build is at-least-once delivery with deduplication.”
- “I’d rather let a unique constraint referee concurrent requests than rely on a check in application code.”
- “The dedupe row and the business change commit in the same transaction, otherwise there’s a window.”
- “A replay gets the original response back, byte for byte; a reused key with a different body gets a 422.”
- “Once there’s an outbound call, we switch to two phases and pass the same key down to the gateway.”
- “A lock stops concurrent runs, not repeated ones, so it can’t be our idempotency mechanism.”
- “The lock actually hid the bug: instead of a double booking, users got a wrong error for a booking that had succeeded.”
Notes based on the Advanced Backend course (Ronin Engineer), rewritten in my own words.