演出票务小程序：高并发抢票、选座与防超卖的工程方案

CREATE TABLE show_session (
 id            BIGINT PRIMARY KEY AUTO_INCREMENT,
 show_id       BIGINT NOT NULL,
 session_date  DATETIME NOT NULL,
 total_tickets INT NOT NULL,
 status        TINYINT NOT NULL DEFAULT 1 COMMENT '1预售 2开售 3售罄 4已结束',
 UNIQUE KEY uk_show_date (show_id, session_date)
);

CREATE TABLE ticket_order (
 id           BIGINT PRIMARY KEY AUTO_INCREMENT,
 order_no     VARCHAR(32) NOT NULL,
 session_id   BIGINT NOT NULL,
 seat_id      BIGINT NULL COMMENT '对号入座场次才有值',
 zone         VARCHAR(20) NULL COMMENT '区域，不对号入座用',
 uid          BIGINT NOT NULL,
 price        INT NOT NULL COMMENT '单位分',
 status       TINYINT NOT NULL DEFAULT 1
 COMMENT '1待支付 2已支付 3已出票 4已取消 5已退票',
 expire_at    DATETIME NOT NULL COMMENT '待支付截止时间',
 created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uk_order_no (order_no),
 KEY idx_session_status (session_id, status),
 KEY idx_uid_session (uid, session_id)
);

async function createOrder(sessionId, uid, qty, price) {
 // 1. 限购校验：同一用户同一场次限购 N 张
 const bought = await redis.get(`bought:${sessionId}:${uid}`);
 if (bought && +bought + qty > 4) throw new Error('超出限购数量');

 // 2. Redis 预扣（DECR 原子操作，天然不超卖）
 const remain = await redis.decr(`stock:${sessionId}`);
 if (remain < 0) {
 await redis.incr(`stock:${sessionId}`);   // 回补
 throw new Error('本场次已售罄');
 }

 try {
 // 3. 数据库条件更新兜底：库存充足才创建订单
 const r = await db.query(
 `UPDATE show_session SET total_tickets = total_tickets - ?
 WHERE id = ? AND total_tickets >= ? AND status = 2`,
 [qty, sessionId, qty]);
 if (r.affectedRows === 0) throw new Error('售罄');

 // 4. 限购计数，TTL 到场次结束
 await redis.incr(`bought:${sessionId}:${uid}`);
 await redis.expire(`bought:${sessionId}:${uid}`, 86400);

 const orderNo = genOrderNo();
 await db.query(
 `INSERT INTO ticket_order (order_no, session_id, uid, price, qty, expire_at)
 VALUES (?,?,?,?,?, NOW() + INTERVAL 10 MINUTE)`,
 [orderNo, sessionId, uid, price, qty]);

 return orderNo;
 } catch (e) {
 await redis.incr(`stock:${sessionId}`);   // 失败回补
 throw e;
 }
}

async function lockSeats(sessionId, seatIds, uid) {
 const key = `seats:${sessionId}`;
 // 原子方式把用户想买的座位加入已锁集合
 // 用 Lua 保证"检查 + 锁定"原子性
 const lua = `
 local locked = redis.call('SMEMBERS', KEYS[1])
 for i, s in ipairs(ARGV) do
 if locked[s] then return s end
 end
 for i, s in ipairs(ARGV) do redis.call('SADD', KEYS[1], s) end
 return 'OK'`;
 const conflict = await redis.eval(lua, 1, key, ...seatIds);
 if (conflict !== 'OK') throw new Error(`座位 ${conflict} 已被锁定`);

 await redis.expire(key, 300);   // 锁 5 分钟
 await redis.hset(`seat_owner:${sessionId}`,
 seatIds.join(','), uid);
}

-- 定时任务：取消过期未支付订单
UPDATE ticket_order SET status = 4
 WHERE status = 1 AND expire_at < NOW();
-- 回补对应场次库存（按取消量）
UPDATE show_session s JOIN (
 SELECT session_id, SUM(qty) q FROM ticket_order
 WHERE status = 1 AND expire_at < NOW() GROUP BY session_id
) t ON s.id = t.session_id
 SET s.total_tickets = s.total_tickets + t.q;