库存系统需用乐观锁（version/updated_at）防超卖，订单与库存操作须先落库再驱动变更，实时可用库存应通过视图聚合goods.quantity与inventory_logs各类型delta，辅以合理索引、DECIMAL精度及幂等日志校验。
库存表必须带版本号或时间戳字段
直接用 quantity 字段做减法更新（如 UPDATE goods SET quantity = quantity - 1 WHERE id = 123）在并发场景下大概率超卖。MySQL 的行锁只锁住匹配的行，但不阻止多个事务同时读到旧值再各自扣减。
推荐方案是加乐观锁：在 goods 表中增加 version 或 updated_at 字段，更新时带上条件：
UPDATE goods SET quantity = quantity - 1, version = version + 1 WHERE id = 123 AND quantity >= 1 AND version = 5;
执行后检查 ROW_COUNT() 是否为 1 —— 不是则说明已被其他事务抢先修改，需重试或报错。
-
version方案更精确，适合高一致性要求场景 -
updated_at方案可兼作审计，但要注意 MySQL 的NOW()在事务内不刷新，得用SYSDATE()或应用层传入时间 - 别用
SELECT ... FOR UPDATE简单包住读写 —— 容易引发死锁，且锁粒度大、吞吐低
订单与库存变动必须落库再操作
下单不是“查库存 → 扣减 → 创建订单”三步链路，而是先插入一条带状态的订单记录，再用该记录驱动库存变更。否则网络中断或服务崩溃会导致状态不一致。
典型表结构组合：
-
orders表含status ENUM('created', 'paid', 'cancelled')和created_at -
order_items表存商品 ID、数量、快照单价（避免后续价格变动影响） -
inventory_logs表记录每次变动：谁（order_id）、什么动作（type IN ('reserve', 'confirm', 'cancel')）、影响数量、时间
例如用户下单时：
INSERT INTO orders (user_id, status) VALUES (456, 'created'); INSERT INTO order_items (order_id, goods_id, quantity, price) VALUES (LAST_INSERT_ID(), 123, 2, 99.00); INSERT INTO inventory_logs (order_id, goods_id, type, delta, created_at) VALUES (LAST_INSERT_ID(), 123, 'reserve', -2, SYSDATE());
后续支付成功再补一条 type = 'confirm' 日志，并真正扣减 goods.quantity；超时未支付则走 cancel 流程回滚预留量。
开发语言：java，支持数据库：Mysql 5，系统架构：J2EE，操作系统：linux/Windows1. 引言 32. 系统的结构 32.1 系统概述 33. 功能模块设计说明 43.1 商品管理 43.1.1 添加商品功能模块 53.1.2 商品列表功能模块 83.1.3 商品关联功能模块 93.
查询实时可用库存要合并多张表
用户看到的“剩余库存”不是 goods.quantity 单一字段，而是：goods.quantity - 已确认占用（confirm） + 已取消释放（cancel） - 当前待确认预留（reserve）
推荐用视图封装逻辑，避免每个业务点重复写 JOIN：
CREATE VIEW goods_available AS
SELECT
g.id,
g.name,
g.quantity - COALESCE(r.reserved, 0) AS available
FROM goods g
LEFT JOIN (
SELECT goods_id, SUM(delta) AS reserved
FROM inventory_logs
WHERE type = 'reserve' AND order_id NOT IN (
SELECT id FROM orders WHERE status IN ('cancelled', 'paid')
)
GROUP BY goods_id
) r ON g.id = r.goods_id;注意：IN 子查询在大数据量时性能差，生产环境建议改用 LEFT JOIN orders + WHERE o.status IS NULL OR o.status = 'created'。
- 不要在应用层拼接 SQL 去算可用库存 —— 容易漏掉某类日志类型
- 缓存该视图结果需设短 TTL（如 1–5 秒），不能依赖长期 Redis 缓存
- 后台管理页可查原始
inventory_logs追溯每一笔变动
初始化和迁移要预置约束与索引
没加唯一约束和索引的库存系统，上线后查慢、改错、删不掉数据是常态。
- 给
goods.id加主键，goods.sku加唯一索引（防止重复上架） -
inventory_logs上建复合索引：(goods_id, type, order_id)，覆盖常用查询路径 - 所有金额类字段用
DECIMAL(10,2)，别用FLOAT—— 否则 0.1 + 0.2 ≠ 0.3 - 外键不是必须，但
order_items.goods_id和inventory_logs.goods_id应有索引，否则 JOIN 慢成瓶颈
建表语句里漏掉 ON DELETE RESTRICT 或 CASCADE 意味着后期清理脏数据只能手动 DELETE，风险极高。
实际最难的不是建几张表，而是把「预留→确认→取消」这个状态机跑稳。很多团队卡在库存对不上账，根源不在 SQL 写得对不对，而在没把每一步操作映射到明确的日志记录和幂等校验。
