基于数据库乐观锁的防超卖控制

假设上层缓存已经做了限流（缓存限流）。

那么需要在一个事务中完成查库存，扣库存，创建订单的操作。借助数据库乐观锁机制可以轻松完成。

@Service
@Transactional(rollbackFor = Exception.class)
public class ShopOrderServiceImpl implements ShopOrderService {
    @Autowired
    private ShopStockMapper shopStockMapper;

    @Autowired
    private ShopOrderMapper shopOrderMapper;

    @Override
    public boolean createOrder(Long stockId) throws Exception {
        //检查库存数
        ShopStock shopStock = shopStockMapper.getStockCount(stockId);
        if (shopStock.getCount() == 0){
            throw new StockSoldOutException(ConstantUtil.SNATCH_DEAL_FINISHED);
        }

        //扣除库存
        shopStock.setCount(shopStock.getCount() - 1);
        if (shopStockMapper.reduceOneStock(shopStock) == 0){
            throw new StockReduceFaildException(ConstantUtil.STOCK_REDUCE_FAILED);
        }

        //创建订单
        ShopOrder shopOrder = new ShopOrder();
        shopOrder.setOrderId(RandomUtils.random());
        shopOrder.setStockId(shopStock.getStockId());
        shopOrder.setCreatedTime(new Date());
        shopOrder.setModifiedTime(new Date());
        if (shopOrderMapper.createOrder(shopOrder) != 1){
            throw new OrderCreateFailedException(ConstantUtil.ORDER_CREATE_FAILED);
        }

        return true;
    }
}

1. 查库存，如果库存数为0，则秒杀结束

SELECT stock_id,count,version,created_time,modified_time
FROM shop_stock
WHERE stock_id = #{stockId, jdbcType=BIGINT}

2. 库存减一

UPDATE shop_stock
SET count = #{count, jdbcType=INTEGER},
version = version + 1
WHERE stock_id = #{stockId, jdbcType=BIGINT}
AND version = #{version, jdbcType=INTEGER}

3. 创建订单

INSERT INTO
shop_order(order_id, stock_id, created_time, modified_time)
VALUES (#{orderId, jdbcType=VARCHAR},
#{stockId, jdbcType=BIGINT},
#{createdTime, jdbcType=TIMESTAMP},
#{modifiedTime, jdbcType=TIMESTAMP})