Implementing Stock Deduction with MySQL and Redis Using Lua Scripts

In daily development, inventory deduction is common in e-commerce, lottery systems, and similar scenarios.

Three solution approaches are presented: (1) use a single MySQL field to store stock and update it on each deduction, (2) shard stock across multiple rows to increase concurrency, and (3) store stock in Redis and use its INCRBY feature.

Analysis of the database‑based methods shows that the single‑stock approach causes all requests to wait for a lock, leading to request blocking, time‑outs, and possible system avalanche under high concurrency, while also generating heavy DB traffic; the multi‑stock approach mitigates blocking slightly but still results in frequent DB updates, row‑level lock contention, and risk of over‑deduction if SELECT and UPDATE are not atomic.

The Redis‑based solution places stock in cache and leverages an atomic Lua script that calls INCRBY, eliminating over‑deduction and greatly improving performance, though cache loss requires a recovery strategy such as re‑initializing stock from a persistent source.

The concrete implementation uses a Java service with a Lua script executed via RedisTemplate , a distributed lock for safe initialization, and a callback interface to obtain the initial stock value:

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

The StockService class defines constants, builds the Lua script, and provides methods to deduct stock, add stock, and query stock, handling cases such as uninitialized stock, insufficient stock, unlimited stock, and returning the remaining quantity after deduction:

/**
 * 扣库存
 *
 * @author yuhao.wang
 */
@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");
        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();
    }

    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) {
                        final 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;
    }

    public long addStock(String key, int num) {
        return addStock(key, null, num);
    }

    public long addStock(String key, Long expire, int num) {
        boolean hasKey = redisTemplate.hasKey(key);
        if (hasKey) {
            return redisTemplate.opsForValue().increment(key, num);
        }
        Assert.notNull(expire, "初始化库存失败，库存过期时间不能为null");
        RedisLock redisLock = new RedisLock(redisTemplate, key);
        try {
            if (redisLock.tryLock()) {
                hasKey = redisTemplate.hasKey(key);
                if (!hasKey) {
                    redisTemplate.opsForValue().set(key, num, expire, TimeUnit.SECONDS);
                }
            }
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
        } finally {
            redisLock.unlock();
        }
        return num;
    }

    public int getStock(String key) {
        Integer stock = (Integer) redisTemplate.opsForValue().get(key);
        return stock == null ? -1 : stock;
    }

    private Long stock(String key, int num) {
        List<String> keys = new ArrayList<>();
        keys.add(key);
        List<String> args = new ArrayList<>();
        args.add(Integer.toString(num));
        long result = redisTemplate.execute(new RedisCallback<Long>() {
            @Override
            public Long doInRedis(RedisConnection connection) throws DataAccessException {
                Object nativeConnection = connection.getNativeConnection();
                if (nativeConnection instanceof JedisCluster) {
                    return (Long) ((JedisCluster) nativeConnection).eval(STOCK_LUA, keys, args);
                } else if (nativeConnection instanceof Jedis) {
                    return (Long) ((Jedis) nativeConnection).eval(STOCK_LUA, keys, args);
                }
                return UNINITIALIZED_STOCK;
            }
        });
        return result;
    }
}

An example StockController demonstrates REST endpoints for deducting stock, retrieving current stock, and adding stock, wiring the StockService and providing an initialization callback:

@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 persistent store
        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);
    }
}