MySQLの悲観的ロックと楽観的ロックの実装スキーム

-- Goods table
CREATE TABLE `goods` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `goods_name` varchar(50) NOT NULL,
  `price` decimal(10,2) NOT NULL,
  `stock` int(11) DEFAULT '0',
  `version` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

-- order table
CREATE TABLE `t_order` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `goods_id` bigint(20) NOT NULL,
  `order_time` datetime NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

// Place an order
private boolean order(){
    Goods goods = goodsMapper.selectById(1L);
    boolean success = false;
    if (goods.getStock() > 0) {
        goods.setStock(goods.getStock() - 1);
        // Update the inventory
        goodsMapper.updateById(goods);
        // Create an order
        orderMapper.save(goods.getId());
        success = true;
    }
    return success;
}

@Select("SELECT * FROM goods WHERE id = #{id} FOR UPDATE")
Goods selectForUpdate(Long id);

@Update("UPDATE goods SET stock = #{stock},version = version+1 WHERE id = #{id} AND version = #{version}")
int updateByVersion(Long id, Integer stock, Integer version);

boolean order(){
    Goods goods = goodsMapper.selectById(1L);
    boolean success = false;
    if (goods.getStock() > 0) {
        goods.setStock(goods.getStock() - 1);
        // Update the stock with the version number
        int result = goodsMapper.updateByVersion(goods.getId(), goods.getStock(), goods.getVersion());
        if (result <= 0) {
            // The update failed, indicating that the data has been modified by other threads during the period and needs to be retried recursively
            return order();
        }
        // Create an order
        orderMapper.save(goods.getId());
        success = true;
    }
    return success;
}