I Simulated a Lost Response After Commit. The Retry Created a Second Order

Imagine a client submitting an order. The server inserts the row and commits the transaction. Just before the response reaches the client, the connection disappears. The client sees an error. The database contains a perfectly good order.

What should the client do now?

Retrying is reasonable from its point of view. The client cannot see the commit. But if the server treats the retry as a new operation, there are now two orders. This failure has nothing to do with a slow database or a broken transaction. The transaction did exactly what it was supposed to do. The ambiguity sits between the commit and the response.

I wanted to make that gap visible in a small program. It uses SQLite as a stand-in for the order database and deliberately raises a connection error immediately after committing. There is no HTTP server in the program; the returned status codes represent the responses an API would send. This keeps the experiment focused on the database state and the client’s uncertainty.

Put the failure after the commit

The first version stores a request key but does not enforce its uniqueness. The second version adds a unique constraint and uses the same key for the retry. Save the following as retry_after_commit.py and run it with python3 retry_after_commit.py. It needs only the Python standard library.

import sqlite3import tempfilefrom pathlib import Pathdef create_order(path, key, payload, safe, lose_reply=False):    db = sqlite3.connect(path)    try:        if safe:            cursor = db.execute(                "INSERT INTO orders(request_key, payload) VALUES (?, ?) "                "ON CONFLICT(request_key) DO NOTHING",                (key, payload),            )            inserted = cursor.rowcount == 1            order_id, stored_payload = db.execute(                "SELECT id, payload FROM orders WHERE request_key = ?",                (key,),            ).fetchone()        else:            cursor = db.execute(                "INSERT INTO orders(request_key, payload) VALUES (?, ?)",                (key, payload),            )            inserted = True            order_id, stored_payload = cursor.lastrowid, payload        db.commit()        if lose_reply:            raise ConnectionError("response lost after commit")        if stored_payload != payload:            return 409, None        return (201 if inserted else 200), order_id    finally:        db.close()def run(safe):    with tempfile.TemporaryDirectory() as directory:        path = Path(directory) / "orders.db"        db = sqlite3.connect(path)        try:            unique = " UNIQUE" if safe else ""            db.execute(                "CREATE TABLE orders (id INTEGER PRIMARY KEY, "                f"request_key TEXT{unique}, payload TEXT NOT NULL)"            )            db.commit()        finally:            db.close()        try:            create_order(path, "checkout-42", "one book", safe, lose_reply=True)        except ConnectionError as error:            print(f"safe={safe}: first={error}")        print(f"safe={safe}: retry={create_order(path, 'checkout-42', 'one book', safe)}")        db = sqlite3.connect(path)        try:            ids = [row[0] for row in db.execute("SELECT id FROM orders ORDER BY id")]        finally:            db.close()        print(f"safe={safe}: stored_ids={ids}")        if safe:            print(                "safe=True: changed_payload="                f"{create_order(path, 'checkout-42', 'two books', safe)}"            )run(False)run(True)

Here is the output from the run:

safe=False: first=response lost after commitsafe=False: retry=(201, 2)safe=False: stored_ids=[1, 2]safe=True: first=response lost after commitsafe=True: retry=(200, 1)safe=True: stored_ids=[1]safe=True: changed_payload=(409, None)

In both cases, the first call commits order 1 and then reports a lost response. In the version without a constraint, the retry inserts order 2. The client receives a successful response, but the database now has two records for one intended action.

With a unique request key, the retry finds order 1. It can return the original identifier instead of creating another row. The client still cannot know from its first error whether the original attempt committed. It does not need to know before retrying, provided the server can recognize the operation.

Notice the last line. If the same key arrives with a different payload, the demo returns 409. Silently returning order 1 for a changed order would hide a client bug. A request key identifies one intended operation, including its meaningful inputs.

Why checking first is not enough

The tempting patch is to run a SELECT before the insert. If the key exists, return its order. If it does not, insert a new row. That works in a sequential demonstration. It is not a complete concurrency rule.

Two handlers can both check the key before either inserts it. Both see no row. Both proceed. The decisive protection is the uniqueness constraint in the database, where competing inserts are resolved as writes. The ON CONFLICT(request_key) DO NOTHING statement lets the losing attempt read the row that already owns the key. The code does not depend on a process-local lock that another server instance cannot see.

The toy schema keeps just a string payload to make the conflict visible. For a real API I would scope the key to the caller or tenant, define how long keys remain valid, and store enough information to compare the important parts of the request. If the operation creates several database rows, the key record and those rows belong in the same transaction. Otherwise a crash can leave a remembered key without a completed operation, or an operation without a usable retry record.

The response deserves the same care. Reusing an existing order ID is easy when the reply contains only that ID. A real endpoint may include a price snapshot, a confirmation number, or another value produced during the first attempt. I would persist or reconstruct a consistent result rather than generate a new confirmation on every retry.

The two sides of the commit

The location of the injected error matters. If the process fails before the database commits, the transaction can roll back and the retry can create the order. If it fails after the commit, the retry must find the existing order. The client may receive the same network error in both cases.

That is the distinction I was trying to test. An error returned to the caller is not proof that no write happened. A successful database commit is not proof that the caller received confirmation. Logging only the HTTP result misses one side of the story.

I would therefore record the request key with the operation result and make it searchable in logs. During an incident, it should be possible to answer whether a particular retry reused an existing operation, created one, or was rejected because its payload changed. A generic count of 500 responses cannot answer that.

There is also a limit to what the unique key proves. This program guards one SQLite row. If creating an order also charges a payment provider or publishes a message, a database constraint alone cannot make those external effects happen exactly once. The design then needs to define ownership of each effect and its retry behavior. An outbox can coordinate a database change with later message delivery, but consumers still need to handle repeated delivery. A payment call needs the provider’s own idempotency behavior or a carefully designed recovery path.

I would not claim that this tiny script solves all of that. It isolates the first boundary: the database committed, then the program simulated a lost response. In the original version, retrying turned one intended order into two rows. Once the request key became a database constraint, the same retry recovered the existing result. The injected failure was identical in both runs. The difference was whether the server could recognize a repeated intention.