php - How to prevent race condition in online hotel booking

Question

I am writing a hotel booking software using PHP and MySQL. I am pretty much done with it but I ran into a race condition problem. For example there is only one standard room left and when 2 guests both select it it shows available to both of them. I tried fixing it by checking the room again when the guest clicks confirm before payment but that still has issues. I also tried making the room status to pending when whoever clicks the confirm first 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 SO for answers but I didn't really find a definitive answer. Thanks in advance

Answer 1

One solution is to add two columns to a table in the database. One column is the session ID or user ID or whatever of the user that is being offered the room. The second column is a timestamp indicating when that offer will expire.

Then, in your app, only show rooms that have an expired timestamp in the hold column. (Set the initial timestamp to 0 so that it starts out expired.) When a room is selected, check the column again. If there's an unexpired timestamp there, the user gets a "sorry, you were too slow" message. Otherwise, put a timestamp there for 15 minutes into the future or whatever, and proceed.

You see this on travel sites and ticket-purchasing sites a lot where it says something like "We're holding these seats for you for another 14 minutes. Please complete the transaction by then or it will be released blah blah blah."