Designing a Scalable E‑Commerce Inventory System: Stock Deduction & DB Strategies

1 Stock Deduction

When many users attempt to purchase the same SKU simultaneously, the system must serialize stock reduction to prevent overselling. This is typically achieved with database‑level atomic updates or explicit locking mechanisms.

1.1 Key Technical Points

Stock for a given SKU is shared across all users.

Deduction succeeds only if remaining_stock >= requested_quantity; otherwise the operation must be rejected.

Concurrent deductions must be thread‑safe and maintain data consistency.

High‑QPS scenarios (e.g., flash sales) require low‑latency, highly available solutions.

Batch deductions for a shopping‑cart must be executed within a transaction so that a partial failure rolls back the whole batch.

Refunds must be able to restore stock, and the restored amount cannot exceed the amount previously deducted.

Refund operations should be idempotent and may be split into multiple steps.

1.2 Database‑Based Deduction

Using native database features (optimistic locking and transactions) provides strong consistency with minimal development effort.

1.2.1 Required Database Features

Optimistic lock – a version column or the stock value itself is used in the WHERE clause to guarantee that only one thread can modify the row when the condition holds.

Transaction support – enables atomic batch deductions and automatic rollback on any failure.

The service first performs a lightweight read to obtain the current remaining_stock. If the stock is insufficient, the request is rejected early, reducing write load on the primary database.

2 Database Schema

2.1 Inventory Table

create table t_inventory (
    sku_id bigint null comment '商品规格 id',
    leaved_amount int null comment '剩余可购买数量'
);

When an order is cancelled or refunded, the deducted quantity is added back.

When the merchant replenishes inventory, the leaved_amount is increased accordingly.

2.2 Inventory Flow Table

create table t_inventory_flow (
    id bigint auto_increment comment '主键 id',
    primary key,
    sku_id int null comment '商品规格 id',
    order_detail_id mediumtext null comment '订单明细 id',
    quantity_trade int null comment '本次购买扣减的数量'
);

Records each stock change for audit, reconciliation, inventory checks, and troubleshooting.

Refund processes reference this table to determine how much stock can be returned.

2.3 Single‑Item Deduction SQL

update t_inventory
set leaved_amount = leaved_amount - #{count}
where sku_id = #{skuId}
  and leaved_amount >= #{count};

The WHERE clause implements the optimistic‑lock check. The service inspects the affected‑row count: 1 means success; 0 means insufficient stock and the transaction must be rolled back.

2.4 Deduction Flow Recording

When a refund occurs, the id from t_inventory_flow is used to link the return to the original deduction.

Idempotency: the flow id is returned to the client so that a retry can safely re‑use the same identifier without creating duplicate records.

3 First Upgrade – Read‑Write Splitting

In flash‑sale scenarios (e.g., 5 SKUs, 100 k QPS), the flow table stores only a few rows while read traffic is massive. Adding a read replica offloads stock‑check queries from the primary. The replica may serve slightly stale data, but the final atomic deduction still runs on the primary, preserving correctness.

4 Second Upgrade – Cache Layer

To further reduce latency, the remaining stock is synchronized from MySQL to Redis. Stock‑check requests read from Redis, achieving tens of thousands of QPS per instance because the operation is in‑memory.

Hot SKUs are distributed across multiple Redis nodes to balance load and avoid a single‑point bottleneck.

5 Evaluation of the Database‑Centric Approach

Pros

ACID guarantees prevent overselling.

Implementation is straightforward and suitable when development time or resources are limited.

Cons

When many SKUs participate in flash sales, write pressure on the primary can become a performance bottleneck, even with read replicas and caching.