How to prevent race conditions in online hotel reservations

Question

I am writing a hotel reservation program using PHP and MySQL. I pretty much did it, but I ran into a race condition problem. For example, only one standard number remains, and when 2 people select it, it shows both of them available. I tried to fix it by checking the room again when the guest pressed the confirmation button before paying, but she still has problems. I also tried to make the state of the room pending when someone who first clicks on confirmation, but I can’t figure out how to change it back to available if the guest decides not to pay or just closes the browser. I searched for SO for answers, but in fact I did not find a definitive answer. thanks in advance

Answer 1

One solution is to add two columns to the table in the database. One column is the session identifier or user identifier, or any user to whom the room is offered. The second column is a timestamp indicating when this offer will expire.

Then, only those rooms that have an expired timestamp in the hold column are displayed in your application. (Set the initial timestamp to 0 so that it starts.) When a room is selected, check the column again. If there is an expected timestamp, the user receives a message "sorry, you were too slow." Otherwise, put there a 15-minute time stamp in the future or something else, and continue.

You often see this on ticketing sites and sites that say something like, “We hold these places for you for another 14 minutes. Please complete the transaction by then or it will be released blah blah blah.”

Answer 2

I will also go to a waiting state. You can save the state along with this user's session identifier and have a cronjob that removes all pending states that have expired session identifiers associated with them.