MariaDB酒店预订场景如何解决触发器防重复预订的竞态条件问题

问题根源 #

当前触发器存在竞态的核心原因是：BEFORE INSERT触发器中的SELECT默认是快照读，并发事务同时执行查询时，会互相看不到对方未提交的插入记录，最终导致两个冲突的预约都插入成功。

修复步骤 #

1. 补充必要联合索引 #

为了避免后续加锁操作锁全表，同时提升查询效率，首先给房间ID、时间、软删除字段加联合索引，可在线执行不锁表：

ALTER TABLE `reservation_tbl` 
ADD INDEX `idx_room_time_delete` (`room_idx`, `start_datetime`, `end_datetime`, `deleted_datetime`),
ALGORITHM=INPLACE, LOCK=NONE;

2. 修正触发器逻辑 #

核心修改三点：

• 简化时间重叠判断逻辑，通用规则不易出歧义
• 增加软删除过滤，排除已取消的预约记录
• 查询加FOR UPDATE转为当前读，触发InnoDB的行锁+间隙锁，阻塞并发的同房间预约查询，避免幻读

CREATE DEFINER=`admin`@`%` TRIGGER `prevent_double_booking` BEFORE INSERT ON `reservation_tbl` FOR EACH ROW BEGIN
    SET @val = EXISTS (
        SELECT NULL FROM `reservation_tbl` AS existing
        WHERE NEW.room_idx = existing.room_idx
        -- 过滤已取消的预约
        AND existing.deleted_datetime IS NULL
        -- 半开区间时间重叠判断：符合酒店预订「前一个订单12点退房、后一个订单12点可入住」的常规规则，需调整边界可修改比较符
        AND NEW.start_datetime < existing.end_datetime
        AND existing.start_datetime < NEW.end_datetime
        -- 转为当前读加排他锁，阻塞同房间的并发查询
        FOR UPDATE
    );
    IF (@val) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Double Booking Detected';
    END IF;
END

3. 补充UPDATE场景的校验逻辑 #

如果业务支持修改预约的时间、房间号，需要同步新增BEFORE UPDATE触发器，逻辑和上述INSERT触发器完全一致，避免修改预约信息时产生冲突。

注意事项 #

• 保持MariaDB默认的**可重复读（REPEATABLE READ）**隔离级别即可，该级别下的间隙锁可以彻底避免同房间同时段插入的幻读问题
• 若有批量插入预约的需求，需要确保批量操作在同一个事务内执行，避免部分插入成功部分失败的不一致问题
• 单库并发量极高的场景，可配合应用层的房间维度分布式锁做双重校验，进一步降低数据库层面的锁冲突概率

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