How to Build a Robust E‑Commerce Backend: Idempotency, Snapshots, and Sharding

1. Avoid Duplicate Orders (Idempotency)

When a user clicks the submit button multiple times, the backend receives duplicate create‑order requests, resulting in duplicate orders. Enforce idempotency so that repeated requests have the same effect as a single request.

Primary‑Key Constraint : Generate a globally unique order ID before the order page is shown. Include this ID in the create‑order request; duplicate inserts fail because the primary‑key is unique.

One‑Time Token : Render a token on the order page and validate it on submission. The token is consumed after the first successful use.

2. Order Snapshot to Reduce Storage Cost

Store a snapshot of product details with the order to protect against later product changes. Because many orders share identical product data, compute an MD5 digest of the product JSON. If a snapshot with the same digest already exists, reference the existing snapshot instead of creating a new one. Snapshot creation should be asynchronous so failures do not block the purchase flow.

public class DigestTest {
    public static void encodeStr(String data) {
        String encodeS = DigestUtils.md5Hex(data);
        System.out.println(encodeS);
    }
    public static void main(String[] args) {
        String data = "..."; // example product description
        encodeStr(data);
    }
}

3. Shopping Cart – Mixed Storage

Allow anonymous users to add items by issuing a temporary token that identifies a server‑side cart record. The token is stored in a cookie; when the user later logs in, the temporary cart is merged into the permanent cart. Keeping the cart on the server avoids complex client‑side merge logic.

4. Inventory Oversell Prevention

Three common stock‑deduction strategies:

Deduct on Order Creation : Reduce stock within the same transaction that creates the order. Guarantees no oversell but may lock inventory for unpaid orders.

Deduct on Payment : Reserve stock until payment succeeds. Risk: stock may be unavailable when the user finally pays.

Pre‑Reserve (Timed Hold) : Reserve stock for a limited period (e.g., 30 minutes). Release the reservation if payment does not occur.

All approaches rely on a row‑level lock that prevents the stock column from becoming negative.

UPDATE product SET amount = amount - 1 WHERE id = $id AND amount - 1 >= 0;

5. Logistics Number Update – ABA Problem

When a logistics number is corrected, a retry of the original update can overwrite the new value (ABA inconsistency). Add a version column and update only when the version matches the expected value.

UPDATE order
SET logistics_num = #{logistics_num}, version = version + 1
WHERE order_id = 1111 AND version = #{version};

6. Account Balance Updates with Transaction Guarantees

Paying an order requires atomically deducting the buyer’s balance and crediting the seller’s balance while inserting an immutable, auto‑incrementing transaction log. Use a database transaction so that either both balance updates and the log succeed, or all are rolled back.

Typical isolation levels are Read‑Committed (RC) and Repeatable‑Read (RR) to avoid dirty reads. When multiple micro‑services participate, employ distributed transaction coordination.

7. MySQL Read‑Write Splitting Inconsistency

In a primary‑replica setup, writes go to the primary and reads are served from replicas. Replication lag can cause a read immediately after a write to return stale data. In e‑commerce checkout flows, this may surface when the order‑detail page reads from a replica before the write has propagated.

8. Historical Order Archiving (Hot/Cold Data)

Apply the 80/20 rule: 20 % of orders generate 80 % of load. Separate recent/active orders (hot) from older or completed orders (cold). Common partitioning criteria:

Time‑based: orders older than three months are cold.

Status‑based: completed orders are cold.

Combined: orders older than three months and completed are cold.

Cold data can be moved via scheduled batch jobs or by listening to binlog events and migrating qualifying rows.

9. Order Sharding for Multi‑Dimensional Queries

When order volume is large, shard tables by a derived key. For a 19‑digit order ID, the last six digits correspond to the buyer’s ID suffix. Use (order_id % 1_000_000) as the shard key to distribute orders across 1 000 000 tables, satisfying buyer‑centric queries.

Seller‑centric queries can be served by maintaining a separate dataset keyed by seller_id, avoiding cross‑shard joins.