sql prevent double booking with trigger-CodePudding

I am stumbled on constructing a DB table for hotel reservations.

In my reservation db, there is a reservation start_datetime and end_datetime attribute to indicate booked period and I tried to prevent double booking with a trigger statemen, but I am certain that this method would be vulnerable to race condition.

How can I prevent race condition in such case?

Trigger:

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 (
            ( new.start_datetime <= existing.start_datetime AND existing.start_datetime < new.end_datetime )
            OR ( new.start_datetime < existing.end_datetime AND existing.end_datetime <= new.end_datetime )
            OR ( existing.start_datetime <= new.start_datetime AND new.start_datetime < end_datetime )
            OR ( existing.start_datetime < new.end_datetime AND new.end_datetime <= end_datetime )
        )
    );
    IF (@val) THEN
        signal SQLSTATE '45000' SET MESSAGE_TEXT = 'Double Booking Detected';
    END IF;
END

Table:

CREATE TABLE `reservation_tbl` (
    `reserv_idx` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `member_idx` INT(11) UNSIGNED NOT NULL,
    `room_idx` INT(11) UNSIGNED NOT NULL,
    `start_datetime` DATETIME NOT NULL,
    `end_datetime` DATETIME NOT NULL,
    `created_datetime` DATETIME NOT NULL DEFAULT current_timestamp(),
    `updated_datetime` DATETIME NULL DEFAULT NULL ON UPDATE current_timestamp(),
    `deleted_datetime` DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (`reserv_idx`) USING BTREE,
);

Thanks

ps. The DB at play is MariaDB 10.3.31.

CodePudding user response：

CREATE DEFINER=`admin`@`%` TRIGGER `prevent_double_booking` 
BEFORE INSERT ON `reservation_tbl` 
FOR EACH ROW 
BEGIN
    IF EXISTS ( SELECT NULL 
                FROM `reservation_tbl` AS existing
                WHERE NEW.room_idx = existing.room_idx
                  AND new.start_datetime <= existing.end_datetime 
                  AND existing.start_datetime <= new.end_datetime ) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Double Booking Detected';
    END IF;
END

If the timeslots cannot be adjacent then use strict unequiations instead of soft one.

For to prevent concurrent proccesses cross-influence lock the table before insertion. Or insert in the transaction of according isolation level.