Idempotency and compensation when outcomes are unknown

Implementation and trade-offs

code example

Deduplication of business operations within SQLite transactions

Python 3 standard library demonstration, using in-memory SQLite; real recovery requires a file or server database, and external actions require remote idempotent capabilities.

import hashlib
import json
import sqlite3

db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE publications (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    tenant TEXT NOT NULL,
    title TEXT NOT NULL
);
CREATE TABLE operations (
    tenant TEXT NOT NULL,
    op_key TEXT NOT NULL,
    payload_hash TEXT NOT NULL,
    result_id INTEGER NOT NULL,
    PRIMARY KEY (tenant, op_key)
);
""")

def publish(tenant, op_key, title):
    payload = json.dumps({"title": title}, sort_keys=True).encode()
    digest = hashlib.sha256(payload).hexdigest()
    db.execute("BEGIN IMMEDIATE")
    try:
        old = db.execute(
            "SELECT payload_hash, result_id FROM operations WHERE tenant=? AND op_key=?",
            (tenant, op_key),
        ).fetchone()
        if old:
            if old[0] != digest:
                raise ValueError("IDEMPOTENCY_CONFLICT")
            db.commit()
            return old[1]
        cur = db.execute(
            "INSERT INTO publications(tenant,title) VALUES (?,?)", (tenant, title)
        )
        result_id = cur.lastrowid
        db.execute("INSERT INTO operations VALUES (?,?,?,?)",
                   (tenant, op_key, digest, result_id))
        db.commit()
        return result_id
    except Exception:
        db.rollback()
        raise

print(publish("t1", "task-7:publish:v3", "Agent recovery"))
print(publish("t1", "task-7:publish:v3", "Agent recovery"))
print("rows", db.execute("SELECT COUNT(*) FROM publications").fetchone()[0])
try:
    publish("t1", "task-7:publish:v3", "Changed title")
except ValueError as exc:
    print(str(exc))
db.close()

expected output

1
1
rows 1
IDEMPOTENCY_CONFLICT

Engineering deduction

scene

Assume an engineering scenario: the article publishing API times out, and the Agent re-requests publishing after recovery.

design decisions

The operation key is bound to the task and draft version; retrying with the same key returns the original release result; parameter changes require new approval and new operations.

Verify target

Demonstration results: Two identical calls return the same ID, the database has only one publishing record, and different parameters are rejected.

applicable boundary

The code only proves deduplication within a single database transaction; it does not prove exactly-once external network actions, nor does it implement distributed delivery.

Easy to make mistakes

• Treat the local timeout as if the remote end has not been executed
• Check first and then write but without transaction and unique constraints
• Think that saving checkpoints can ensure that external side effects only happen once

References

It is designed based on public technical information; the reference materials support the technical mechanism, and the scenarios and scoring standards are designed by this website and do not represent the original interview questions of a certain company. New Q&A and migration cases are added for principle explanation, and source verification and case operation verification are recorded separately.

• LangGraph: Interrupts and side effect replay
• Python: sqlite3 transactions and parameter binding
• Stripe: Parameter comparison and retention period for idempotent requests
• AWS: Safely retry via idempotent API

Check how far you understand

After reading, you can explain the principles, boundaries, and trade-offs against these standards. It is up to you to evaluate your mastery; if further verification is needed, complete the small tasks below.

Basic standards met

Know that timeouts may occur after external commits.

Intermediate and advanced signals

Use stable business idempotency keys, intent ledgers, and receipt reconciliation.

Senior criteria

Unknown final states, manual processing and fault injection when idempotent are explicitly not supported.