How to prevent overlap booking on a calendar booking system

Question

As an exercise, I am trying to design a simple calendar booking system for multiple meeting rooms. I kind of got my head around some requirements such as find available rooms for a given time range, room booking look up. However, I seem to stuck a bit on a booking scenario, say in a particular racing situation, 2 users are trying to book room A at the exact same time, the time range that they pick can be exact same or overlapped. For example, user 1 is trying to book room A from 10AM to 12PM, user 2 is trying to book room A from 9AM to 11AM. As the time of look up availability this room showed up for both user saying it is available in the asked timeframe. In this case, since they are overlapped, I can only accept one booking and fail the other, for simplicity I won't give any user preference but just a first come first serve. How would I resolve this part in an efficient manner? I was thinking to approach this in several ways:

1. Have the booking passed through in the request, post process the booking using a queue, every time I dequeue a booking I will perform the availability check before actually insert into DB confirm the booking. In the propose scenario, only one booking can get into the queue first hence the second one would fail. Then the system will turn around and notify the users about their booking either it fail or passed. (I realized this is similar to how Outlook handle their meeting room set up). But this a short coming, this force me to process the queue in a single thread then I can maintain the ordering, if I get two thread dequeue them, I ran back in the circle that now two thread will see the same result when doing the recheck condition as I plan to distribute this process.

2. Try to put a lock on the time slot. But this only work when I have pre-set up time slot for the room (for example, fixed from 9-10, 10-11,...), this defeat my purpose to keep this booking system open for any time range. And for this, would read on write is acceptable in such a system when talking to database? Because if I am putting an optimistic lock on the record, I would need to read the row to compare version before writing into DB?

3. Another way that I could see this work is just let the request go through, then having another process re-check the booking calendar to detect overlapping and only keep the first valid booking. But I feel this is not efficient as I have to do this for every booking and if the room is popular, and many user would like to book it. It would take a lot of time to turn around.

If you had to design such a system, how would you go about at it? Is there anyway we can confirm the booking in realtime. How would the hotel booking system work where the booking unit is in day which would face the same problem given the hypothetical situation, there is only one room that users are trying to book?

Answer 1

A queue looks like an elegant solution to this problem. Decent queueing implementations ensure that only one message is seen by one de-queue operation so the condition you describe wouldn't occur (you weren't thinking of writing your own queue were you? Please don't do this).

Answer 2

When you search anything in agoda, booking or any flight/hotel searching platforms; you give a particular time range, you then choose a flight/hotel, and proceed to checkout - you have seen a timer at top. The timer has a limited time, most commonly 5 or 10 mins. And you have to make your purchase within that slot.

The timer is ensuring a few things -

1. if you were seeing the rooms/flights were available in that date range, they remain available, it's holding it for you till that timer expires
2. if you were seeing the rooms were available in a certain price, it will also ensure within that time range the room rate remains the same, even if hotel owner wants to change during that time for a particular room, it won't be immediately reflected - within the hold time, you see what you get

To implement such a hold-release mechanism(basically a lock), most implementations use a fast lookup temporary storage, like redis. You should not use a persistent storage here, like any database.

The flow you should have is-

1. User 1 picked the room, and proceeds to checkout, you immediately create a lock on that room resource in your redis cache. How you decide your key will help you how efficiently you implement the locking
2. User 2 is still searching, then should not see any locked resources, if the timeframe overlaps
3. User 2 is in a search page where he already chose the room and about to proceed to checkout. Before loading checkout page, you are again checking your redis and denying that it's not available right now (like you see in similar sites)

If you have multiple replicas of redis, due to replication lag, you can still get into a situation when both are still able to proceed till checkout. But again, this is where the timer may come to rescue; you can do a continuous polling to see if the state is still valid. If your lookup now returns that you can not book anymore, you show relevant experience(i.e. someone is already booking or something)!

You can even implement smarter solutions by doing a socket push. Send a denial push if somehow in your background check you see that current checkout started at X time but in X - 0.001 time there's already another checkout in progress, which is now available in your redis.

I hope this gives you some idea on how you can implement it.

Answer 3

One way is to break down the day into 15 minute increments. Then simply create a primary key by room, date, and interval ID. If someone tries to insert another record a primary key error will occur. One just needs one table for this approach. You will not need transactions because the database is atomic and will not allow duplicate primary keys.

If you want something like being able to book any time period then a transaction is going to be needed because using keys will be problematic. The transaction is going to have to look at the records in the table and see if the start and end of the meeting overlaps any other records. If so, return message saying can't book and end the transaction, if not book the room and complete the transaction. While your looking, no one else can be booking, so they have to wait until your transaction finishes before they can start theirs. This causes blocking. To alleviate...

In this scenario, you might want to have each room be it's own table so that when your locking and there's a transaction for that room, other users booking other rooms don't have to wait until your booking is completed. This gives more scalability across the rooms and will only block if two users are trying to book a room at the same time (unlikely, but possible).