Stock Deduction Strategies: Database vs. Redis with Distributed Lock Implementation in Java

In everyday development many systems need to decrement inventory, such as e‑commerce product stock or prize stock in a lottery system.

Solution

Use a MySQL database with a single column to store the stock and update this field on each decrement.

Still use a database but split the stock into multiple rows, routing requests to different rows to increase concurrency, though database updates remain heavy.

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

Analysis

The first two methods are database‑centric. The single‑record approach blocks all requests on a lock; under high concurrency this leads to request time‑outs and heavy DB load, causing system avalanche. The sharded‑record method improves concurrency slightly but still incurs massive DB updates.

Problems with database‑based stock deduction include:

Stock decrement must be atomic; a separate SELECT followed by UPDATE can cause overselling. Example SQL:

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

MySQL performance degrades sharply after a certain concurrency level.

High concurrency on a single row leads to InnoDB row‑lock contention, possible deadlocks, and front‑end time‑outs.

Redis‑Based Approach

To solve the above issues, the third solution puts stock in Redis and uses a Lua script to perform atomic decrement, eliminating overselling and improving performance. Cache loss requires a recovery strategy, e.g., re‑initialising stock after MQ consumption.

Specific Redis Implementation

Use a Redis Lua script for atomic decrement.

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

Provide a callback function to fetch the initial stock value.

Stock 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"
        );
        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();
    }
    // ... (methods stock, addStock, getStock, etc.)
    private Long stock(String key, int num) {
        List
keys = new ArrayList<>();
        keys.add(key);
        List
args = new ArrayList<>();
        args.add(Integer.toString(num));
        long result = redisTemplate.execute(new RedisCallback
() {
            @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;
    }
}

Controller Usage Example

@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: fetch initial stock from DB or other source
        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);
    }
}

References

http://www.cnblogs.com/billyxp/p/3701124.html

http://blog.csdn.net/jiao_fuyou/article/details/15504777

https://www.jianshu.com/p/48c1a92fbf3a