0

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.

2
  • Which database system and version? The general principle to answer your question will be the same, but the actual implementation and even example code may differ based on your answer to those questions.
    – J.D.
    Mar 6, 2023 at 15:35
  • I am also curious about the general principle as I don't have much experience working on database. At present I am using MySQL 8.0.
    – bornfree
    Mar 6, 2023 at 15:40

1 Answer 1

0

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.

1
  • Does FOR UPDATE lock the rows of both rooms and booking table which match the WHERE criteria? If we assume that rooms table is not going to change and inventory is fixed, the only part that remains is inserting same records in booking table(double booking). can we prevent this using isolation level serializable?
    – bornfree
    Apr 8, 2023 at 8:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.