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:
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.
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?
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?