Implementing Stock Deduction with MySQL and Redis Using Java

In everyday development, operations such as reducing product inventory in e‑commerce or prize stock in a lottery system require reliable stock deduction mechanisms.

Solution

Use a MySQL table with a single column to store stock and update it on each deduction.

Store stock across multiple rows to increase concurrency, but still rely heavily on database updates.

Place stock in Redis and use its INCRBY feature to decrement stock.

Analysis

Both MySQL‑based approaches suffer from high contention under heavy load, leading to request blocking, time‑outs, and potential system collapse. They also risk over‑deduction if the decrement is not performed atomically.

Problems with Database‑Based Stock Deduction

The deduction must be executed in a single SQL statement; otherwise, concurrent requests may cause over‑deduction. Example: update number set x = x-1 where x > 0 MySQL performance degrades sharply after a certain concurrency level, and row‑level locking can cause deadlocks.

Redis‑Based Stock Deduction

Using Redis eliminates over‑deduction and improves performance, but requires handling cache loss and ensuring stock consistency when initializing from the database.

Specific Implementation Using Redis

Implement stock deduction with a Redis Lua script.

Use a distributed lock to ensure only one service initializes stock.

Provide a callback interface IStockCallback to fetch initial stock.

Initialization Callback Interface

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

StockService Implementation

@Service
public class StockService {
    Logger logger = LoggerFactory.getLogger(StockService.class);
    public static final long UNINITIALIZED_STOCK = -3L;
    @Autowired
    private RedisTemplate<String, Object> 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 return -1; end;");
        sb.append("    if (stock >= num) then return redis.call('incrby', KEYS[1], 0 - num); end;");
        sb.append("    return -2; end; return -3;");
        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);
                    }
                }
            } catch (Exception e) {
                logger.error(e.getMessage(), e);
            } finally {
                redisLock.unlock();
            }
        }
        return stock;
    }
    // addStock, getStock, and private stock methods omitted for brevity
}

Controller Example

@RestController
public class StockController {
    @Autowired
    private StockService stockService;
    @RequestMapping(value = "stock", produces = MediaType.APPLICATION_JSON_UTF8_VALUE)
    public Object stock() {
        long commodityId = 1L;
        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 provided code demonstrates a complete backend solution for high‑concurrency stock deduction, combining Redis Lua scripting, distributed locking, and a fallback initialization callback.