Ensuring Idempotency in Order Services: Preventing Duplicate Orders and Solving the ABA Problem

Problem Background

The simplest case is a DB transaction: when creating an order, inserts to the order table and order‑item table must be in the same transaction.

Order service calls Pay service; a network timeout triggers a retry, causing the Pay service to receive the same payment request twice, possibly 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 or gateways mean duplicate requests cannot be fully avoided on the client side; the service must ensure idempotency.

2.1 How to Determine a Duplicate Request

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

Is an order with the same user, product, and price a duplicate? What if the user intentionally places two identical orders?

To guarantee idempotency, the following must be done:

2.1.1 Each request must have a unique identifier

For example, a payment request must include an order ID, and an order ID can only be successfully paid once.

2.1.2 Record a marker after processing each request

In MySQL, add a status field or a payment record before payment.

2.1.3 When receiving a request, check whether it has been processed before

If an order has already been paid, a payment record exists; a duplicate request will attempt to insert a payment record with the same order_id , triggering a unique‑key violation and preventing double charging.

In MySQL, if the INSERT statement provides a primary key that already exists, the statement fails. Thus, using the database’s primary‑key uniqueness constraint can enforce idempotent order creation.

Provide an "orderId generation" API that returns a globally unique order number. The front‑end obtains this ID before the user submits the order, and the same ID is used in the creation request, ensuring that duplicate requests carry the same primary key.

The order ID becomes the primary key of the order table; duplicate INSERT statements will have the same primary key, and the DB’s unique constraint guarantees only one succeeds.

In practice, combine this with Redis: use the orderId as a unique key; only after successfully inserting a payment record should the system proceed with the charge.

Solving the ABA Problem

3.1 What Is the ABA Problem

After an order is paid, the seller fills in a tracking number. If the seller first enters "666" then corrects it to "888", two update requests are sent. If the system processes the "666" request, then the "888" request, but the response for "666" is lost and the client retries, the tracking number may be overwritten back to "666", causing incorrect data.

3.2 Solution

Add a version column to the order main table. Each query returns the current version, which the front‑end includes in the update request.

When updating, the service compares the provided version with the current one:

If they differ, reject the update.

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

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

The version value must be supplied by the client in the WHERE clause. This ensures that between reading and updating, no other modification occurs; otherwise the version changes and the update fails, prompting a re‑read.

With this versioning, the ABA scenarios are handled as follows:

Updating to "666" succeeds; a subsequent "888" request with the old version fails, so the user sees the failure.

Updating to "666" succeeds, then "888" with the new version succeeds; a later retry of the "666" request carries the old version and therefore fails.

Thus, the database state and user feedback remain consistent, achieving idempotent updates and avoiding ABA.

Summary

For order creation, pre‑generate a unique order number and rely on the DB’s unique constraint to ensure idempotent inserts.

For order updates, use a version‑column mechanism to verify and atomically increment the version, solving the ABA problem and guaranteeing idempotent updates.

These idempotency techniques can be applied to any service that writes to a database with a primary‑key‑based table.