高并发下如何防止商品超卖少卖

-- 核心SQL：只有库存>0时才执行扣减，返回受影响行数
UPDATE goods SET stock = stock - 1 WHERE id = #{goodsId} AND stock > 0;

-- 先查版本号
SELECT version FROM goods WHERE id = #{goodsId};
-- 扣减时校验版本号
UPDATE goods SET stock = stock -1, version = version +1 
WHERE id = #{goodsId} AND version = #{oldVersion} AND stock >0;

BEGIN;
SELECT stock FROM goods WHERE id = #{goodsId} FOR UPDATE;
-- 业务逻辑
UPDATE goods SET stock = stock -1 WHERE id = #{goodsId};
COMMIT;

-- 核心Lua脚本
local stock = redis.call('GET', KEYS[1])
if not stock or tonumber(stock) < tonumber(ARGV[1]) then
    return 0
end
redis.call('DECRBY', KEYS[1], ARGV[1])
return 1

-- 回补库存脚本，同样简单原子
redis.call('incrby', KEYS[1], ARGV[1])
-- 如果之前标记了售罄，回补后要删除标记，让商品重新上架
redis.call('del', KEYS[2])

-- 文件名：stock_deduct.lua
-- KEYS[1]: 库存key  ARGV[1]: 扣减数量  ARGV[2]: 幂等键(防重复扣减)
local idempotentKey = ARGV[2]
-- 1. 先校验幂等性，防止重复下单扣减
if redis.call('EXISTS', idempotentKey) == 1 then
    return -1 -- -1表示重复请求
end

-- 2. 校验库存是否充足
local stock = redis.call('GET', KEYS[1])
if not stock or tonumber(stock) < tonumber(ARGV[1]) then
    return 0 -- 0表示库存不足
end

-- 3. 原子扣减库存+记录幂等键(过期时间1分钟)
redis.call('DECRBY', KEYS[1], ARGV[1])
redis.call('SET', idempotentKey, '1', 'EX', 60)
return 1 -- 1表示扣减成功

@Service
public class RedisStockService {
    @Autowired
    private StringRedisTemplate redisTemplate;
    
    // 预加载Lua脚本，避免每次编译
    private final DefaultRedisScript<Long> deductScript;
    
    public RedisStockService() {
        deductScript = new DefaultRedisScript<>();
        deductScript.setScriptSource(new ResourceScriptSource(new ClassPathResource("lua/stock_deduct.lua")));
        deductScript.setResultType(Long.class);
    }
    
    /**
     * 原子扣减库存
     * @param goodsId 商品ID
     * @param quantity 扣减数量
     * @param userId 用户ID(用于生成幂等键)
     * @return 1:成功 0:库存不足 -1:重复请求
     */
    public Long deductStock(Long goodsId, Integer quantity, Long userId) {
        String stockKey = "goods:stock:" + goodsId;
        String idempotentKey = "order:idempotent:" + userId + ":" + goodsId;
        return redisTemplate.execute(deductScript, 
            Collections.singletonList(stockKey), 
            quantity.toString(), 
            idempotentKey);
    }
}

@Service
public class DistributedLockService {
    @Autowired
    private RedissonClient redissonClient;
    
    public boolean createOrderWithLock(Long goodsId, Integer quantity, Long userId) {
        String lockKey = "lock:goods:" + goodsId;
        // 等待时间0秒(抢不到直接失败)，持有时间-1(开启看门狗自动续期)
        RLock lock = redissonClient.getLock(lockKey);
        
        try {
            if (!lock.tryLock(0, -1, TimeUnit.SECONDS)) {
                return false;
            }
            
            // 执行业务逻辑：查库存→扣减数据库→生成订单
            return orderService.createOrder(goodsId, quantity, userId);
        } catch (InterruptedException e) {
            Thread.currentThread().interrupt();
            return false;
        } finally {
            // 确保锁释放
            if (lock.isHeldByCurrentThread()) {
                lock.unlock();
            }
        }
    }
}

@Service
public class OrderTimeoutService {
    @Autowired
    private RocketMQTemplate rocketMQTemplate;
    @Autowired
    private RedisStockService redisStockService;
    
    // 订单超时时间：30分钟
    private static final int ORDER_TIMEOUT_LEVEL = 16; // RocketMQ延迟级别16对应30分钟
    
    /**
     * 订单创建成功后，发送延迟消息
     */
    public void sendTimeoutCheckMessage(Long orderId, Long goodsId, Integer quantity) {
        OrderTimeoutMessage message = new OrderTimeoutMessage(orderId, goodsId, quantity);
        rocketMQTemplate.syncSend("order-timeout-topic", 
            MessageBuilder.withPayload(message).build(), 
            3000, 
            ORDER_TIMEOUT_LEVEL);
    }
    
    /**
     * 消费者处理超时订单，回滚库存
     */
    @RocketMQMessageListener(topic = "order-timeout-topic", consumerGroup = "order-timeout-consumer")
    public class OrderTimeoutConsumer implements RocketMQListener<OrderTimeoutMessage> {
        @Override
        public void onMessage(OrderTimeoutMessage message) {
            Long orderId = message.getOrderId();
            // 1. 查询订单状态
            Order order = orderService.getById(orderId);
            if (order == null || order.getStatus() != OrderStatus.UNPAID) {
                return; // 订单已支付或不存在，无需回滚
            }
            
            // 2. 原子回滚Redis库存
            String stockKey = "goods:stock:" + message.getGoodsId();
            redisTemplate.opsForValue().increment(stockKey, message.getQuantity());
            
            // 3. 取消订单
            orderService.cancelOrder(orderId);
        }
    }
}

-- 文件名：stock_shard_deduct.lua
-- KEYS[1..N]: 库存分片key列表  ARGV[1]: 扣减数量  ARGV[2]: 幂等键
local idempotentKey = ARGV[2]
if redis.call('EXISTS', idempotentKey) == 1 then
    return -1
end

-- 随机选择一个分片开始尝试，避免所有请求都打第一个分片
local shardCount = #KEYS
local startIndex = math.random(1, shardCount)

for i = 0, shardCount - 1 do
    local index = (startIndex + i) % shardCount + 1
    local shardKey = KEYS[index]
    local stock = redis.call('GET', shardKey)
    
    if stock and tonumber(stock) >= tonumber(ARGV[1]) then
        redis.call('DECRBY', shardKey, ARGV[1])
        redis.call('SET', idempotentKey, '1', 'EX', 60)
        return 1
    end
end

return 0