如何设计库存扣减表结构，避免超卖又保证高并发性能？

学完这篇，你将掌握利用 SQL 乐观锁和 Redis 原子操作设计高并发库存扣减方案，既能防止超卖，又能保证系统性能。

第一步：设计库存表结构

这一步我们要创建一个包含版本号的库存表，利用版本号实现乐观锁机制，确保数据一致性。

在数据库管理工具（如 Navicat 或 Workbench）中执行以下 SQL 语句：

CREATE TABLE `stock` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `goods_id` int(11) NOT NULL COMMENT '商品ID',
  `name` varchar(50) DEFAULT NULL COMMENT '商品名称',
  `count` int(11) DEFAULT '0' COMMENT '当前库存数量',
  `version` int(11) DEFAULT '0' COMMENT '版本号，用于乐观锁',
  PRIMARY KEY (`id`),
  KEY `idx_goods_id` (`goods_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品库存表';

注意：必须添加 version 字段（初始值为 0）和 goods_id 的索引，这是后续实现乐观锁和快速查询的基础。

第二步：编写基础的扣减 SQL（存在超卖风险）

这一步我们先写一个最简单的逻辑，看看哪里会出问题。

在代码中执行以下 SQL 语句：

UPDATE stock SET count = count - 1 WHERE goods_id = 1 AND count > 0;

注意：这个 SQL 在高并发下是不安全的。比如库存剩 1，两个线程同时读到 count = 1，都会执行减 1 操作，最终库存变成 -1，导致超卖。

第三步：使用乐观锁修复超卖问题

这一步我们要修改 SQL，增加版本号校验，确保数据一致性。

UPDATE stock SET count = count - 1, version = version + 1 
WHERE goods_id = 1 AND count > 0 AND version = 1;

注意：执行后检查 affected_rows（影响的行数）。

- 如果是 1：扣减成功，版本号已自增。

- 如果是 0：说明版本号不匹配，说明有其他事务已经修改过库存，此时应提示“库存不足”并重试或放弃。

第四步：利用 Redis 实现高性能扣减

这一步我们要引入 Redis，利用其原子性操作大幅提升并发性能，减轻数据库压力。

在 Redis 客户端中执行以下命令：

SET stock:1001 10

# 2. 执行原子扣减操作
DECRBY stock:1001 1

注意：Redis 的 DECRBY 是原子操作，不会出现并发扣减错误。如果返回值小于 0，说明库存不足。

第五步：处理扣减结果与回滚

这一步我们要判断 Redis 返回值，如果扣减成功则写入数据库，失败则提示用户。

# 伪代码逻辑示例
redis_count = redis.decrby("stock:1001", 1)
if redis_count < 0:
    # 回滚：恢复 Redis 库存
    redis.incrby("stock:1001", 1)
    return "库存不足"
else:
    # 扣减成功，异步或同步写入数据库
    # UPDATE stock SET count = count - 1, version = version + 1 
    # WHERE goods_id = 1 AND version = current_version
    return "扣减成功"

注意：必须先检查 Redis 返回值，再决定是否回滚，否则会造成数据不一致。

小结

1. 乐观锁：通过 version 字段判断数据是否被修改，解决并发更新冲突，防止超卖。
2. Redis 原子性：利用 DECRBY 命令在高并发场景下保证扣减操作的原子性，提升性能。
3. 结果判断：永远检查 SQL 或命令的返回值（affected_rows 或返回值），以此判断操作是否真正成功。