Inventory Deduction and Concurrency Control in Backend Systems

This article explains the typical three‑step process for deducting inventory in an e‑commerce order: select the current stock by product ID, verify that the stock is sufficient, and update the remaining stock.

The corresponding pseudo‑code is shown below:

// Retrieve remaining stock select stock_remaing from stock_table where id=${goodsId}; // Compare stock if (stock_remaing < quantity) { // Throw out‑of‑stock exception } else { int new_stock = stock_remaing - quantity; } // Update stock update stock_table set stock_remaing=${new_stock} where id=${goodsId};

When multiple requests modify the stock concurrently, non‑serializable isolation levels can cause write‑overwrites, leading to overselling. For example, two customers may both read a stock of 100 and each deduct 20 and 30, resulting in an incorrect final stock.

To prevent this, a pessimistic lock using SELECT ... FOR UPDATE can serialize the deduction, but it reduces throughput under high concurrency.

An optimistic‑lock approach (CAS with a version number and retry logic) allows higher concurrency while still guaranteeing consistency.

A direct‑decrement SQL statement can be used, as illustrated:

update stock_table set remaing_stock = remaing_stock - ${quantity} where id = 商品id and remaing_stock > ${quantity};

However, this method is not idempotent; in distributed systems with retries, it may cause multiple deductions and stock loss. Using Redis transactions can achieve fast atomic decrements, but it sacrifices the strong consistency guarantees of relational databases.