The idempotency key: the unknown outcome made harmless

Break it on purposeNeeds Python

Problem

A commit has three outcomes from the client's side: committed, aborted, and unknown (the connection dropped before the answer). A retry after "unknown" may apply the change twice. An idempotency key recorded in the same transaction as the change makes the retry safe: the second attempt finds the key and does nothing. The protection lasts as long as the key is kept; a request replayed after its key has been deleted applies again.

Create wallets and topups(idem_key PRIMARY KEY, ...) in SQLite. Write a function topup(owner, amount, key) that runs in one transaction: insert the key into topups (fails on a duplicate), then update the balance. Simulate a lost acknowledgment by calling it, discarding the answer, and calling it again with the same key. Then call a naive version (no key) twice. Then delete keys older than the retention window and replay an old request.

The question. What balance does each version leave after a retried top-up of 50, and what happens after the key expires?

Predict

After the stored key has expired, a replayed top-up of 50 arrives. What is the balance?

50100

Run it

1Install uv once. It fetches Python and the lab's packages for you.
Terminal
```
curl -LsSf https://astral.sh/uv/install.sh | sh
```

2Get the script. It is the whole lab: read it, then download it.

the-idempotency-key-the-unknown-outcome.py47 linesDownload

View the code

the-idempotency-key-the-unknown-outcome.py

# /// script
# requires-python = ">=3.10"
# ///
# Copyright (c) 2026 Aayush Sharma · SPDX-License-Identifier: MIT
# https://thedataengineering.com/lab-code/LICENSE.txt
"""The idempotency key and the unknown outcome.

SQLite wallets and a topups table keyed by an idempotency key. A top-up of 50
whose acknowledgment is lost is retried: once by a naive function (no key)
and once by a keyed one (insert the key, then update the balance, in one
transaction). Then the keys older than the retention window are deleted and
an old request is replayed.
"""
import argparse, json, sqlite3

p = argparse.ArgumentParser(description=__doc__.splitlines()[0])
p.add_argument("--json", help="Also write the results to this JSON file.")
args = p.parse_args()

def fresh():
    c = sqlite3.connect(":memory:", isolation_level=None)
    c.execute("CREATE TABLE wallets (owner TEXT PRIMARY KEY, balance INTEGER NOT NULL)")
    c.execute("CREATE TABLE topups (idem_key TEXT PRIMARY KEY, owner TEXT, amount INTEGER, at INTEGER)")
    c.execute("INSERT INTO wallets VALUES ('anna', 0)"); return c
def naive(c, owner, amount): c.execute("UPDATE wallets SET balance = balance + ? WHERE owner = ?", (amount, owner)); return "applied"
def topup(c, owner, amount, key, at=0):
    try:
        c.execute("BEGIN")
        c.execute("INSERT INTO topups VALUES (?, ?, ?, ?)", (key, owner, amount, at))           # fails on a duplicate key
        c.execute("UPDATE wallets SET balance = balance + ? WHERE owner = ?", (amount, owner)); c.execute("COMMIT"); return "applied"
    except sqlite3.IntegrityError:
        c.execute("ROLLBACK"); return "already applied"
bal = lambda c: c.execute("SELECT balance FROM wallets").fetchone()[0]
rows = []
c = fresh(); naive(c, "anna", 50); r2 = naive(c, "anna", 50)                                  # the first answer was lost; retry
rows.append({"version": "naive, no key", "second_call": r2, "balance": bal(c)})
c = fresh(); topup(c, "anna", 50, "req-1"); r2 = topup(c, "anna", 50, "req-1")
rows.append({"version": "idempotency key", "second_call": r2, "balance": bal(c)})
c.execute("DELETE FROM topups WHERE at < 7")                                                    # retention: keys older than a week are gone
r3 = topup(c, "anna", 50, "req-1", at=30)                                                       # an old request replayed a month later
rows.append({"version": "idempotency key, replay after the key expired", "second_call": r3, "balance": bal(c)})
for r in rows: print(f"{r['version']:<48} retry: {r['second_call']:<16} balance {r['balance']}")

if args.json:
    with open(args.json, "w") as f:
        json.dump({"tables": {"versions": rows}}, f)

3Run it in the folder you saved it to, with the defaults first. Then change a knob from the table below and run it again.

Terminal

uv run the-idempotency-key-the-unknown-outcome.py
uv run the-idempotency-key-the-unknown-outcome.py --help     # every knob

Our run

A top-up of 50, retried

A top-up of 50, retried
Version	The retry	Balance
naive, no key	applied	100
idempotency key	already applied	50
idempotency key, replay after the key expired	applied	100

Reference runApple M2 · 8 cores · 8 GB

Output · uv run the-idempotency-key-the-unknown-outcome.py

naive, no key                                    retry: applied          balance 100
idempotency key                                  retry: already applied  balance 50
idempotency key, replay after the key expired    retry: applied          balance 100

macOS 26.6.2 · Python 3.13.3 · run 2026-10-20

What we found

The naive retry applied again, leaving 100. With the key, the retry reported already applied and the balance stayed 50. After the key was deleted by retention, the replayed request was applied a second time: balance 100.

Read it

An idempotency key turns "unknown" into "safe to retry", but only inside its dedup window, and nothing tells you when a request older than the window arrives. Size the window from the longest retry or replay you can have (including backfills and message-log replays), not from typical retry delays.