Booking slot concurrency issue

Background
Product: Online appointment booking form.
Requirement: 1 client allowed to book 1 slot at the same time.

Problem statement
Assumes 3 clients selected the same appointment slot and submission at the same time. And backend capture 3 appointment slots at the same time which violates the business requirement.

Introduction
In reality, in order to solve the issue, the first thing I’ll do is try to:

1. Reproduce the issue on a sandbox, staging environment.
2. Evaluate and review the code and database design.
3. Evaluate every possible layer like API, network, host.
4. Narrow down the scope of the possible issue.

Let’s assume the things mentioned above is in the working state and bug-free. How do we take preventive measure to avoid replicated slot?
To make it simple, let’s review the issue at 3 levels: database, API and design.

Database
Regardless of SQL or NoSQL, most database, storage tech provides the lock mechanism to resolve concurrency issue. So in the end, it downs to database config that prevents concurrency issue. What if concurrency issue still persists if the config is right? Let’s move on to the API level.

API
We may take an extra step on API level by adding validation or safety net to ensure there’s no duplication after submission. If duplication found, may redirect the client back to re-select new available timeslot.

Potential issue
Let’s assume 10% of the client unable to pass the validation API and required to re-select timeslot. This is hurting the user experience and increase the dropout rate in the funnel, how to we further prevent it on design level? Let’s move on to the next section.

Design
On the product design level, we may add a realtime timeslot, if 1 of the client picks the slot, it will lock and release after X min, and other clients would see the slot is locked and unable to pick it anymore. This design acts as a filter net to prevent multiple clients to select the same timeslot. It’s more or less like AirAsia’s flight seat selection. (Learn from others and make it better 😅)

This screenshot captured from another site.

Potential issue 1
If 2 clients manage to pick and lock the same slot, then validation API is in place as a safety net to prevent redundant slot.

Potential issue 2
Performance bottleneck if 1000 clients view concurrently? Yes, that’s why the realtime timeslot needs to be filtered, by date, place, in order to divert the traffic.

Final thought
Each approach has new potential issues, tradeoff, business value, client value, team value to consider. So prioritise is the key, whether to prioritise client value or to prioritise business value at the current stage.

Finally, the discovery shouldn’t stop here, keep learning and improving (Kaizen 改善). Peace ✌️