I was wondering how do you implement the concept of holding a registration of an event or a booking?
For instance, when you try to book a hotel room or a try to book a air ticket online, they hold your registration for say 10 or 15 mins, during which the room or seat will be locked for that time and once the time expires, they release the holding.
A real life example would be events hosting on eventbrite
If I were doing it, I’d implement it in the database.
have a bookedtime field, and a status field.
When someone selects that registration booking, add a record to the table; this record prevents anyone else from selecting that booking. (INSERT INTO bookings(some,fields,bookedtime,status) VALUES(‘quack’,‘moo’,NOW(),0) )
Whenever someone indicates they’re in the process of booking that slot (IE: Every time they page reload in the same session, moving through the checkout process), update the table.
(UPDATE bookings SET bookedtime = NOW() WHERE somefield = ‘someidentifier’
Every X minutes, cron the database with “DELETE FROM bookings WHERE status = 0 AND bookedtime < CURRENT_TIME - INTERVAL 15 MINUTE”.
I was wondering, if we are allowing a 15 mins for the user to complete their whole registration process and there are 3 pages they have to complete in order to book the hotel room, so ain’t we allowing 15 * 3 =45 mins in total?
Should it not allow 15 mins in total to complete the whole registration process i.e. 3 pages?
Whenever someone indicates they’re in the process of booking that slot (IE: Every time they page reload in the same session, moving through the checkout process), update the table.
(UPDATE bookings SET bookedtime = NOW() WHERE somefield = ‘someidentifier’
The booking time is updated on each page interaction and his next section
Every X minutes, cron the database with “DELETE FROM bookings WHERE status = 0 AND bookedtime < CURRENT_TIME - INTERVAL 15 MINUTE”.
Would run and delete items that have a booktime that is too old (15 minutes in this instance)
This would allow the user a new 15 minute window on each page load of your checkout or reservation process. After 15+ minutes of inactivity they would lose their ‘lock’ on whatever was held.
My metacode would theoretically allow infinite amount of time for an order to be completed, if the person refreshes the page in intervals less than 15 minutes (the PHP default for a session).
Could you do it strictly in 15 minute max? Sure. Skip the UPDATE line, and the time gets fixed to 15 minutes period. If your user takes 10 minutes per page, their booking will expire, even though their session did not. (Session gets refreshed every pageload)
An ideal system would not be dependent upon crons, updates, and deletes. There’s too much going on in one table, and with row locks, cron failures, etc, too many holes to potentially break the system. Further, deleting records is just a bad idea in any database. Especially in this case… why would you EVER delete abandonment data? That’s like throwing away money! Anyhow, there’s no need to delete the data for the system to work. Just make your WHERE clause account for (time OR status) when you’re checking for holds, and you’ll be fine. If the booking wasn’t completed in the allotted time, the hold disappears from results.
An ideal enterprise solution to the problem would involve two tables, one for Bookings and one for Booking Holds. The second table would be an insert-and-select-only pipeline that would maintain a list of held slots. Each step inserts a new hold. Select the max(created_date) to check if a hold is valid. Cron Purge the database daily for performance (if you want to… not really necessary).
The solution I presented wouldn’t involve a join, but a union. You would select holds that have not expired and union those results with active bookings. Should appease Rudy.
I have implemented the concept and it works like a charm except for one case. Our QA team has discovered an issue and I am not sure if this is the problem of the code or the database queue request.
Our QA team are testing this by clicking on the “Check Room Availability” button simultaneously on 2 different computers at a single point of time. 2 out of 20 times they succeed in breaking the rule.
For example, for 10th July 2011 only 1 room is available in a hotel. If we proceed with booking this room for the aforesaid date, the application holds the reservation for 15 mins for that user. But if the “Check Room Availability” button is clicked by 2 users at the same point of time, the rule fails 2 times out of 20 attempts.
What do you think the cause of this and how can we prevent it?
I’m assuming you’ve got a proper UNIQUE setup on that table (UNIQUE on hotel,room,bookingtime, or whatever natural key sits on that table)
If so, then the problem is one of microtiming - what you could do is add a column to the table for SESS_ID, passing it the session_id(); result. When you get to the end of the ‘booking cycle’ (IE: The end of the ‘secure this booking’ set, which may very well be the next line after the INSERT), run a SELECT against the table to make sure the booking exists with that user’s session_id. If it doesnt, then someone booked the room at the same exact second, and you should reject the booking attempt.
Let me tell you how the use cases are designed for this application:
User (Customer) lands on the “Room Availability” page.
Selects check-in and check-out date.
Clicks on “Check Availability” button.
System checks for availability of rooms for the aforesaid dates.
If Room is available, system redirects the user to the next page where they are prompted that “congratulations the rooms is available”. This is where the system holds the reservation.
User follows steps to fill up details, make payment etc.
The concept of holding a reservation FAILS when 2 users clicks on the “Check Availability” button at the same point of time.
In alternate to what you suggested, can we avoid this issue using a table WRITE lock?
But the question is what happens, when the WRITE lock is executed at the same point of time for 2 users? Will MySQL let it happen or queue the request?
I’m not a mySQL guru as gets pointed out to me repeatedly, but it is as far as i know impossible for a write lock to be executed at the same point in time - even if both operations start at the same time, the processor will execute one before the other.
I have an interesting question regarding the concept of holding a reservation.
Suppose a registration is being held for a user for 20 mins, he fills up all the required forms (lets assume he spends 15 mins filling up the forms), after that the application takes him to the payment gateway page, where he spends 6-7 mins filling up teh credit card details, name etc.
In the meanwhile a CRON which is set to run every 20 mins, deletes the record of that user from the database since the user has spent more than 20 mins for completing the whole process.
After making a successful payment, when the user is redirected back to the application, the application does not find the matching record and displays an error to him saying your session has timed out etc.
How would you handle or make the application handle such a case where the user has already made the payment?
There’s not really a need for a cron job to garbage clean, it’s creating unneeded drain on the system.
What I simply do is have a field that holds the time that they initiated the reserve.
On the page that displays the open seats, your query would simply look at seats that have no reservation, or hold a reservation time beyond the threshold.
Yes, it will increase size. In most inter-corporate systems (bank to bank, hotel to hotel, agent to whoever and whatever), you will find that records are NEVER deleted for legal purposes, historical audits and other factors. What actually happens is that they change state instead. i.e: customers are put into an inactive state, orders and marked cancelled, reservations are marked expired…
Sometimes it may be worthwhile to move such records to a separate historical database, separate from the one(s) hit by the live system. In any case, most large, distributed systems do not even entertain the concept of ‘delete’. If they do, they are risking a lot…
By the way…instead of setting a booked-time, I’d set and expires-on date, and only if not already set. This way it doesn’t have to be recalculated.
What happens when the user is at the payment gateway (paypal etc) and spends all the time there, will the application still allow them to do the booking?
Time spent at a payment gateway is usually very minimal. Whether your app allows it or not depends on how you code it, and how much time your app allows for the entire process. That’s not really something we can answer for you. We’ve given you some tips on design that should help, but you and your team are going to have to look at the domains language, speak to your domain experts, and come up with a plan. I know this isn’t what you wanted to hear, but honestly, it’s the only one. We can give technical advice, tips from personal experience, but when it comes to a specific business solution, we just aren’t privvy to the things and people required to answer such a thing.
