How to Prevent Inventory Over‑Selling in High‑Concurrency Systems

1. Why does inventory over‑selling happen?

When processing a purchase, the basic flow is to read the current stock, check if the requested quantity is available, and then decrement the stock. In a single‑threaded scenario this works fine, but under high concurrency two requests may read the same stock value simultaneously, both think the stock is sufficient, and both decrement it, resulting in a negative stock (e.g., -1).

We will look at three common solutions: pessimistic lock, optimistic lock, and Redis + Lua.

2. Solutions

2.1 Pessimistic Lock

The root cause of over‑selling is that shared data is modified by multiple threads concurrently. By locking the row when it is read (using SELECT ... FOR UPDATE), other transactions cannot read or write the same row until the lock is released, preventing over‑selling.

// Transaction start
select id, product_name, stock, ... 
from t_product 
where id=#{id} for update;

update t_product 
set stock = stock - #{quantity} 
where id = #{id};
// Transaction end

Advantages:

Simple logic and easy implementation directly in the database.

Disadvantages:

The exclusive lock can significantly impact performance.

2.2 Optimistic Lock

To avoid the performance penalty of pessimistic locking, optimistic locking adds a version field to the product record. The version is read together with the stock; when updating, the statement checks that the version has not changed. If it matches, the update proceeds and the version is incremented; otherwise the update fails.

update t_product 
set stock = stock - #{quantity},
    version = version + 1
where id = #{id} and version = #{version}

Advantages:

No blocking, better performance than pessimistic lock.

Disadvantages:

More complex implementation, requires version control and possibly a retry mechanism.

2.3 Redis + Lua

In high‑concurrency environments, database access can be a bottleneck. Using Redis with a Lua script allows the read‑modify‑write sequence to be executed atomically in memory, providing the best performance.

The Lua script obtains the current stock, checks if it is sufficient, decrements it, updates the stock in Redis, and records the purchase.

-- Get current stock
local stock = tonumber(redis.call('hget', product, 'stock'))

-- If stock is insufficient, return 0
if stock < quantity then return 0 end

-- Decrease stock
stock = stock - quantity

-- Update stock
redis.call('hset', product, 'stock', tostring(stock))

-- Save purchase record
redis.call('rpush', purchaseList, purchaseRecord)

return 1

Advantages:

Highest performance and simple implementation.

Disadvantages:

Requires additional work to synchronize data back to the database and ensure Redis high availability.

3. Summary

Content referenced from “深入浅出 Spring Boot 2.x”.