My Retry Wrote the Row Twice, and the Idempotency Key Was the Bug

Here is a failure shape I keep meeting in batch jobs: one input, two writes to the database, and a clean 200 on every log line I check. Which attempt actually did the work? Did the queue hand me the same job twice? Did my own retry fire while the first call was still in flight, so two workers raced to the same finish line?

None of those questions can be answered from timing alone, because a duplicate looks exactly like a success. So for 48 hours I stopped adding retries and started counting duplicates instead. I worked it in three passes: where a duplicate is born, how to fingerprint a unit of work, and how to prove the guard holds when I inject faults. These are the field notes from that pass, including the parts that broke.

Pass 1: Three places a duplicate is born

Before writing any code I listed the birthplaces, because each one needs a different fix. Skipping this step is how people end up with three half-guards that each cover the wrong layer.

1. The producer enqueues twice. A form submit, a cron overlap, or a webhook redelivery creates two jobs with the same payload and different job IDs.
2. The broker redelivers. Visibility timeouts, at-least-once queues, and worker crashes all cause the same job to be handed to a second worker mid-flight.
3. Your own retry wrapper fires. A timeout does not mean the server did nothing; it means you stopped waiting for the answer.

Only the third one lives in my code, and it was the one I had been "fixing" for months. That is the trap, isn't it? You patch the layer you can see and assume the other two are somebody else's problem.

Pass 2: Fingerprint the work, not the attempt

The worst bug in this whole exercise was mine, and it was one line long. My idempotency key was built from the job ID plus the attempt counter, which guaranteed that every retry looked like brand new work. Nothing deduplicated, ever, and the logs looked perfectly healthy.

The key has to describe the input, not the history. Here is the fingerprint function I settled on, which normalizes the payload so cosmetic differences do not create new work:

import hashlib, json def fingerprint(model: str, messages: list[dict], params: dict) -> str: payload = { "model": model, "messages": [{"role": m["role"], "content": m["content"]} for m in messages], "params": {k: params[k] for k in sorted(params) if k != "idempotency_key"}, } blob = json.dumps(payload, sort_keys=True, separators=(",", ":")).encode("utf-8") return hashlib.sha256(blob).hexdigest() 

The rules I follow for that function are boring and they matter:

• Include every field that changes the output, and exclude every field that changes between attempts (timestamps, attempt counters, request IDs).
• Normalize whitespace and key order before hashing, or the same prompt in two formats becomes two units of work.
• Hash the whole conversation, not just the last message, or a multi-turn job collapses into a single key by accident.
• Write the fingerprint to the log line, so the next duplicate is one grep away instead of one afternoon away.

A cache key is not a claim

I also had to separate two ideas that I had been conflating. A cache key answers "have I seen this input before, and can I reuse the output?" A claim answers "is someone working on this input right now, and should I back off?" Those are different questions with different lifetimes, and a single table serving both was the reason my stale entries kept resurrecting finished work.

Pass 3: A claim table with a state machine

The smallest guard that survives a crash is a table with a primary key on the fingerprint and a status column. The insert either wins or it does not, and the database does the arguing for you.

CREATE TABLE work_claims ( fingerprint TEXT PRIMARY KEY, state TEXT NOT NULL CHECK (state IN ('claimed','done','failed')), attempt INTEGER NOT NULL DEFAULT 1, claimed_at REAL NOT NULL, updated_at REAL NOT NULL ); 

The claim path is short, and every branch is explicit:

import sqlite3, time STALE_AFTER = 900 # seconds; must exceed your worst p99 call latency  def claim(db: sqlite3.Connection, fp: str) -> str: now = time.time() db.execute("BEGIN IMMEDIATE") try: row = db.execute( "SELECT state, claimed_at FROM work_claims WHERE fingerprint = ?", (fp,) ).fetchone() if row is None: db.execute( "INSERT INTO work_claims(fingerprint, state, attempt, claimed_at, updated_at)" " VALUES (?, 'claimed', 1, ?, ?)", (fp, now, now) ) db.execute("COMMIT") return "claimed" state, claimed_at = row if state == "done": db.execute("COMMIT") return "skip" if state == "claimed" and now - claimed_at > STALE_AFTER: db.execute( "UPDATE work_claims SET attempt = attempt + 1, claimed_at = ?, updated_at = ?" " WHERE fingerprint = ?", (now, now, fp) ) db.execute("COMMIT") return "reclaimed" db.execute("COMMIT") return "busy" except Exception: db.execute("ROLLBACK") raise 

