Idempotency keys for database-local charges
A stable request key lets the application detect a repeated logical operation. This PostgreSQL READ COMMITTED scenario stores the payment key and subtracts from an account in the same transaction. It asserts one subtraction for each of two keys, including a duplicate that arrives before the original commits. The charge is a database balance change, not a payment-provider call.
The client sends 'charge $30' with idempotency key req-42. Server A processes it.
A> BEGIN;
BEGIN
A> INSERT INTO payments VALUES ('req-42', 30)
ON CONFLICT (idempotency_key) DO NOTHING
RETURNING idempotency_key; -- a row came back: this key is new — do the work
idempotency_key
-----------------
req-42
(1 row)
A> UPDATE accounts SET balance = balance - 30 WHERE id = 1;
UPDATE 1
A> COMMIT;
COMMITB repeats the same key after commit. No response loss or network fault is injected.
B> BEGIN;
BEGIN
B> INSERT INTO payments VALUES ('req-42', 30)
ON CONFLICT (idempotency_key) DO NOTHING
RETURNING idempotency_key; -- 0 rows for this retained key: skip the balance change and read the stored amount
INSERT 0 0
B> SELECT amount FROM payments WHERE idempotency_key = 'req-42';
amount
--------
30
(1 row)
B> COMMIT;
COMMIT
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;
BEGIN
A> INSERT INTO payments VALUES ('req-99', 25)
ON CONFLICT (idempotency_key) DO NOTHING
RETURNING idempotency_key;
idempotency_key
-----------------
req-99
(1 row)
A> UPDATE accounts SET balance = balance - 25 WHERE id = 1;
UPDATE 1
B> INSERT INTO payments VALUES ('req-99', 25)
ON CONFLICT (idempotency_key) DO NOTHING
RETURNING idempotency_key;
⏳ 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;
COMMIT
⏵ B resumes:
INSERT 0 0This in-flight duplicate inserted no row. The next assertion checks database-local charges only.
A> SELECT balance FROM accounts WHERE id = 1; -- 100 - 30 - 25: every charge applied exactly once
balance
---------
45
(1 row)Verified against PostgreSQL 18.6 · Run it yourself · Scenario source
req-42 subtracts 30; its later duplicate inserts no row and reads the stored amount. For req-99, the duplicate waits for A's commit and then inserts no row. The final balance is 45, from 100 - 30 - 25. These are Demonstrated behaviors, not an exactly-once network-delivery guarantee. No response is suppressed and no network failure is injected in this schedule.
The in-flight retry
The INSERT contract says DO NOTHING avoids the conflicting insertion and RETURNING returns rows actually inserted or updated. At READ COMMITTED a concurrent conflict can prevent insertion even if the conflicting row was not visible to that statement's snapshot, as the isolation manual explains. A subsequent statement can read the committed key. Other isolation levels and other errors need their own recovery handling.
† With a retained unique key, a stable key per operation, and every participating writer performing the gate and the database-local work in the same transaction, only the successful insertion authorizes that work. Atomicity commits or rolls back the two together. This is an Entailed guarantee from the unique-constraint and transaction contracts, not a transcript of every retry or failure path. Treat zero returned rows as a conflict to inspect, not proof that an arbitrary request with that key has identical parameters. Define key retention, payload validation, and stored response handling for your application.
External effects need their own protection. The outbox stores event intent in the same database transaction; it does not make a remote charge atomic.
Further reading
The reconstructed support assessment records the exact manual support and execution limits for this lesson.