Case Study - How to handle concurrent reservation

Greetings!

There are multiple domains with reservation build into it, however the problem they face is same. How do you handle when concurrent users try to reserve the same resource? We can see this situation in systems like;

• Doctor appointment
• Airline ticketing
• Movie ticketing
• Hotel booking
• Train ticket
• many more...

Depending on how you define the business this again has two paths.

• Reserve the same resource - exact time slot for a doctor appointment.
• Reserve with count - 10 patients for a doctor for given timeslot with an appointment number.

Let's understand the problem again

You are going to watch the latest hit movie. While you are on your way, someone faster than you passed you. Then both of you joined the queue. Unfortunately for you, the person before you got the last available ticket hence you missed the chance to watch the movie at that time.
Now imagine there is a fraud happen. Somehow, someone has the same ticket as you have. Surely a fight will happen.
This is the race condition we want to solve.

As in a real queue, an online system can be flooded with thousands of users. This is a problem of many users but less available resources. Hence we need to provide proper mechanism to handle concurrent allocations as one seat/slot should only be available for one user.

In reality, users will take considerable time to find their favourite movie, read description, watch trailers, find a suitable seat positon, fill the payment information, etc. Hence, our solution should support that too.

Solution 1 (not good)

Use SQL 'SELECT FOR UPDATE'. When you execute 'select for update', database row will be locked until the current transaction completed. Eventhough this might helps for same raw updates for small projects, this will not help in bigger projects as we want provide better user experience. Why? as mentioned above, those work flows take more time for filling forms, complete the payment process etc.

Solution 2

Reserve the ticket/slot temporarily with a shorter expirary period. When the user selects his/her seat, it will not available for other users. For example, we can give 15 minutes window to fill the form and complete the payment. If he takes longer than 15 minutes, seat will be released and put back to available pool so that another user can pick it. This is considered the most used technique in many systems.

How can you manage temporary allocation? You might use separate lock table. Even better solution will be to use another table for reservation. We will have separate tables for temporary reservation (price quotation) and final reservatation. This will reduce the load the reservation table as well. However, depending on the requirement we can use the same table for this with a status/flag.

But, what if concurrent users try to allocate same seat in this solution? We can use unique id in our table. It might be seat+time for movie ticketing, a slot for doctor appointment etc. When concurrent users try to reserve the seat, our database will throw unique key violation for the second user.

reservations - unique_key (slot, seat_number)

movie_slot 1-----* reservations (id, slot, seat_number)

What will happen when 15 minutes exceeded and the seat put back to available pool? To handle that we can use a count down timer in UI so that user knows his time is over. However, still users can ignore this. We might need above same logic to handle actual reservation as well (force UI refresh can easily handle this).

Tentative reservation point can vary as per the business requirement. You might temporary reserve the movie ticket as soon as the user selects the seat, or you can wait till the user fill the payment form. However, initial reservation will increase user experience.

reserve(slot, seat) {
  if (seat available) {
    try {
      reserveSeatInTable(slot, seat);
      return 'seat is reserved';
    } catch(UniqueKeyViolation exception) {
      return 'seat is not available';
    }
  }
  return 'seat is not available';
}

Can we use the same technique to handle 'seat/ticket pool'? Instead of exact seat number, we will have 10 avaiable tickets as first come first serve basis. This is trickier than above as we do not have a proper resource for unique violation. Not only that, we need to provide an incremental number as well.

slot/session 1-----10 tickets

slot+ticket_number can be unique.

appointments - uniquey_key (slot, ticket_number)

slot 1----* appointments (id, slot, ticket_number)

reserve(slot) {
  if (not already filled) {
    int retries = 3;
    while (retries > 0) {
      int currentNumber = select current max appointment number;
      int newNumber = currentNumber++;
      try {
        saveReservation(slot, newNumber);
        return newNumber;
      } catch(UniqueViolation exception) {
         retries--;
      }
    }
  }
  return 'sorry, no reservations available';
}

These kind of systems will not have many concurrent users. However, it is still possible. Hence, we have implemented a retry mechanism.

One drawback of these solutions anyway is, we have to rely on database transaction. On the other hand, it is single point of failure.

Conclusion

Handling concurrent use requests is challenging and fun. As per the requirement we can utilize the underline database's transactional properties to solve multiple users having same allocation. Unique key violation is not a difficult thing to manage but our business logic is relying on the database.

Happy coding :)