Implementing Inventory Deduction and Preventing Overselling in E‑commerce with MySQL and Redis

The author, a senior architect, introduces the problem of safely reducing inventory in an e‑commerce system while avoiding overselling.

Solution Overview

Use a single MySQL field to store stock and update it on each deduction.

Split stock into multiple MySQL rows to increase concurrency.

Store stock in Redis and use the INCRBY command for atomic deduction.

Analysis

Both MySQL‑based approaches suffer from lock contention, high database load, and possible deadlocks under heavy traffic, leading to request timeouts and system avalanche.

Redis solves these issues by providing fast atomic operations, but requires handling cache loss and initialization.

Redis‑Based Implementation

The author proposes a Redis‑Lua script that checks stock status, handles unlimited stock, insufficient stock, and returns the remaining quantity. The script is executed via a RedisCallback that supports both single‑node and cluster modes.

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;

A distributed lock (implemented by RedisLock ) ensures that only one instance initializes the stock when it is missing.

Key Interfaces and Classes

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

@Service
public class StockService {
    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");
        // ... script building omitted for brevity ...
        STOCK_LUA = sb.toString();
    }
    public long stock(String key, long expire, int num, IStockCallback stockCallback) {
        long stock = stock(key, num);
        if (stock == UNINITIALIZED_STOCK) {
            RedisLock redisLock = new RedisLock(redisTemplate, key);
            try {
                if (redisLock.tryLock()) {
                    stock = stock(key, num);
                    if (stock == UNINITIALIZED_STOCK) {
                        int initStock = stockCallback.getStock();
                        redisTemplate.opsForValue().set(key, initStock, expire, TimeUnit.SECONDS);
                        stock = stock(key, num);
                    }
                }
            } finally {
                redisLock.unlock();
            }
        }
        return stock;
    }
    // addStock, getStock, and internal stock execution methods omitted for brevity
}

@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, 3600, 2, () -> initStock(commodityId));
        return stock >= 0;
    }
    private int initStock(long commodityId) { return 1000; }
    // getStock and addStock endpoints omitted for brevity
}

The article concludes with a call to action for readers to join the architect community, share feedback, and explore additional resources.