I am working on an Online Booking System. In a normal process when a user selects a room which was marked as 'Available' at a particular time and submits his choice. Now at back-end, just to confirm, I run a SELECT statement to verify the room's status is available or not as follows:
SELECT room_status FROM rooms WHERE room_number = 'XXX'
then I run a UPDATE query to mark it as 'UNAVAILABLE':
UPDATE rooms SET room_status = 'UNAVAILABLE' WHERE room_number = 'XXX' AND room_status = 'AVAILABLE'
Now when I ran SELECT query, the room was available and as I proceed to UPDATE query, the room is taken by other user. So the UPDATE query will fail. I want the room to be taken by first user only. What should I do ?
UPDATE: There can also be a case when a user has to book multiple rooms. For instance user selects 8 rooms. My code was at 4th room checking its status and as it reached 8th room the first room is gone. Also it may happen, while I was checking 4th room and upto now 8th room is gone.
I am using : PHP and MySQL
When selecting the rows, you need to lock them, so that other queries can't read or write from them until you finish updating them. In MySQL, this can be done inside a transaction using the SELECT ... FOR UPDATE statement:
START TRANSACTION;
SELECT room_status FROM rooms WHERE room_number = 'XXX' FOR UPDATE;
All the rows returned from the previous statement will be locked until the end of the current transaction. You can now mark them as unavailable:
UPDATE rooms SET room_status = 'UNAVAILABLE' WHERE room_number = 'XXX';
Remember to close the transaction by commiting the changes. This will release the lock on the rows.
COMMIT;
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments