Skip to content
E-Commerce Inventory Deduction: From MySQL Row Locks to Redis Lua Scripts

E-Commerce Inventory Deduction: From MySQL Row Locks to Redis Lua Scripts

This article analyzes three approaches to e-commerce inventory deduction—single-row MySQL, sharded MySQL, and Redis with Lua scripts—detailing why database row locks fail under high concurrency and providing a complete Redis-based implementation with distributed locking for cache initialization and atomic stock decrement.

Architect's Guide
Architect's Guide
Architect's Guide

Three Inventory Deduction Approaches

The article presents three common solutions for e-commerce inventory deduction:

Single-row MySQL: Store inventory in one field; update it on each deduction.

Sharded MySQL: Split inventory across multiple rows; route requests to different rows to increase concurrency.

Redis with incrby : Move inventory to Redis and use atomic incrby (via Lua script) for deduction.

Analysis of Database-Based Approaches

Single-Row MySQL

All requests wait for a lock on the same row. Under low concurrency this works, but high concurrency causes massive blocking, request timeouts, and system avalanche. Frequent database access also consumes excessive DB resources.

Sharded MySQL

An optimization of the single-row approach that improves concurrency somewhat, but still performs heavy update operations on the database, consuming significant resources.

Persistent Problems with Database Deduction

Atomicity requirement: Deduction must be a single statement ( update number set x=x-1 where x > 0); a select-then-update pattern causes overselling under concurrency.

MySQL concurrency cliff: Performance rises with thread count until a turning point, then degrades sharply, eventually worse than single-thread.

InnoDB row-lock contention: Concurrent updates on the same row cause lock contention, mutual waiting, deadlocks, and severe performance drops, leading to frontend timeouts.

Redis-Based Solution

Move inventory to cache and use Redis incrby (via Lua) for atomic deduction, solving both overselling and performance issues. Cache loss requires a recovery strategy; e.g., in a lottery system, initial stock = total - already awarded. If rewards are async via MQ, Redis must be re-initialized only after messages are consumed, otherwise inconsistency arises.

Implementation Details

Use a Redis Lua script for atomic check-and-decrement.

Distributed lock (RedisLock) ensures only one service initializes the cache.

Callback interface ( IStockCallback) supplies initial stock during initialization.

Callback Interface

public interface IStockCallback {
    int getStock();
}

StockService Core Logic

@Service
public class StockService {
    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); // double-check
                    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));
        return 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;
            }
        });
    }
}

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

The Lua script returns: -3 (uninitialized), -2 (insufficient stock), -1 (unlimited stock), or >=0 (remaining stock after deduction). The service handles cache initialization with double-checked locking via RedisLock, and supports both cluster and standalone Redis modes.

Original Source

Signed-in readers can open the original source through BestHub's protected redirect.

Sign in to view source
Republication Notice

This article has been distilled and summarized from source material, then republished for learning and reference. If you believe it infringes your rights, please contactadmin@besthub.devand we will review it promptly.

e-commerceredishigh-concurrencymysqldistributed-lockinventory-managementlua-scriptoverselling-prevention
Reader feedback

How this landed with the community

Sign in to like

Rate this article

Was this worth your time?

Sign in to rate
Discussion

0 Comments

Thoughtful readers leave field notes, pushback, and hard-won operational detail here.