From DB Row Locks to Redis + Lua: Evolving Flash‑Sale Inventory Deduction

Incident: Why the Classic UPDATE Fails Under Flash‑Sale Load

A popular SKU with 10,000 units receives ~70,000 purchase requests instantly. The naïve SQL<br/>

UPDATE stock SET stock = stock - 1 WHERE sku_id = ? AND stock > 0;

works at low QPS but collapses under flash‑sale traffic because InnoDB row‑lock contention, connection‑pool exhaustion, long‑running transactions, retry storms, and asynchronous consistency issues are amplified.

Core Requirements of Stock Deduction

Prevent overselling – sold quantity must never exceed real stock.

Avoid lost sales – a successful deduction must eventually be persisted.

Handle massive spikes – the system must stay alive when traffic multiplies by tens or hundreds.

Recoverable & auditable – middleware failures, consumer crashes, network glitches, and node restarts must be compensated.

Stage 1 – Direct Database Deduction (Simple but Limited)

Minimal Implementation

CREATE TABLE seckill_stock (
  sku_id BIGINT PRIMARY KEY,
  available_stock INT NOT NULL,
  locked_stock INT NOT NULL DEFAULT 0,
  version BIGINT NOT NULL DEFAULT 0,
  update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

@Transactional(rollbackFor = Exception.class)
public void createOrder(Long skuId, Long userId) {
  int affected = stockMapper.deductStock(skuId, 1);
  if (affected == 0) {
    throw new BizException("OUT_OF_STOCK");
  }
  orderMapper.insert(OrderEntity.builder()
    .orderNo(IdGenerator.nextId())
    .skuId(skuId)
    .userId(userId)
    .status("INIT")
    .build());
}

Why It Works at Low Concurrency

The SQL guarantees two properties: available_stock >= 1 prevents negative stock.

The single‑row update is atomic, so no application‑level locking is needed.

Why It Breaks Under Flash Sale

Assume 20,000 concurrent requests on the same SKU. Each transaction holds the row lock for ~15 ms (including order creation, coupon handling, logging). Subsequent requests queue, consume threads and connections, and trigger client‑side retries, turning the system into a lock‑waiting bottleneck.

Common Misjudgments

Assuming a fast SQL means the system can handle high QPS.

Believing vertical scaling (adding DB machines) solves the hotspot problem.

Thinking optimistic‑lock version numbers are enough for extreme contention.

Engineering Patch 1 – Protect the Database Before Switching to Redis

Shorten Transaction Scope

Only keep the stock update inside the transaction; move order creation, coupon deduction, messaging, and logging to asynchronous processes.

Entry‑Level Rate Limiting

private final RateLimiter dbRateLimiter = RateLimiter.create(800.0);

public void deductByDbWithProtection(Long skuId) {
  if (!dbRateLimiter.tryAcquire()) {
    throw new BizException("SYSTEM_BUSY");
  }
  stockRepository.deductFromDb(skuId, 1);
}

Hot‑SKU Isolation

Hot items get dedicated thread pools, rate limits, and even separate Redis clusters to avoid dragging normal SKUs down.

Patch Limits

These measures alleviate pressure but do not eliminate the fundamental hotspot on a single DB row; a migration to Redis is still required.

Stage 2 – Redis Pre‑Deduction (Move Hot Path Out of the DB)

Core Idea

Cache the stock in Redis before the event, let requests decrement Redis, and asynchronously sync the result to MySQL.

Typical Pre‑Warm Command

SET stock:{1001} 10000

Additional keys often used: stock:{skuId} – available stock. sold:{skuId} – already sold. seckill:users:{skuId} – set or Bloom filter of buyers. order:deduct:stream – stream for successful deductions.

Why Simple GET + DECRBY Is Not Enough

Separate GET, check, and DECRBY steps are not atomic; two concurrent requests can both see stock = 1, pass the check, and each decrement, causing oversell.

Root Cause

read stock → check → decrement

Because the three steps are independent, another request can interleave, breaking correctness.

Stage 3 – Redis + Lua (Atomic Deduction and Business Checks)

Why Lua Guarantees Atomicity

Redis executes a Lua script as a single, serial operation; no other client commands interleave during execution.

Minimal Lua Script (deduct_stock.lua)

-- KEYS[1] = stock:{skuId}
-- ARGV[1] = quantity
local stock = tonumber(redis.call('GET', KEYS[1]) or '0')
local quantity = tonumber(ARGV[1])
if stock < quantity then
  return -1
end
local remain = redis.call('DECRBY', KEYS[1], quantity)
return remain

Returns -1 for insufficient stock, otherwise the remaining stock.

Production‑Ready Script

-- KEYS[1] = stock:{skuId}
-- KEYS[2] = buyers:{skuId}
-- KEYS[3] = stream.orders
-- ARGV[1] = userId
-- ARGV[2] = orderNo
-- ARGV[3] = skuId
-- ARGV[4] = quantity
-- ARGV[5] = requestId

local stockKey = KEYS[1]
local buyersKey = KEYS[2]
local streamKey = KEYS[3]
local userId = ARGV[1]
local orderNo = ARGV[2]
local skuId = ARGV[3]
local quantity = tonumber(ARGV[4])
local requestId = ARGV[5]

if redis.call('SISMEMBER', buyersKey, userId) == 1 then
  return 2  -- duplicate order
end

local stock = tonumber(redis.call('GET', stockKey) or '0')
if stock < quantity then
  return 1  -- out of stock
end

redis.call('DECRBY', stockKey, quantity)
redis.call('SADD', buyersKey, userId)
redis.call('XADD', streamKey, '*',
  'requestId', requestId,
  'orderNo', orderNo,
  'skuId', skuId,
  'userId', userId,
  'quantity', tostring(quantity))
return 0  -- success

Return codes: 0 – success. 1 – out of stock. 2 – duplicate order.

Why Writing to a Redis Stream Inside the Script Matters

If the application writes the stream after the script and the MQ send fails, the stock is already deducted but the order never materialises – a classic “stock hanging” scenario. Embedding the stream write guarantees that a successful deduction always produces a durable event.

After Redis + Lua – The System Is Still Incomplete

Lua solves the front‑end high‑concurrency safety, but it does not address:

Order creation after deduction.

Compensation when async persistence to MySQL fails.

Idempotent consumption of the stream.

Reconciliation between Redis and the database.

Redis node failures or master‑slave switch‑overs.

Hot‑SKU sharding and isolation.

Back‑pressure handling when downstream cannot keep up.

Production‑Grade Flash‑Sale Architecture

Recommended End‑to‑End Flow

User Request → API Gateway (auth/limit/blacklist)

Seckill Service (local cache, hot‑SKU routing)

Redis Cluster + Lua atomic deduction

Redis Stream / MQ (deduction success events)

Order Consumer (idempotent order creation)

MySQL (final stock update)

Order & Stock tables

Periodic reconciliation task

Monitoring & alerts

Layer Responsibilities

Ingress Layer : login check, activity eligibility, rate limiting, blacklist.

Seckill Service Layer : local cache of activity metadata, hot‑SKU routing, invoke Lua, generate order number, return result.

Async Stream Layer : write successful deduction events, decouple traffic, retry, dead‑letter handling.

Persistence Layer : final stock update, order insertion, idempotent constraints, compensation.

State‑Machine Design – Total, Available, Reserved

Three counters are often used: total_stock – total inventory. available_stock – can be sold now. reserved_stock – pre‑reserved, awaiting payment.

Two typical models:

Direct‑Sell Model : available → sold on successful Lua deduction.

Pre‑Reserve Model : available → reserved → sold after payment; timeout returns reserved → available.

High‑Concurrency Engineering Points

Hot‑SKU Isolation

Separate rate limits, thread pools, and even dedicated Redis clusters for the few explosive SKUs.

Local Two‑Level Cache

JVM‑level cache for activity metadata; fallback to Redis for cache miss.

Asynchronous Shaving (削峰)

Keep the main request path minimal; push order creation, notifications, marketing, and logging to async pipelines.

Back‑Pressure & Capacity Control

If front‑end QPS (e.g., 50 k) exceeds consumer TPS (e.g., 6 k), trigger alerts, additional rate limiting, or circuit breaking to avoid unbounded queue buildup.

Sample Spring‑Boot Implementation

Table Schemas

CREATE TABLE seckill_stock (
  sku_id BIGINT PRIMARY KEY,
  total_stock INT NOT NULL,
  available_stock INT NOT NULL,
  reserved_stock INT NOT NULL DEFAULT 0,
  sold_stock INT NOT NULL DEFAULT 0,
  version BIGINT NOT NULL DEFAULT 0,
  update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE seckill_order (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  order_no BIGINT NOT NULL UNIQUE,
  request_id VARCHAR(64) NOT NULL UNIQUE,
  sku_id BIGINT NOT NULL,
  user_id BIGINT NOT NULL,
  quantity INT NOT NULL,
  status VARCHAR(32) NOT NULL,
  create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE stock_deduct_log (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  request_id VARCHAR(64) NOT NULL UNIQUE,
  order_no BIGINT NOT NULL,
  sku_id BIGINT NOT NULL,
  user_id BIGINT NOT NULL,
  quantity INT NOT NULL,
  process_status VARCHAR(32) NOT NULL,
  retry_count INT NOT NULL DEFAULT 0,
  create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Lua Script Bean Configuration

@Configuration
public class LuaScriptConfig {
  @Bean
  public DefaultRedisScript<Long> seckillDeductScript() {
    DefaultRedisScript<Long> script = new DefaultRedisScript<>();
    script.setLocation(new ClassPathResource("lua/seckill_deduct.lua"));
    script.setResultType(Long.class);
    return script;
  }
}

Facade Service Calling the Script

@Service
public class SeckillFacadeService {
  private final StringRedisTemplate redisTemplate;
  private final DefaultRedisScript<Long> seckillDeductScript;
  private final IdGenerator idGenerator;

  public SeckillFacadeService(StringRedisTemplate redisTemplate,
                               DefaultRedisScript<Long> seckillDeductScript,
                               IdGenerator idGenerator) {
    this.redisTemplate = redisTemplate;
    this.seckillDeductScript = seckillDeductScript;
    this.idGenerator = idGenerator;
  }

  public SeckillResult seckill(SeckillRequest request) {
    Long skuId = request.skuId();
    Long userId = request.userId();
    Integer quantity = request.quantity();
    Long orderNo = idGenerator.nextId();
    List<String> keys = List.of(
        "stock:{" + skuId + "}",
        "buyers:{" + skuId + "}",
        "stream.orders"
    );
    Long result = redisTemplate.execute(seckillDeductScript, keys,
        String.valueOf(userId),
        String.valueOf(orderNo),
        String.valueOf(skuId),
        String.valueOf(quantity),
        request.requestId());
    if (result == null) return SeckillResult.fail("SCRIPT_EXECUTE_ERROR");
    if (result == 1L) return SeckillResult.fail("OUT_OF_STOCK");
    if (result == 2L) return SeckillResult.fail("DUPLICATE_ORDER");
    return SeckillResult.success(orderNo);
  }
}

Why Redis Key Uses Hash Tags

In Redis Cluster, all keys used in a Lua script must reside in the same hash slot. Using a hash tag – e.g., stock:{1001} and buyers:{1001} – forces them onto the same slot, allowing the script to run in cluster mode.

Asynchronous Persistence – From Redis Success to MySQL

Consumer Skeleton

@Service
public class OrderCreateConsumer {
  private final SeckillOrderMapper orderMapper;
  private final StockMapper stockMapper;
  private final StockDeductLogMapper deductLogMapper;

  @Transactional(rollbackFor = Exception.class)
  public void handle(OrderEvent event) {
    if (deductLogMapper.existsByRequestId(event.requestId())) {
      return; // already processed
    }
    deductLogMapper.insert(StockDeductLogEntity.init(event));
    orderMapper.insert(SeckillOrderEntity.builder()
        .orderNo(event.orderNo())
        .requestId(event.requestId())
        .skuId(event.skuId())
        .userId(event.userId())
        .quantity(event.quantity())
        .status("CREATED")
        .build());
    int affected = stockMapper.confirmDeduct(event.skuId(), event.quantity());
    if (affected == 0) {
      throw new IllegalStateException("DB_STOCK_UPDATE_FAILED");
    }
    deductLogMapper.markSuccess(event.requestId());
  }
}

Database Update SQL (Direct‑Sell Model)

UPDATE seckill_stock
SET available_stock = available_stock - #{quantity},
    sold_stock = sold_stock + #{quantity}
WHERE sku_id = #{skuId}
  AND available_stock >= #{quantity};

Pre‑Reserve Model – Two‑Step Updates

When a user reserves stock, the first step moves available → reserved. After payment, a second step moves reserved → sold. If payment times out, reserved → available rolls back.

-- Payment success
UPDATE seckill_stock
SET reserved_stock = reserved_stock - #{quantity},
    sold_stock = sold_stock + #{quantity}
WHERE sku_id = #{skuId}
  AND reserved_stock >= #{quantity};

-- Timeout rollback
UPDATE seckill_stock
SET reserved_stock = reserved_stock - #{quantity},
    available_stock = available_stock + #{quantity}
WHERE sku_id = #{skuId}
  AND reserved_stock >= #{quantity};

Oversell vs. Undersell – Root Causes

Oversell Sources

Non‑atomic GET + DECRBY.

Mixed DB‑cache state without a unified source.

Duplicate‑order checks performed outside the atomic script.

Undersell Sources

Redis deduct succeeds but the downstream MQ send fails.

Consumer crashes without retry.

Payment timeout without stock rollback.

Lack of reconciliation.

Idempotency – The Role of request_id

Every request gets a globally unique request_id. All downstream components use this key to guarantee at‑most‑once processing:

API gateway can cache the response.

Lua script checks the buyer set and returns duplicate code.

Consumer inserts with unique DB constraints; duplicate‑key exceptions are ignored.

Idempotent Consumer Template

@Transactional(rollbackFor = Exception.class)
public void consume(OrderEvent event) {
  try {
    orderMapper.insert(...);
  } catch (DuplicateKeyException ex) {
    return; // already processed
  }
  stockMapper.confirmDeduct(...);
}

Degradation, Rate Limiting, and Circuit Breaking

Multi‑Layer Rate Limiting

Gateway‑level limit to block unauthenticated or pre‑activity traffic.

Application‑level per‑instance limit for hot SKUs.

Hot‑SKU specific limit to protect the rest of the catalog.

Circuit Breaking Targets

Isolate downstream risk services (risk, coupon, recommendation). If they fail, the main stock‑deduction path stays short.

Degradation Strategies

If Redis is unavailable, immediately return “service busy” instead of falling back to DB.

Graceful fallback to simple rules or whitelist when risk checks time out.

Pause the activity if MQ backlog exceeds a threshold.

Real‑World Example – 10 k Stock, 100 k Users

Pre‑Warm Phase

SET stock:{1001} 10000
DEL buyers:{1001}

Request Flow

Gateway validates login and activity window.

Service checks local cache for activity status.

Hot‑SKU rate limiter applies.

Lua script atomically checks duplicate, stock, decrements, and writes to stream.

Response Codes

1

– sold out. 2 – duplicate order. 0 – success, order creation in progress.

Async Order Creation

Consumer reads the stream, uses request_id for idempotency, inserts the order, updates MySQL stock, and marks the log as successful.

Payment Success vs. Timeout

Success moves reserved → sold.

Timeout moves reserved → available and optionally restores Redis stock.

Redis Failure Scenarios

What Not to Do

Never silently switch back to direct DB deduction or keep accepting traffic; that would instantly overload the database.

Recommended Strategy

When Redis is down, put the activity into a degraded or paused state and return “service busy”.

Use a highly available Redis cluster with persistence.

Accept brief unavailability as preferable to a full‑scale DB crash.

Master‑Slave Switch Risks

Asynchronous replication can lose the last few deductions. Mitigate with durable streams, frequent snapshots, and reconciliation.

Reconciliation & Compensation

What to Reconcile

Redis stock vs. MySQL stock.

Deduction logs vs. order table.

Reserved stock vs. paid/cancelled orders.

Sample Reconciliation Job (runs every minute)

@Scheduled(fixedDelay = 60000)
public void reconcileStock() {
  List<Long> skuIds = stockMapper.findHotSkuIds();
  for (Long skuId : skuIds) {
    Integer dbAvailable = stockMapper.getAvailableStock(skuId);
    String cacheValue = redisTemplate.opsForValue().get("stock:{" + skuId + "}");
    int redisAvailable = cacheValue == null ? 0 : Integer.parseInt(cacheValue);
    if (Math.abs(dbAvailable - redisAvailable) > 0) {
      reconcileService.repair(skuId, dbAvailable, redisAvailable);
    }
  }
}

Compensation Must Be Idempotent

All repair actions (re‑push logs, recreate missing orders, restore hanging stock) must use unique keys or upserts to avoid double‑fixes.

Load Testing – Getting Real Insights

Common Mistakes

Testing with many SKUs instead of a single hot SKU.

Even request rate instead of spike traffic.

Omitting retries and timeouts.

Only measuring API QPS, ignoring DB lock wait and MQ backlog.

Recommended Metrics

Request volume – peak entry capacity.

Success rate – business‑acceptable purchase rate.

Lua P99 latency – whether the script becomes the bottleneck.

Redis CPU – hot‑activity pressure on the cache layer.

Message backlog – async pipeline health.

DB update success – final persistence stability.

Reconciliation diff – consistency risk.

Design Advice for a New Flash‑Sale System

Stage 1 – Early Phase : Direct DB deduction, shorten transactions, basic rate limiting, monitor lock waits.

Stage 2 – Growth Phase : Warm stock to Redis, use Lua atomic deduction, enforce one‑order‑per‑user inside the script, async order creation.

Stage 3 – Mature Production : Hot‑SKU isolation, MQ/Stream shaving, pre‑reserve state machine, full idempotency, automated reconciliation, comprehensive degradation & load‑test suite.

Key Takeaway

Redis + Lua solves the “how to safely deduct under massive concurrency”, but a production‑ready flash‑sale system must also answer “how to reliably persist, compensate, and reconcile the deduction”. The evolution moves from a single‑point DB approach to layered caching, atomic scripting, asynchronous pipelines, eventual consistency, and robust operational safeguards.