Ensuring Order Service Idempotency: Transactions, Unique Keys, and Versioning to Prevent ABA Issues

From order placement, payment, shipment to receipt, each step requires updating multiple tables, often across several servers that may fail or experience network issues, making data consistency a critical challenge for order services.

Basic Functions and Data Tables

Create order

Update order status throughout the shopping flow

Query orders for reports and other purposes

The core tables typically include:

Order main table : stores basic order information; serves as the primary key for related child tables.

Order items table : records the products in each order.

Order payment table : keeps payment and refund details.

Order discount table : logs all discount information applied to the order.

How to Avoid Duplicate Orders?

When a user clicks the "Submit Order" button twice, two identical orders may be created. Front‑end duplicate‑submission prevention is insufficient because network errors and automatic retries can still cause repeated requests. The real problem is guaranteeing idempotency: multiple identical calls must result in a single order record.

Detecting Duplicate Requests

Checking the database for an existing order before insertion is unreliable, as defining "duplicate" via SQL conditions (same user, same items, same price) is error‑prone, especially when legitimate consecutive orders look identical.

Best Practice for Idempotent Order Creation

Let the database generate a primary key automatically, or explicitly provide a globally unique order number generated by a separate "order‑number" service. Because the primary‑key column has a unique constraint, an INSERT that supplies an existing key will fail, ensuring only one successful insertion.

Typical workflow:

Front‑end calls the order‑number service to obtain a unique order ID.

The order creation request includes this ID.

The INSERT uses the supplied ID; duplicate requests reuse the same ID, and the database’s unique constraint guarantees a single successful row.

If a duplicate INSERT fails, the order service should still return a success response to the client rather than propagating the error, because the order may already exist.

Solving the ABA Problem

What Is ABA?

Consider a scenario where a seller first records tracking number 666, then corrects it to 888. If the system crashes after the first update, the 666 response is lost and the client retries, causing the tracking number to revert to 666 and creating an inconsistent state.

General Solution with Optimistic Locking

Add a version column to the order main table. Each read returns the current version; the client includes this version in the update request. The service then executes an atomic UPDATE that checks the version and increments it.

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

If the version supplied by the client no longer matches the stored version, the UPDATE fails, indicating that another update occurred in the meantime.

Case 1: The first update to 666 succeeds; a later retry with the old version (8) fails, preventing the tracking number from reverting.

Case 2: After the successful 666 update, the client obtains the new version (9) and updates to 888; the subsequent retry of the 666 request still fails because the version has changed.

Summary

For order creation, generate a unique order number in advance and rely on the database’s unique‑key constraint to achieve idempotent inserts.

For order updates, use an optimistic‑locking version field: verify the version, update the data, and increment the version within a single transaction to prevent ABA anomalies.

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

Reference

《后端存储实战》