How to prevent double booking of a room in this hotel booking database design?

Question

I have two tables for hotel booking. One is rooms table where I save room type, hotelId and total number of rooms that the hotel has for that type.

HotelId	RoomTypeId	RoomType	TotalRooms
100	2	Premium	24
100	3	SeaView	10

I have Bookings table where I keep record of all the reservations.

BookinId	RoomTypeId	HotelId	RoomsBooked	start	end
1001	2	100	3	2023-03-17 14:00	2023-03-20 11:00
1002	2	100	2	2023-03-17 14:00	2023-03-20 11:00
1003	3	100	1	2023-03-17 14:00	2023-03-20 11:00

When a user tries to book a room of a specific type(say premium) in a hotel. I do below two things:

1. Using sql query, get the sum of number of all the premium rooms booked in the hotel from Bookings table and subtract it from the TotalRooms of the premium room for that hotel from Rooms table. This gives me the number of available rooms.
2. If available rooms > 0, I insert a new booking record in the Bookings table.

How can I prevent double booking in this scenario? For example, when there is only one premium room left and two users try to book it for the same DateTime, step 1 may get that available rooms is 1 for both users and then step 2 may insert duplicate record in Bookings table.

Answer 1

The following pseudo-code demonstrates the "general principle" for "Check for availability, then either consume the item or abort".

Use "transactions", and be sure to include FOR UPDATE:

START TRANSACTION;
SELECT ... AS rooms_available
    FROM ... JOIN ... ON ...
    FOR UPDATE;
if rooms_available > 0 then
    UPDATE ... SET rooms_booked = rooms_booked - 1 WHERE ...;
    say 'reserved'
    COMMIT;
else
    ROLLBACK;
    say 'no rooms available'

Using ENGINE=InnoDB on the table, plus FOR UPDATE and BEGIN...COMMIT/ROLLBACK are the SQL pieces to us in MySQL.