秒杀系统常见问题—库存超卖

public String buy(Long goodsId, Integer goodsNum) {
    //查询商品库存
    Goods goods = goodsMapper.selectById(goodsId);
    //如果当前库存为0，提示商品已经卖光了
    if (goods.getGoodsInventory() <= 0) {
        return "商品已经卖光了！";
    }
    //如果当前购买数量大于库存，提示库存不足
    if (goodsNum > goods.getGoodsInventory()) {
        return "库存不足！";
    }
    //更新库存
    goods.setGoodsInventory(goods.getGoodsInventory() - goodsNum);
    goodsMapper.updateById(goods);
    return "购买成功！";
}

CREATE TABLE `t_goods` (
  `id` bigint NOT NULL COMMENT '物理主键',
  `goods_name` varchar(64) DEFAULT NULL COMMENT '商品名称',
  `goods_pic` varchar(255) DEFAULT NULL COMMENT '商品图片',
  `goods_desc` varchar(255) DEFAULT NULL COMMENT '商品描述信息',
  `goods_inventory` int DEFAULT NULL COMMENT '商品库存',
  `goods_price` decimal(10,2) DEFAULT NULL COMMENT '商品价格',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

<!--使用redisson作为分布式锁-->
<dependency>
    <groupId>org.redisson</groupId>
    <artifactId>redisson</artifactId>
    <version>3.16.8</version>
</dependency>

import org.redisson.Redisson;
import org.redisson.api.RedissonClient;
import org.redisson.config.Config;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
public class RedissonConfig {
    /**
     * 所有对Redisson的使用都是通过RedissonClient对象
     *
     * @return
     */
    @Bean(destroyMethod = "shutdown")
    public RedissonClient redissonClient() {
        // 创建配置 指定redis地址及节点信息
        Config config = new Config();
        config.useSingleServer().setAddress("redis://127.0.0.1:6379").setPassword("123456");

        // 根据config创建出RedissonClient实例
        RedissonClient redissonClient = Redisson.create(config);
        return redissonClient;

    }
}

public String buyRedisLock(Long goodsId, Integer goodsNum) {
    RLock lock = redissonClient.getLock("goods_buy");
    try {
        //加分布式锁
        lock.lock();
        //查询商品库存
        Goods goods = goodsMapper.selectById(goodsId);
        //如果当前库存为0，提示商品已经卖光了
        if (goods.getGoodsInventory() <= 0) {
                return "商品已经卖光了！";
        }
        //如果当前购买数量大于库存，提示库存不足
        if (goodsNum > goods.getGoodsInventory()) {
                return "库存不足！";
        }
        //更新库存
        goods.setGoodsInventory(goods.getGoodsInventory() - goodsNum);
        goodsMapper.updateById(goods);
        return "购买成功！";
    } catch (Exception e) {
        log.error("秒杀失败");
    } finally {
        lock.unlock();
    }
    return "购买失败";
}

 //查询商品库存
Goods goods = goodsMapper.selectById(goodsId);

原始查询SQL
SELECT *
  FROM t_goods
  WHERE id = #{goodsId}

改写为
 SELECT *
  FROM t_goods
  WHERE id = #{goodsId} for update

`version` int(11) DEFAULT NULL COMMENT '版本'

update t_goods
set goods_inventory = goods_inventory - #{goodsNum},
     version         = version + 1
where id = #{goodsId}
and version = #{version}

public String buyVersion(Long goodsId, Integer goodsNum) {
    //查询商品库存(该语句使用了行锁)
    Goods goods = goodsMapper.selectById(goodsId);
    //如果当前库存为0，提示商品已经卖光了
    if (goods.getGoodsInventory() <= 0) {
        return "商品已经卖光了！";
    }
    if (goodsMapper.updateInventoryAndVersion(goodsId, goodsNum, goods.getVersion()) > 0) {
      return "购买成功！";
    }
    return "库存不足！";
}

 //更新库存
goods.setGoodsInventory(goods.getGoodsInventory() - goodsNum);
goodsMapper.updateById(goods);

 update t_goods
 set goods_inventory = goods_inventory - #{goodsNum}
 where id = #{goodsId}

update t_goods
set goods_inventory = goods_inventory - #{goodsNum}
where id = #{goodsId}
AND (goods_inventory - #{goodsNum}) >= 0

public String buySqlUpdate(Long goodsId, Integer goodsNum) {
    //查询商品库存(该语句使用了行锁)
    Goods goods = goodsMapper.queryById(goodsId);
    //如果当前库存为0，提示商品已经卖光了
    if (goods.getGoodsInventory() <= 0) {
        return "商品已经卖光了！";
    }
    //此处需要判断更新操作是否成功
    if (goodsMapper.updateInventory(goodsId, goodsNum) > 0) {
        return "购买成功！";
     }
    return "库存不足！";
}