How do you keep retries from creating duplicate writes?

Short answer

Require the client to send a unique idempotency key for each operation, store it with its result in the same transaction, and return the stored result if the same key arrives again instead of executing anew.

Explanation

A timeout does not mean the server did not execute the request; it may have succeeded and the response was lost, so the client retries and the amount is charged twice. Store the key with a unique constraint so two concurrent requests with the same key cannot both succeed, save a fingerprint of the request body to reject reuse of the key with different data, and set an expiry for keys. Payment APIs such as Stripe use this pattern.

Code example

import json

def create_payment(conn, key: str, request: PaymentRequest) -> dict:
    with conn:
        row = conn.execute("SELECT response FROM idempotency WHERE key = ?", (key,)).fetchone()
        if row:
            return json.loads(row[0])          # Return the previous result
        result = charge_card(request)
        conn.execute(
            "INSERT INTO idempotency (key, response) VALUES (?, ?)",  # key On it UNIQUE
            (key, json.dumps(result)),
        )
        return result

Common mistakes

• Relying on the client alone to avoid duplicates.
• Checking whether the key exists and then inserting without a unique constraint, so two concurrent requests get through.

Follow-up questions

• What are the basic rules for using an HTTP client in Python?

Sources

• RFC 9110 — HTTP Semantics: Idempotent methods