Idempotency keys: one database charge per retained key
A client can retry a request when it does not receive a response. This lesson demonstrates duplicate requests with the same key, not a network failure or delivery to a payment service. The protected effect is an InnoDB account update.
The client names its intent (req-42). The handler inserts that key and changes the balance in the same transaction. The demonstrated connection does not set CLIENT_FOUND_ROWS. The MySQL 8.4 manual describes a no-op upsert as "an existing row is set to its current values": it reports 0 affected rows, or 1 with CLIENT_FOUND_ROWS. A new insert reports 1 and a changed existing row reports 2. That connection setting is part of this recipe.
The client sends 'charge $30' with idempotency key req-42. Server A processes it.
A> BEGIN;
Query OK
A> INSERT INTO payments VALUES ('req-42', 30)
ON DUPLICATE KEY UPDATE amount = amount; -- 1 row inserted: this key is new — do the work
Query OK, 1 row affected
A> UPDATE accounts SET balance = balance - 30 WHERE id = 1;
Query OK, 1 row affected
A> COMMIT;
Query OKModel a same-key retry after commit. No network failure or application response is executed; B reads the stored amount.
B> BEGIN;
Query OK
B> INSERT INTO payments VALUES ('req-42', 30)
ON DUPLICATE KEY UPDATE amount = amount; -- 0 rows: existing key, skip the balance update; the next SELECT reads the stored amount
Query OK, 0 rows affected
B> SELECT amount FROM payments WHERE idempotency_key = 'req-42';
amount
--------
30
(1 row)
B> COMMIT;
Query OK
A> SELECT balance FROM accounts WHERE id = 1; -- charged exactly once
balance
---------
70
(1 row)The nasty case: the retry arrives while the original is still in flight, uncommitted.
A> BEGIN;
Query OK
A> INSERT INTO payments VALUES ('req-99', 25)
ON DUPLICATE KEY UPDATE amount = amount;
Query OK, 1 row affected
A> UPDATE accounts SET balance = balance - 25 WHERE id = 1;
Query OK, 1 row affected
B> INSERT INTO payments VALUES ('req-99', 25)
ON DUPLICATE KEY UPDATE amount = amount;
⏳ B is waiting for a lock…The unique index parks the retry until the original decides. A commits — the retry absorbs to 0 rows.
A> COMMIT;
Query OK
⏵ B resumes:
Query OK, 0 rows affectedThis in-flight duplicate affects 0 rows after A commits. No additional balance update is issued.
A> SELECT balance FROM accounts WHERE id = 1; -- 100 - 30 - 25: every charge applied exactly once
balance
---------
45
(1 row)Verified against MySQL 8.4.11 · Run it yourself · Scenario source
What the two schedules establish
After req-42 commits, B's duplicate affects 0 rows and reads the stored amount. The balance remains 70. For req-99, B's duplicate waits while A holds the uncommitted key. After A commits, B affects 0 rows; the final balance is 45. The scenario does not execute the branch where A rolls back, or assert an application response.
Entailed guarantee†: for this database-local recipe, a retained unique key and its protected balance change commit together. If every handler performs the balance change only after inserting a new key, competing uses of that key cannot commit another such change. This follows from the unique-index conflict handling and transaction boundary. † The two schedules above demonstrate particular executions, not all executions of that rule.
Keep keys for the required retry period, bind each key to one intent, and reject a changed payload. Store enough result data to answer a retry; amount here is only a sketch of that record. Payload validation, key expiry, and full response recovery are not executed here. Errors still need transaction cleanup and appropriate recovery. An email, broker publish, or remote charge is outside this transaction and is not made exactly-once by this key table. See the outbox boundary.