Implementing High‑Concurrency Stock Deduction with MySQL, Redis, Lua Scripts and Distributed Locks

In daily development, many scenarios require decrementing inventory, such as e‑commerce product stock or lottery prize stock.

Solution

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

Store stock in multiple rows to increase concurrency, but still suffers heavy DB writes.

Put stock in Redis and use the INCRBY command to decrement.

Analysis

Single‑row DB approach blocks under high concurrency, leading to timeouts and DB overload. Multi‑row DB improves concurrency slightly but still generates many updates. Direct DB updates can cause over‑deduction if SELECT and UPDATE are separate, and MySQL performance degrades sharply after a certain concurrency level, with row‑level lock contention and possible deadlocks.

Redis based solution

Using Redis eliminates over‑deduction and improves performance, but requires a recovery plan for cache loss. The article presents a Lua script that atomically checks stock, handles unlimited stock (-1), insufficient stock, and returns the remaining quantity.

if (redis.call('exists', KEYS[1]) == 1) then
    local stock = tonumber(redis.call('get', KEYS[1]));
    local num = tonumber(ARGV[1]);
    if (stock == -1) then
        return -1;
    end;
    if (stock >= num) then
        return redis.call('incrby', KEYS[1], 0 - num);
    end;
    return -2;
end;
return -3;

Implementation Details

The Java service uses a RedisTemplate , a Lua script stored in STOCK_LUA , and a distributed lock ( RedisLock ) to ensure only one instance initializes stock from the database. The IStockCallback interface provides the initial stock value.

/**
 * 获取库存回调
 */
public interface IStockCallback {
    int getStock();
}

StockService contains methods to decrement stock, add stock, and retrieve current stock, handling uninitialized stock, unlimited stock, and insufficient stock cases.

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;
}

A simple REST controller demonstrates how to call the service.

@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;
    }
}

Overall, the article recommends the Redis‑Lua approach with distributed locking as the most reliable method for high‑concurrency stock deduction.