基于数据库乐观锁的秒杀实现

简单的秒杀实现方式比较多，常见的有可重入锁、redis分布式锁、线程同步锁、数据库锁等，其中最简单也最容易实现的就是数据库乐观锁了，下面的demo以springboot+Data JPA 框架为基础，利用mysql乐观锁实现了一个简单的秒杀场景，乐观锁本质并不会加锁，只是在进行数据修改做版本校验，校验通过更新数据，否则不更新。本demo仅供了解并发学习之用，还有很多未完善的地方，请大家多多包涵。

订单实体:

@Data
@Entity(name = "tbl_order")
public class OrderDomain {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    private Integer sid;

    private String name;

    private Integer number;

    @Column(name = "create_time")
    private Date createTime;

    public OrderDomain(Integer sid,String name,Integer number){
        this.sid = sid;this.name = name;this.number = number;
    }
}

商品库存实体：

@Data
@Entity
@Table(name = "tbl_stock")
public class StockDomain {

    @Id
    private Integer id;

    private Integer count;

    private Integer sale;

    private String name;

    private Integer version;
}

在mysql中创建对应数据库表

CREATE TABLE `tbl_order` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `sid` int(11) NOT NULL COMMENT '库存ID',
  `name` varchar(30) NOT NULL DEFAULT '' COMMENT '商品名称',
  `number` int(30) NOT NULL COMMENT '数量',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `tbl_stock` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL DEFAULT '' COMMENT '名称',
  `count` int(11) NOT NULL COMMENT '库存',
  `sale` int(11) NOT NULL COMMENT '已售',
  `version` int(11) NOT NULL COMMENT '乐观锁，版本号',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Service层逻辑处理

@Service
public class OrderService {

    private final OrderRepository orderRepository;

    private final StockService stockService;

    @Autowired
    public OrderService(OrderRepository orderRepository, StockService stockService) {
        this.orderRepository = orderRepository;
        this.stockService = stockService;
    }

    //未加锁
    public OrderDomain create(int sid){
        //库存校验
        StockDomain stockDomain = checkStock(sid);
        //减库存
        stockService.updateById(stockDomain);
        //创建订单
        return createOrder(stockDomain);
    }

    //使用乐观锁实现下单秒杀
    public OrderDomain createByOptimistic(int sid){
        //库存校验
        StockDomain stockDomain = checkStock(sid);
        //减库存
        stockService.updateByOptimistic(stockDomain);
        //创建订单
        return createOrder(stockDomain);
    }


    private OrderDomain createOrder(StockDomain stockDomain){
        String name = stockDomain.getName();
        Integer sid = stockDomain.getId();
        OrderDomain orderDomain = new OrderDomain(sid,name,1);
        return orderRepository.save(orderDomain);
    }


    private StockDomain checkStock(int sid){
        StockDomain count = stockService.getCount(sid);
        if(count.getSale().equals(count.getCount()) || count.getSale() > count.getCount()){
            throw new RuntimeException("库存不足");
        }
        return count;
    }
}

@Slf4j
@Service 
public class StockService {

    private final StockRepository stockRepository;

    @Autowired
    public StockService(StockRepository stockRepository) {
        this.stockRepository = stockRepository;
    }

    public StockDomain getCount(int id){

        return stockRepository.findById(id).get();
    }
}

Repository

@Repository
public interface StockRepository extends JpaRepository<StockDomain,Integer>{

    //不加锁更新库存
    @Modifying
    @Query("update StockDomain t set t.sale=:sale where id=:id")
    Integer updateById(@Param("id")Integer id,@Param("sale")Integer sale);

    //使用乐观锁更新数据库库存
    @Modifying
    @Query(value = "update StockDomain t set t.sale=:sale, t.version=:newVersion where t.id=:id and t.version=:oldversion")
    Integer updateByOptimistic(@Param("sale") Integer sale, @Param("id") Integer id,
                       @Param("oldversion")Integer oldversion,@Param("newVersion")Integer newVersion);

}

Controller 层下单接口

@Slf4j
@RestController
@RequestMapping("/order")
public class OrderController {

    private final OrderService orderService;

    @Autowired
    public OrderController(OrderService orderService) {
        this.orderService = orderService;   
    }

    @GetMapping("/create/{sid}")
    @ResponseBody
    public ResponseEntity<String> createOrder(@PathVariable("sid")Integer sid){
        try {
            if(!ObjectUtils.isEmpty(orderService.create(sid))){
                log.info("创建订单成功");
                return ResponseEntity.ok().body("Success");
            }else {

                log.error("创建订单失败");
                return ResponseEntity.ok().body("Failed");
            }

        }catch (Exception e){
            log.error("创建订单失败,exception = {}",e);
            throw new RuntimeException(e.getMessage());
        }
    }

    @GetMapping("/create/optimistic/{sid}")
    @ResponseBody
    public ResponseEntity<String> createByOptimistic(@PathVariable("sid")Integer sid){
        try {
            if(!ObjectUtils.isEmpty(orderService.createByOptimistic(sid))){

                log.info("创建订单成功");
                return ResponseEntity.ok().body("Success");
            }else {
                log.error("创建订单失败");
                return ResponseEntity.ok().body("Failed");
            }
        }catch (Exception e){
            log.error("创建订单失败,exception = {}",e);
            throw new RuntimeException(e.getMessage());
        }
    }  
}

准备20台iphone8

使用Jmeter模拟100并发调用不加锁接口争抢商品，库存20台iphone8，结果卖出77台，严重超卖:

 

库存10台iphoneX,使用Jmeter模拟100并发调用乐观锁接口争抢商品，结果符合预期，没有出现超卖: