How to Prevent Inventory Overselling in High‑Traffic E‑Commerce Systems

In everyday shopping, selling more of a regular product is ideal, but some loss‑making promotions set prices so low that they must be sold quickly, creating a risk of inventory overselling; the more the sales exceed the limit, the greater the loss.

1. Lock Queue

Principle: Use a lock to make threads process business logic sequentially. This method is simple to implement but performs poorly under high concurrency.

2. Update Statement Limitation

Principle: When reducing stock, the SQL update includes a condition that stock > 0 and returns the number of affected rows. If the affected rows > 0, the stock deduction succeeds; if < 0, it fails. This effectively prevents overselling but becomes a bottleneck in high‑concurrency scenarios because the database is the limiting factor.

3. Database Optimistic Lock

Principle: Add a version field to the product table. Each update includes the current version as a condition; the database returns the number of affected rows. If > 0, the deduction succeeds; otherwise it fails, indicating a concurrent modification.

4. Temporary Table Method

Principle: When reducing stock, first query a log table (creating a record if none exists). The operation checks the log’s version; if another thread has already updated it, the deduction fails and the transaction is rolled back, thus preventing overselling.

5. Redis Pre‑Store Inventory

Principle: Use a scheduled task (e.g., XXL‑Job) to load product stock into Redis before the sale starts (key = product ID, value = stock). When a user places an order, decrement the Redis key with the DECRBY command. If the result remains > 0, the order proceeds; otherwise it is rejected, effectively preventing overselling.

Conclusion: Choose the solution based on the actual business scenario. For high‑concurrency environments, the Redis pre‑stocking method is recommended due to its superior performance. For lower‑traffic situations, simpler approaches such as lock queues, update‑statement limits, or optimistic locking are sufficient and easier to implement.