Two details I got wrong at first. A process-level threading.Lock is useless once you run two workers, so the atomicity has to live in the database, and BEGIN IMMEDIATE is what stops two writers from both seeing "no row" in SQLite. And the side effect and the state = 'done' update belong in one transaction, or you will produce exactly the duplicate you were preventing.

How I would prove it: three fault injections

A guard you have not attacked is a guard you do not know. My test plan kills the worker at three specific points and asserts that the effect count stays at one.

1. Crash after send, before commit. The model has been called, the row is still claimed, and no output was written. Expected result: the next run returns reclaimed only after STALE_AFTER, and the effect count stays at one if you also store the output before committing.
2. Duplicate delivery. Two workers claim the same fingerprint in parallel with no injected delay. Expected result: one claimed, one busy, and the busy worker exits without calling anything.
3. Retry storm. Wrap the call so it raises a timeout three times while the server keeps working. Expected result: the fingerprint never changes across attempts, so the fourth worker sees done and skips.

def run_unit(job, call_model, db, crash_at=None): fp = fingerprint(job["model"], job["messages"], job["params"]) verdict = claim(db, fp) if verdict in ("skip", "busy"): return verdict if crash_at == "before_call": raise SystemExit("injected crash") out = call_model(job) if crash_at == "after_call": raise SystemExit("injected crash") with db: write_effect(db, fp, out) db.execute("UPDATE work_claims SET state = 'done', updated_at = ? WHERE fingerprint = ?", (time.time(), fp)) return "done" 

The decision table I now keep next to the code

| Situation | Guard I use | Why |
| --- | --- |
| Single interactive call, user watching | None | A duplicate is a re-click, and the user notices immediately |
| Nightly batch writing rows | Claim table + outbox | Crash windows are minutes long, not milliseconds |
| Fan-out over the same document | Fingerprint of document | Many jobs, identical input, high duplicate cost |
| Cheap, naturally idempotent effect | Upsert only | The database already promises the same outcome |
| Multi-host workers | Unique index in Postgres | In-process locks do not cross machines |

What broke, and who should skip this

The fingerprint is only as good as your normalization, and that is where this design leaks. Two prompts that differ by a single trailing space are different keys to the ledger and the same work to a human, so semantic duplicates slip past and cost you twice while the table reports perfect health. Garbage collection is the other unglamorous part: done rows accumulate forever unless you index updated_at and prune on a schedule.

Sampling also matters. If your job legitimately wants a fresh sample for the same input, a claim table is the wrong tool, because it will faithfully refuse to run the work you actually wanted. Skip this whole approach if your effects are already idempotent upserts, if you only ever run one worker against one job, or if your volume is low enough that a duplicate is a shrug rather than an incident.

The workflow above is also why a second host and cheap model calls were on my mind at all, since a claim ledger needs a real second process to be attacked by. I ran the harness against MonkeyCode's free server option and its free model access, which kept the injected-failure runs off my laptop and off my card while I iterated on the state machine. Disclosure: This article was prepared as part of MonkeyCode's product outreach.

What I would repeat

I would repeat the ordering, not the clever bits: enumerate the birthplaces first, fingerprint the input rather than the attempt, and put the state machine in the database before you tune anything. I would also repeat the deliberate crash tests, because the bug that survived my first three fixes was invisible in normal logs and obvious within minutes of killing a worker mid-flight.

So before you add another retry, ask the boring question: if this exact call runs twice, what in my system notices? If the answer is "nothing, the second write just looks newer," build the ledger first and keep the retries you already have. If you want, try the three fault injections above on your own job and tell me which one found something.