A reader pointed out a hole in my last post about idempotency keys, and they were right. This is the follow-up with the fix.

The problem

The simple pattern is: look up the key, run the work, save the response. It breaks in one spot:

def handle(key, payload): row = db.get(key) if row: return row.response result = do_work(payload) # side effect happens here  db.insert(key, result) # process dies before this line?  return result

If the process dies after do_work() but before insert, the retry sees no row and repeats the side effect. A customer is charged twice.

Fix 1: write a pending row first 

import sqlite3, time, json db = sqlite3.connect("idem.db", isolation_level=None) db.execute("""CREATE TABLE IF NOT EXISTS idem ( key TEXT PRIMARY KEY, fingerprint TEXT, status TEXT, response TEXT, created REAL)""") def begin(key, fingerprint): try: db.execute("INSERT INTO idem VALUES (?,?,?,?,?)", (key, fingerprint, "pending", None, time.time())) return "new", None except sqlite3.IntegrityError: fp, status, resp, created = db.execute( "SELECT fingerprint,status,response,created FROM idem WHERE key=?", (key,)).fetchone() if fp != fingerprint: return "mismatch", None # return HTTP 422  if status == "done": return "done", json.loads(resp) return "pending", created

The primary key makes the insert atomic, so two concurrent requests cannot both start the work.

Fix 2: store a request fingerprint

Hash the payload (hashlib.sha256(json.dumps(payload, sort_keys=True).encode())). If the same key arrives with a different payload, reject it with 422. This is how Stripe documents its idempotency behavior: https://docs.stripe.com/api/idempotent_requests

Fix 3: recover stale pending rows

A pending row older than your timeout means a crash. Do not blindly redo the work. Ask the downstream provider what happened, using the same key you passed to it:

STALE = 60 # seconds  state, info = begin(key, fp) if state == "pending" and time.time() - info > STALE: outcome = provider.lookup(key) # e.g. search by idempotency key  if outcome is None: result = do_work(payload, idempotency_key=key) else: result = outcome

The most important rule: pass the same key to the payment or email provider, so the side effect itself is idempotent downstream. Your local table is then a cache, not the only line of defense.

Checklist

  1. Insert a pending row before the side effect.
  2. Store a payload fingerprint, reject mismatches with 422.
  3. Forward the key to the downstream API.
  4. Treat old pending rows as unknown, and check with the provider.

I do not have a perfect answer for what to do when the provider has no lookup endpoint. Do you reconcile stale pending keys in a background job, or on the retry path? What do you do when the provider cannot tell you the outcome?

This article was written with AI assistance and checked by the author.