Inventory Deduction Strategies: Database vs. Redis with Lua Script Implementation

In everyday development, many scenarios require inventory deduction, such as product stock in e‑commerce or prize stock in lottery systems.

Solution

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

Store stock across multiple rows in MySQL and route deductions to increase concurrency, though it still heavily accesses the database.

Place stock in Redis and use the INCRBY feature (via a Lua script) to deduct stock.

Analysis

The first two methods are database‑centric and suffer from blocking, lock contention, and performance degradation under high concurrency, often leading to request timeouts and potential system avalanche.

Based on Single‑Database Stock

When every request must acquire a lock and update the database, low concurrency works, but high concurrency causes massive blocking, excessive DB load, and possible deadlocks.

Based on Multi‑Database Stock

This is an optimized version of the single‑stock approach; it improves concurrency slightly but still results in heavy DB update traffic and resource consumption.

Problems remaining with database‑based stock deduction:

The deduction must be performed in a single SQL statement; separating SELECT and UPDATE can cause over‑deduction under concurrency. Example: update number set x=x-1 where x>0

MySQL performance degrades sharply after a certain concurrency threshold.

Row‑level locks on the same row can cause waiting or deadlocks, leading to front‑end timeouts.

Based on Redis

Using Redis solves over‑deduction and performance issues, but cache loss requires a recovery strategy, especially for asynchronous reward distribution where stock must be rebuilt after message queue consumption.

Specific Redis Implementation for Stock Deduction

Use a Redis Lua script to perform atomic deduction.

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

Provide a callback function to obtain initial stock when needed.

Stock Initialization Callback (IStockCallback)

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

Stock Service (StockService)

/**
 * 扣库存
 * @author yuhao.wang
 */
@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 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(key, num) methods omitted for brevity
}

Controller (StockController)

@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, 60 * 60, 2, () -> initStock(commodityId));
        return stock >= 0;
    }
    private int initStock(long commodityId) {
        // TODO: initialize stock from DB
        return 1000;
    }
    @RequestMapping(value = "getStock", produces = MediaType.APPLICATION_JSON_UTF8_VALUE)
    public Object getStock() {
        long commodityId = 1L;
        String redisKey = "redis_key:stock:" + commodityId;
        return stockService.getStock(redisKey);
    }
    @RequestMapping(value = "addStock", produces = MediaType.APPLICATION_JSON_UTF8_VALUE)
    public Object addStock() {
        long commodityId = 2L;
        String redisKey = "redis_key:stock:" + commodityId;
        return stockService.addStock(redisKey, 2);
    }
}

This complete example demonstrates how to safely deduct inventory in high‑concurrency environments by moving the critical operation to Redis, using Lua for atomicity, and falling back to database initialization when the cache is missing.