Stock Deduction Strategies: Database vs Redis and a Spring‑Based Implementation

In everyday development, many scenarios require decrementing a stock count, such as product inventory in e‑commerce or prize inventory in a lottery system.

Solution

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

Use a MySQL table with multiple rows (sharding) to store the stock, routing deductions to different rows to increase concurrency.

Store the stock in Redis and use Redis's INCRBY command to decrement it.

Analysis

The first two methods are based on database updates. The single‑record approach locks all requests, causing blocking and possible time‑outs under high concurrency, and heavily loads the database. The multi‑record approach reduces contention but still performs many database updates.

Database‑based deduction also suffers from:

Need for a single SQL statement; separate SELECT and UPDATE can cause over‑deduction. Example:

update number set x = x-1 where x > 0

MySQL's performance degrades sharply after a certain concurrency level.

Row‑level locking in InnoDB can lead to waiting, deadlocks, and request time‑outs.

Redis

Placing the stock in Redis and using the INCRBY feature solves over‑deduction and performance problems, but a cache loss requires a recovery strategy (e.g., re‑initializing from the database after MQ consumption).

Specific Redis Implementation

We implement stock deduction with a Redis Lua script, a distributed lock, and an initialization callback.

Initialization Callback (IStockCallback)

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

Stock Service (StockService)

/**
 * 扣库存
 */
@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 internal stock execution 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);
    }
}

The above code demonstrates a complete backend solution for safe, high‑performance stock deduction using Redis, a Lua script, and Spring's dependency injection.