🔥 The problem
The client sends a POST /payments, the network drops before the response arrives, and the client retries. The server had already charged the customer. Result: two charges for a single purchase.
This isn't a rare case: a timeout, a double click, or an HTTP client's automatic retry can cause it. And POST is not idempotent by definition, so the protection is up to you.
The idea: the client generates a unique Idempotency-Key per operation and sends it in the header. If the same key arrives again, you don't repeat the work: you return the saved response.
To test it, I simulated 50 simultaneous retries against the endpoint. Without idempotency: 50 records. With it: 1. Everything you need to reproduce it is below.
🧱 Step 1: the tables
The key piece is a unique constraint. Let the database enforce it, not your code: an if in the application doesn't hold up under concurrency.
CREATE TABLE payments (
id BIGSERIAL PRIMARY KEY,
amount_cents INTEGER NOT NULL,
currency CHAR(3) NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TABLE idempotency_keys (
key TEXT PRIMARY KEY,
request_hash TEXT NOT NULL,
response JSONB,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
request_hash detects when someone reuses the same key with a different body. That's a client error and shouldn't be ignored.
⚙️ Step 2: the endpoint
Example with FastAPI and psycopg 3. Everything happens in a single transaction: reserve the key, create the payment, and store the response.
import hashlib, os
from fastapi import FastAPI, Header, HTTPException
from fastapi.responses import JSONResponse
from psycopg.types.json import Jsonb
from psycopg_pool import ConnectionPool
from pydantic import BaseModel
pool = ConnectionPool(os.environ["DATABASE_URL"], min_size=5, max_size=20, open=True)
app = FastAPI()
class PaymentIn(BaseModel):
amount_cents: int
currency: str
@app.post("/payments")
def create_payment(body: PaymentIn, idempotency_key: str = Header(...)):
req_hash = hashlib.sha256(body.model_dump_json().encode()).hexdigest()
with pool.connection() as conn: # commit al salir, rollback si hay excepción
reserved = conn.execute(
"INSERT INTO idempotency_keys (key, request_hash) "
"VALUES (%s, %s) ON CONFLICT (key) DO NOTHING RETURNING key",
(idempotency_key, req_hash),
).fetchone()
if reserved is None: # la clave ya existía
saved_hash, saved_response = conn.execute(
"SELECT request_hash, response FROM idempotency_keys WHERE key = %s",
(idempotency_key,),
).fetchone()
if saved_hash != req_hash:
raise HTTPException(422, "Idempotency-Key reutilizada con otro cuerpo")
return JSONResponse(saved_response, status_code=200,
headers={"Idempotent-Replayed": "true"})
payment_id = conn.execute(
"INSERT INTO payments (amount_cents, currency) VALUES (%s, %s) RETURNING id",
(body.amount_cents, body.currency),
).fetchone()[0]
response = {"id": payment_id, "status": "created"}
conn.execute(
"UPDATE idempotency_keys SET response = %s WHERE key = %s",
(Jsonb(response), idempotency_key),
)
return JSONResponse(response, status_code=201)
Why does it work under concurrency? If two transactions insert the same key, the second one blocks until the first finishes. When the first commits, the second one's ON CONFLICT DO NOTHING returns no row, reads the stored response, and replays it. No "in progress" state or manual locks are needed.
🧪 Step 3: 50 simultaneous retries
import sys, uuid, concurrent.futures as cf
import httpx
URL = "http://localhost:8000/payments"
BODY = {"amount_cents": 4999, "currency": "USD"}
mode = sys.argv[1] # "same" = misma clave, "unique" = una clave por request
fixed = str(uuid.uuid4())
def pay(_):
key = fixed if mode == "same" else str(uuid.uuid4())
return httpx.post(URL, json=BODY, headers={"Idempotency-Key": key}, timeout=15).status_code
with cf.ThreadPoolExecutor(max_workers=50) as ex:
codes = list(ex.map(pay, range(50)))
print({c: codes.count(c) for c in set(codes)})
Run the server with uvicorn app:app, then:
python stress.py unique # control: sin idempotencia efectiva
python stress.py same # 50 reintentos con la misma clave
psql "$DATABASE_URL" -c "SELECT count(*) FROM payments;"
What you should see:
-
unique: 50201responses and 50 rows inpayments. This is the unprotected scenario. - After clearing the table (
TRUNCATE payments, idempotency_keys;),same: one201, 49200s, andcount(*) = 1.
⚠️ Details people forget
- Expiration. Delete old keys with a job (24 to 72 hours is usually enough), or the table grows forever.
- Same key, different body. Return 422, as in the code.
- External side effects. If you call a payment provider inside the transaction, pass your key to it too. The database can't undo a charge that was already made outside it.
- Errors. If it fails before the commit, the key isn't reserved and the client can retry without problems.
✅ Result
With one table, one primary key, and one transaction, you go from 50 payments to 1 under simultaneous retries. No queues, no distributed locks, and behavior you can verify with a 15-line script.
The underlying lesson: in distributed systems, assume every message can arrive twice, and design for that from the first endpoint.``
Top comments (2)
Letting the database constraint enforce the claim instead of an application-level check is the whole ballgame — your 50-simultaneous-retries test is exactly the case where the if-check version quietly produces two records. The request_hash detail deserves more attention than it usually gets: the same key arriving with a different body is not a retry, it is a client bug, and surfacing it beats silently returning the first response. Returning the saved response for a true retry also matters more than it looks — the client needs the original outcome, not a generic success, to reconcile its own state.
— Chris, founder of SignalEDI
Thanks, Chris. That's exactly the distinction I wanted to highlight.
The database constraint handles the concurrency guarantee, while request_hash ensures we're not silently treating a different operation as a retry.
I also agree that replaying the original response is essential. Idempotency isn't just about preventing duplicate writes; it's about giving the client a consistent result even when the network fails.
Your point about treating key reuse with a different payload as a client error is particularly important. It's an edge case that's easy to overlook when focusing only on duplicate prevention.