酒店预订数据库设计中如何防止客房重复预订？

你的核心问题是两步操作的非原子性导致的竞态条件：当多个请求同时执行"查询可用房→插入预订"时，会同时读到相同的可用数，最终导致超售。以下是几种实用的解决方法：

1. 原子化插入（推荐） #

把"检查可用房"和"插入预订"合并成一条SQL语句，利用数据库的原子性保证只有满足条件时才会插入记录，避免竞态。

针对你的表结构，假设用户要预订酒店100的Premium房型（RoomTypeId=2）1间，时段为2023-03-17 14:00到2023-03-20 11:00，SQL如下：

INSERT INTO Bookings (RoomTypeId, HotelId, RoomsBooked, start, end)
SELECT 2, 100, 1, '2023-03-17 14:00', '2023-03-20 11:00'
WHERE EXISTS (
    SELECT 1
    FROM Rooms r
    LEFT JOIN (
        -- 统计该酒店该房型与新预订时段重叠的已预订总数
        SELECT RoomTypeId, HotelId, SUM(RoomsBooked) AS TotalBooked
        FROM Bookings
        WHERE HotelId = 100 
          AND RoomTypeId = 2
          -- 时间重叠判断：已预订的开始时间 < 新预订的结束时间，且已预订的结束时间 > 新预订的开始时间
          AND start < '2023-03-20 11:00' 
          AND end > '2023-03-17 14:00'
        GROUP BY RoomTypeId, HotelId
    ) b ON r.HotelId = b.HotelId AND r.RoomTypeId = b.RoomTypeId
    WHERE r.HotelId = 100 
      AND r.RoomTypeId = 2
      -- 确保新预订后总数不超过总房量
      AND (COALESCE(b.TotalBooked, 0) + 1) <= r.TotalRooms
);

执行这条SQL后，检查影响行数：

• 如果影响行数为1：预订成功
• 如果影响行数为0：没有可用房，预订失败

2. 悲观锁（适合低并发场景） #

通过数据库行锁，在查询可用房时锁定对应的房型记录，阻止其他请求同时读取该数据，直到当前预订事务完成。

步骤如下（需开启事务）：

-- 开启事务
BEGIN TRANSACTION;

-- 锁定目标房型的记录，其他事务需等待锁释放才能操作
SELECT TotalRooms
FROM Rooms
WHERE HotelId = 100 AND RoomTypeId = 2
FOR UPDATE;

-- 统计重叠时段的已预订数
SELECT SUM(RoomsBooked) AS TotalBooked
FROM Bookings
WHERE HotelId = 100 
  AND RoomTypeId = 2
  AND start < '2023-03-20 11:00' 
  AND end > '2023-03-17 14:00';

-- 计算可用房：TotalRooms - TotalBooked，如果大于0则插入预订
INSERT INTO Bookings (RoomTypeId, HotelId, RoomsBooked, start, end)
VALUES (2, 100, 1, '2023-03-17 14:00', '2023-03-20 11:00');

-- 提交事务，释放锁
COMMIT;

注意：高并发场景下，悲观锁会导致请求排队，可能影响性能。

3. 乐观锁+维护已预订数（适合高并发场景） #

调整Rooms表结构，新增BookedRooms字段（记录当前已预订的数量），利用条件更新来保证原子性。

首先修改Rooms表：

ALTER TABLE Rooms ADD COLUMN BookedRooms INT DEFAULT 0;

每次预订时，先尝试更新BookedRooms：

UPDATE Rooms
SET BookedRooms = BookedRooms + 1
WHERE HotelId = 100 
  AND RoomTypeId = 2
  AND BookedRooms + 1 <= TotalRooms;

同样检查影响行数：

• 影响行数为1：更新成功，接着插入Bookings记录
• 影响行数为0：无可用房，预订失败

这种方法不需要显式事务，数据库会保证UPDATE的原子性，性能更好，但需要注意BookedRooms字段的准确性（比如取消预订时要同步减少该值）。

关键注意点 #

• 必须考虑时间重叠：不能统计所有已预订数，只需要统计与新预订时段重叠的预订，因为不同时段的预订不冲突。
• 所有操作必须保证原子性：要么全部成功，要么全部失败，避免数据不一致。

内容的提问来源于stack exchange，提问作者bornfree