How to Ensure Idempotent Order Creation and Prevent ABA Issues in Distributed Systems

Problem Background

When an order is created the order table and the order_items table must be updated in the same database transaction. If the network times out, the Order service may retry the request, causing the Pay service to receive the same payment request twice. Because load‑balancing can route the two requests to different nodes, the payment interface must be idempotent.

Idempotent Order Creation and Payment

Unique request identifier

Each payment request should carry a globally unique orderId. An orderId can be paid only once, so it serves as the idempotency key.

Persist a processing record

Before performing the actual payment, insert a payment record (or a status flag) into MySQL. The order_id column is defined with a UNIQUE constraint.

Check the record before processing

If a payment record with the same orderId already exists, the INSERT will fail with a duplicate‑key error and the service can skip the second charge.

Implementation options:

Use orderId as the primary key of the order table. Duplicate INSERTs are rejected by MySQL automatically.

Optionally store the orderId in Redis as a unique key. After a successful INSERT, set orderId payed in Redis. Subsequent retries find the key and bypass payment.

Solving the ABA Problem with Optimistic Locking

What is the ABA problem

Consider a seller who first writes a tracking number 666, then corrects it to 888. If the response for the 666 update is lost, a retry may resend 666 after 888, reverting the tracking number to the wrong value.

Version‑column solution

Add a version column to the order table. The client reads the current version together with the order data and sends it back with the update request. The update succeeds only if the version matches, otherwise it is rejected.

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

Two scenarios illustrate the safety:

If the 666 update succeeded, the subsequent 888 request carries the old version number and fails, so the user sees an error instead of an incorrect overwrite.

If the 666 update succeeded and the 888 request uses the new version, the 888 update succeeds; a later retry of 666 uses the stale version and is rejected.

This compare‑and‑set pattern guarantees that the database state and user feedback remain consistent, achieving idempotent updates and eliminating ABA.

Summary

Generate a globally unique orderId before creating an order and rely on the database’s UNIQUE constraint to make order creation idempotent.

Store a payment record (or a Redis key) keyed by orderId so that duplicate payment attempts are detected early.

Use a version column with an optimistic‑locking UPDATE (WHERE version = …) to prevent ABA problems during concurrent updates.

These patterns apply to any service that persists data in a relational table with a primary‑key constraint.