My idempotency key fix still double-charged after a crash. Here is the pending-row pattern that closes the gap
A reader pointed out a hole in my last post about idempotency keys, and they were right. My example saved the key after the work finished. If the process dies between "work done" and "result saved", the retry repeats the
A reader pointed out a hole in my last post about idempotency keys, and they were right. My example saved the key after the work finished. If the process dies between "work done" and "result saved", the retry repeats the side effect. Here is the fix, step by step.
This article was written with AI assistance and checked by the author.
The bug
def charge(key, amount):
row = db.execute("SELECT response FROM keys WHERE key=?", (key,)).fetchone()
if row:
return row[0]
result = do_work(amount) # money moves here
# <-- process crashes here: money moved, nothing recorded
db.execute("INSERT INTO keys VALUES (?, ?)", (key, result))
db.commit()
return result
A retry after the crash finds no row, so it calls do_work again. Two charges.
Fix 1: write a pending row first
Claim the key before doing the work. The PRIMARY KEY makes the claim atomic, so two concurrent requests cannot both win.
import sqlite3, time, hashlib, json
db = sqlite3.connect("keys.db")
db.execute("""CREATE TABLE IF NOT EXISTS keys (
key TEXT PRIMARY KEY,
fingerprint TEXT NOT NULL,
status TEXT NOT NULL, -- 'pending' or 'done'
response TEXT,
started_at REAL NOT NULL)""")
def fingerprint(payload):
return hashlib.sha256(json.dumps(payload, sort_keys=True).encode()).hexdigest()
def charge(key, payload):
fp = fingerprint(payload)
try:
db.execute("INSERT INTO keys VALUES (?,?,?,?,?)",
(key, fp, "pending", None, time.time()))
db.commit()
except sqlite3.IntegrityError:
status, saved_fp, response, started = db.execute(
"SELECT status, fingerprint, response, started_at FROM keys WHERE key=?",
(key,)).fetchone()
if saved_fp != fp:
raise ValueError("422: same key, different payload")
if status == "done":
return response
raise RuntimeError("409: still pending, check recovery rules below")
result = do_work(payload)
db.execute("UPDATE keys SET status='done', response=? WHERE key=?", (result, key))
db.commit()
return result
Fix 2: store a request fingerprint
If the same key arrives with a different body, that is a client bug. Reject it instead of returning the old answer. Stripe documents this behavior in its idempotent requests reference.
Fix 3: decide what a stale pending row means
A pending row can mean "still running" or "the process died". Two safe choices:
- Pass the same key downstream to the payment or email provider, so the provider dedupes the real side effect.
- If the pending row is older than your timeout, ask the provider what happened (look up the charge by key) instead of blindly redoing the work.
STALE_AFTER = 120 # seconds, longer than your slowest do_work
if status == "pending" and time.time() - started > STALE_AFTER:
outcome = provider_lookup(key) # your provider's "get by idempotency key"
# record the real outcome, do not call do_work again
What this still does not solve
If your side effect has no downstream key and no lookup, you cannot know whether it ran. In that case you need a reconciliation job or a manual review queue. No SQLite trick removes that.
Do you reconcile stale pending keys in a background job, or only when the client retries? I would like to hear which has worked better for you.
Originally published by Dev.to WebDev. Aggregated on AIWithGhost for educational purposes — full credit and traffic to the original publisher.