Prevent race condition and lock tables in hotel booking system PHP/MySQL

Question

I am building a hotel style reservation system and I am loosing the way. I've got stuck at the exactly reservation moment when to add the reservation and I am concerned about come situations.

My flow is really easy, it lets multiple user to go through the reservation process for the same room until one of them press the reservation button before the others. The reservation information is kept in the session and is never stored in the db until somebody reach last step and press "book". In every step, the system check if the room/s is/are available if not it gives the error page.

Now the problem is preventing race condition combined to a last one mandatory check if still there are enough rooms left:

When user is in last step, he/she select the payment method and press book button. After checking if the selected payment option is ok (form data hasn't been altered) it redirects to the reservation controller where:

reservation controller

<?php
ReservationController extends JControllerLegacy{

public function placeReservation(){

    //Do some checks to prevent direct access, booking existence and user completed all steps

    $reservatioModel = $this ->getModel('Reservation')
    if(!$reservationModel->placeReservation()){
         //set errors and redirect to error page
         return false;
    }

    //booking had been placed, passes the data to payment plugin
}

?>

and

Reservation model:

 <?php
 ReservationModel extends JModelLegacy{

public function placeReservation(){

    //Get all variables and objects

    //Lock the tables to prevent any insert while the last check
    $db ->lockTable(#__bookings);
    $db ->lockTable(#__booking_room);

    //Perform the last mandatory check with tables locked to prevent read the table right one moment before another transaction insert the booking and allowing overbooking. THIS CHECK MUST BE 100% SURE NO ONE ELSE IS MANIPULATING/READING THE TABLE
    if(!$this ->checkRoomsAvailability($data))
        return false;

    try{
        $db ->transactionStart();

        //Add the reservation in the booking table
        if(!$this ->_saveBooking()){
           $db ->rollBack();
           return false;
        }

        //Add the room/s to the middle table #__booking_room
        if(!$this -> _saveRooms())
           $db ->rollBack();
           return false;
        }

        $db ->transactionCommit();

    }catch(Exception $e){

        $db ->rollBack();
        $this ->setError('We were not able to complete your reservation.');
        return false;
    }

    $db ->unlockTables();

}
 ?>

The above mentioned tables are InnoDB, #__bookings hold the bookings and #__booking_room hold the information for the selected rooms (like avg_price, number of rooms, etc) and it has an foreign key to #__rooms and one to #__bookings

What am I concerned about?

1 - I am using other tables during the last availability check than the two locked and is giving me error.

2 - When the second user's execution arrives at the table locking point, does it wait until they get free or does it raise an error/exception? I could catch the exception and redirect user to the error page but locket table doesn't means reservation placed, it can occurs any issue while saving the information.

I hope my workflow is ok, please let me know if I am good to go or I should improve my code.

Thanks

EDIT:

To be mentioned, the business idea is like i.e. Expedia, booking, etc where you are allowed to select more rooms types and units per reservation. Indeed, my room table is not for specific/real room but is meant to be for room type with a fixed number of units available everyday if not already booked.

Answer 1

My approach would be simpler. In the table that stores the actual reservation, I would have a foreign key for the specific room being reserved, and have a UNIQUE index for the fields roomID, date. This will ensure that the same roomID cannot be recorded twice for the same date.

Next, when the customer confirms booking, I would run everything in a transaction as you're currently doing. When the code gets to the last place and tries to insert a new reservation, if a moment before another customer reserved that room, the DB will not allow you to insert another reservation for that room for that date. That's when I would either:

• rollback and throw the error, or
• try again with another room (with its own roomID) if there is another room of the same type still available

Answer 2

I can not say if your workflow aka business domain is the right choice as it depends on your business. But technically you want to have a booking transaction which also provides a check against double booking. The easiest way could be a room-booking-dates table with unique constraint on room is and date. So during your booking transaction you also insert each day with the room id into the table and the unique constraint will ensure that the second attempt will fail.

PS: If a table is locked, PHP will just wait for the unlock. No exception will be thrown (at least if the connection does not close after x seconds)