Implementing Inventory Deduction and Preventing Overselling in E‑commerce Projects

In e‑commerce projects, preventing overselling while deducting inventory is critical. Three solutions are discussed:

Store inventory in a single MySQL column and update it on each deduction.

Split inventory across multiple MySQL rows to increase concurrency, though it still heavily loads the database.

Use Redis with its INCRBY feature to deduct inventory.

The first two database‑centric methods suffer from lock contention, high latency, and potential deadlocks under high concurrency, leading to system bottlenecks and timeouts.

To overcome these issues, the third solution stores stock in Redis and employs a Lua script to perform atomic deductions, handling cases such as unlimited stock, insufficient stock, and uninitialized stock.

Redis Lua script for stock 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;

Implementation details in Java include an IStockCallback interface for initializing stock, a StockService class that executes the Lua script via RedisTemplate , and a distributed lock ( RedisLock ) to ensure only one instance initializes stock.

/**
 * 获取库存回调
 */
public interface IStockCallback {
    int getStock();
}

@Service
public class StockService {
    Logger logger = LoggerFactory.getLogger(StockService.class);
    public static final long UNINITIALIZED_STOCK = -3L;
    @Autowired
    private RedisTemplate
redisTemplate;
    public static final String STOCK_LUA;
    static {
        StringBuilder sb = new StringBuilder();
        sb.append("if (redis.call('exists', KEYS[1]) == 1) then");
        sb.append("    local stock = tonumber(redis.call('get', KEYS[1]));");
        sb.append("    local num = tonumber(ARGV[1]);");
        sb.append("    if (stock == -1) then");
        sb.append("        return -1;");
        sb.append("    end;");
        sb.append("    if (stock >= num) then");
        sb.append("        return redis.call('incrby', KEYS[1], 0 - num);");
        sb.append("    end;");
        sb.append("    return -2;");
        sb.append("end;");
        sb.append("return -3;");
        STOCK_LUA = sb.toString();
    }
    // ... methods for stock(), addStock(), getStock() omitted for brevity ...
}

A StockController demonstrates how to call the service to deduct stock, retrieve current stock, and add stock, using the callback to initialize the inventory when needed.

@RestController
public class StockController {
    @Autowired
    private StockService stockService;

    @RequestMapping(value = "stock", produces = MediaType.APPLICATION_JSON_UTF8_VALUE)
    public Object stock() {
        long commodityId = 1;
        String redisKey = "redis_key:stock:" + commodityId;
        long stock = stockService.stock(redisKey, 60 * 60, 2, () -> initStock(commodityId));
        return stock >= 0;
    }

    private int initStock(long commodityId) {
        // TODO: initialize stock from DB or other source
        return 1000;
    }

    @RequestMapping(value = "getStock", produces = MediaType.APPLICATION_JSON_UTF8_VALUE)
    public Object getStock() {
        long commodityId = 1;
        String redisKey = "redis_key:stock:" + commodityId;
        return stockService.getStock(redisKey);
    }

    @RequestMapping(value = "addStock", produces = MediaType.APPLICATION_JSON_UTF8_VALUE)
    public Object addStock() {
        long commodityId = 2;
        String redisKey = "redis_key:stock:" + commodityId;
        return stockService.addStock(redisKey, 2);
    }
}

Overall, the Redis‑Lua approach with distributed locking provides an efficient, low‑latency solution for high‑concurrency inventory deduction, while also outlining the pitfalls of pure database methods.