Ensuring Idempotency in Order Creation: Database Constraints, Unique Order IDs, and Versioning to Prevent Duplicate Orders and ABA Problems

Problem Background

The simplest case involves a DB transaction when creating an order: inserting records into the order table and order‑item table must occur within the same transaction.

If the Order service calls the Pay service and a network timeout occurs, the Order service may retry, causing the Pay service to receive the same payment request twice on different nodes, so the interface must guarantee idempotency.

How to Avoid Duplicate Orders

Front‑end can prevent duplicate form submissions, but network errors and automatic retries in RPC frameworks mean duplicate requests can still reach the back‑end, so the service itself must ensure idempotency.

How to Determine a Duplicate Request

Check the order table before inserting, but defining “duplicate order” in SQL is difficult.

Identical user, product, and price may still be a legitimate consecutive order.

To guarantee idempotency, the following measures are needed:

Each Request Must Have a Unique Identifier

For example, a payment request must include an orderId that can only be successfully paid once.

Record a Flag After Processing a Request

Store a status field in MySQL, such as a payment record for the order before processing payment.

Check If the Request Has Been Processed Before

If an order is already paid, a payment record exists; a duplicate request will cause a unique‑key violation on INSERT , preventing double charging.

MySQL’s primary key enforces uniqueness: providing a primary key that already exists causes the INSERT to fail, which can be used to achieve idempotent order creation.

Provide an “orderId generation” API that returns a globally unique order number; the front‑end obtains this ID before submitting the order, and the same ID is used in the create‑order request.

The order ID becomes the primary key of the order table, so duplicate requests carry the same ID, and the DB’s unique constraint ensures only one successful insert.

In practice, combine this with Redis: use the orderId as a unique key, set a flag like set order_id payed after a successful payment record, and reject further payments for the same ID.

Solving the ABA Problem

What Is ABA

When a seller enters a tracking number (e.g., 666), corrects it to 888, and a retry causes the old 666 request to be processed again, the tracking number may revert incorrectly.

Solution Using Version Columns

Add a version column to the order table. Each query returns the current version, which the front‑end sends back when updating.

The order service compares the provided version with the stored one:

If they differ, reject the update.

If they match, update the data and increment version by 1 within the same transaction.

Example SQL:

UPDATE orders set tracking_number = 666, version = version + 1 WHERE version = 8;

This ensures that between reading and updating, no other modification has occurred; otherwise the update fails and the client must reload the latest version.

Using the version column, the two ABA cases are handled: the stale 666 update fails because the version has changed, and a retry of the same request also fails, preventing incorrect data.

Summary

Create order service can pre‑generate a unique order ID and rely on the DB’s unique constraint to achieve idempotent order creation.

Update order service can use a version‑number mechanism to detect concurrent modifications, increment the version atomically, and thus solve the ABA problem while ensuring idempotent updates.

These idempotent techniques can be applied to any service that persists data in a database with a primary‑key table.