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?

  • 2
    What about a simple "temporary reservation" object that has a temporary lease on the time. Once the reservation is cancelled or times out, the time is made available again. Otherwise that reservation is committed. – Berin Loritsch Jan 28 '19 at 18:12
  • I was thinking about the same thing, but the problem is with that, I definitely need some kind of UX to "reserve" on the time for some period of time (5min, 15min or something like that), but would that circle around the same problem? two user put the time reservation at the exact same time? I agree that in practice, this might not happen that often since each user might spend different time to finish the booking. But I am trying to understand hypothetically, if both user enter the flow at the same time and finish at the same time, then what happen, how to fail the other user correctly. – smurf Jan 28 '19 at 18:18
  • With temporary reservations, first one wins. Default time for meetings should be minimum of 30 minutes, although 60 minutes is probably more common. You will need some sort of UX. If on submit someone else has the temporary lease, you have to return the "Sorry it's reserved" message. With a multi-conference room set up, usually size and time are the most important thing. You can do a best match and have them both be winners, and send out an email confirmation with the real conference room number after you choose for them. – Berin Loritsch Jan 28 '19 at 18:24
  • Thank you for your explanation, for the "someone else" situation, when the user submit, then I have to read the booking system again to double check on the slot, then response accordingly correct? Will that "read" on database acceptable? Given every potential write would require a single read, or is it common? I am probably over thinking this stuff, but I am thinking about would this put stress on reading the system? – smurf Jan 28 '19 at 18:30
  • 1
    You are probably overthinking this. The load on the system can't be that high due to the physical constraints of the problem. You have a limited resource: time on x conference rooms. The number of opportunities to reserve the room will be limited. The likelihood that 2 people are attempting to reserve the same conference room at the same time is low, but not zero. It is proportional to the ratio of users to conference room time slots though. More people with fewer time slots means a higher likelihood of conflict for the resources that are left. – Berin Loritsch Jan 28 '19 at 18:36

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).

  • of course not, if I were to do it, then it would just for fun exercise, but if production definitely some established queue. But if we were to drill down on the queue design, it could cause some problem on scaling out since we need to partition the queue so that the dequeue worker can only dequeue all the request for the same room to warranty the ordering and "only one" execution – smurf Jan 29 '19 at 22:37
  • I consider one of the great advantages of queues it that they defer the scaling concerns. Unless you're expecting thousands of booking requests per second then scaling out it not needed - just let the queue take the strain and buffer the requests. The queue guarantees both ordering and once-and-only-once delivery. – Rikalous Jan 30 '19 at 8:54
  • It is indeed elegant solution and I was considering it too. On top of that, it would imply some behavior that decide the work flow. Considering queue can have backlog, I am not talking about thousands of booking on one resource, but thousands or booking across resource that fall in the same queue partition, then every request booking should be confirmed separately after submission. If the requirement is that confirmation need to show right away, we need a synchronous way to determine that, in that sense, we can't wait on the queue to process the expected events. But it definitely work in some – smurf Jan 31 '19 at 18:44

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).

  • I think increments might not fit for this time of problem due it is more likely booking would span over multiple increments then insert would need to insert multiple records at the same time, then on overlapped, you will need to roll back if one of the slot failed due to PK. so operation-wise could be a problem. – smurf Jan 29 '19 at 22:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.