Stock Deduction Strategies: Database vs Redis Implementations

In many e‑commerce and lottery systems, preventing overselling while handling high concurrency is a common challenge; this article presents three typical stock‑deduction solutions and evaluates their suitability.

Solution Overview

Single‑record MySQL stock

Sharded (multiple‑record) MySQL stock

Redis‑based stock using INCRBY and Lua scripts

Database Single Stock

All requests acquire a lock and update a single row; it works under low concurrency but becomes a bottleneck when traffic spikes, leading to request timeouts and database overload.

Database Multi Stock

Improves concurrency by distributing stock across multiple rows, yet still suffers from heavy database write load and row‑level lock contention under extreme load.

Redis Solution

Moves stock to Redis and uses a Lua script to atomically decrement stock, eliminating overselling and greatly improving performance. The approach requires handling cache loss and initializing stock via a callback.

Implementation Details

Lua script used for atomic deduction:

if (redis.call('exists', KEYS[1]) == 1) then
    local stock = tonumber(redis.call('get', KEYS[1]));
    local num = tonumber(ARGV[1]);
    if (stock == -1) then
        return -1;
    end;
    if (stock >= num) then
        return redis.call('incrby', KEYS[1], 0 - num);
    end;
    return -2;
end;
return -3;

Java interface for initializing stock:

/**
 * 获取库存回调
 * @author yuhao.wang
 */
public interface IStockCallback {
    /**
     * 获取库存
     * @return
     */
    int getStock();
}

Core service class StockService provides methods to decrement, add, and query stock, handling distributed locks with RedisLock and executing the Lua script in both Redis cluster and single‑node modes.

Controller StockController demonstrates how to call the service, initialize stock via a lambda, and expose REST endpoints for stock deduction, retrieval, and addition.

Overall, the Redis‑based approach offers the best scalability for high‑traffic inventory scenarios, while the database methods may be acceptable for low‑traffic use cases.