Stock Deduction Strategies Using MySQL and Redis with Lua Scripts

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 to increase concurrency, though database access remains heavy.

Place stock in Redis and use the INCRBY command to deduct stock, solving overselling and performance problems.

Analysis

Both the first and second approaches rely on database updates. The single‑row method blocks all requests on a lock, leading to time‑outs and resource exhaustion under high concurrency. The multi‑row method reduces contention slightly but still generates many database writes.

Database‑Based Single Stock

All requests wait for a lock; suitable only for low traffic. High traffic causes request blocking, time‑outs, and potential system avalanche.

Database‑Based Multi Stock

Improves concurrency by sharding stock across rows, yet still suffers from heavy database write load. Additionally, MySQL's default REPEATABLE READ isolation can cause overselling unless the isolation level is changed to READ COMMITTED.

Redis‑Based Deduction

Using Redis INCRBY avoids overselling and improves performance, but cache loss requires a recovery strategy. For example, in a lottery system, the initial stock equals total stock minus already awarded prizes; if asynchronous award processing is used, the cache must be re‑initialized after MQ consumption.

Redis INCRBY Command

The command increments a numeric key by a specified amount. If the key does not exist, it is initialized to 0 before incrementing. Errors are returned for non‑numeric values, and the result is limited to a signed 64‑bit integer.

Syntax

redis 127.0.0.1:6379> INCRBY KEY_NAME INCR_AMOUNT

Supported Version

>= 1.0.0

Return Value

The key's value after adding the increment.

Specific Implementation Using Redis and Lua

Use a Redis Lua script to perform atomic stock deduction.

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

Provide a callback function to fetch the initial stock during initialization.

After deduction, optionally update the database asynchronously to keep consistency.

For the full Lua script, refer to the Redis script documentation.

Lua Script Benefits

Lua scripts reduce network overhead by sending all commands in a single request and guarantee atomic execution because Redis processes a script as a single command.

Important Points

Works in single‑node, master‑slave, and Sentinel modes, but not in sharded cluster mode.

Avoid loops and long‑running scripts; Redis limits script execution to 5 seconds.

Stock Callback Interface (Java)

/**
 * Get stock callback
 * @author yuhao.wang
 */
public interface IStockCallback {
    /**
     * Get stock
     * @return int
     */
    int getStock();
}

StockService Implementation (Java)

package com.xiaolyuh.service;

import com.xiaolyuh.lock.RedisLock;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.data.redis.connection.RedisConnection;
import org.springframework.data.redis.core.RedisCallback;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.stereotype.Service;
import redis.clients.jedis.Jedis;
import redis.clients.jedis.JedisCluster;

import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.TimeUnit;

/**
 * Stock deduction service
 * @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;");
        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;
    }
    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;
    }
    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, "Expiration time cannot be 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;
    }
}

Controller Usage (Java)

@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: fetch initial 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);
    }
}

Additional Considerations

When using RedisTemplate with default JDK serialization, numeric values become binary objects, preventing INCRBY operations. Switching to StringRedisTemplate or customizing the value serializer to store plain strings resolves this issue.

@Bean
public RedisTemplate
redisTemplate(RedisConnectionFactory factory) {
    StringRedisTemplate template = new StringRedisTemplate(factory);
    Jackson2JsonRedisSerializer jackson2JsonRedisSerializer = new Jackson2JsonRedisSerializer(Object.class);
    ObjectMapper om = new ObjectMapper();
    om.setVisibility(PropertyAccessor.ALL, JsonAutoDetect.Visibility.ANY);
    om.enableDefaultTyping(ObjectMapper.DefaultTyping.NON_FINAL);
    jackson2JsonRedisSerializer.setObjectMapper(om);
    template.setValueSerializer(jackson2JsonRedisSerializer);
    template.setHashValueSerializer(jackson2JsonRedisSerializer);
    template.afterPropertiesSet();
    return template;
}

Potential Problems

Using increment on a non‑numeric Redis value throws ERR value is not an integer or out of range .

Spring's RedisTemplate does not support Lua script execution in cluster mode; the workaround is to obtain the native Jedis/JedisCluster connection and call eval directly.

By combining Redis atomic Lua scripts, distributed locking, and proper serialization, the stock deduction process becomes highly concurrent, avoids overselling, and maintains consistency with the underlying database.