The Indian Premier League (IPL) is one of the most popular and lucrative cricket leagues in the world. The IPL month is celebrated as a festival in India by the fans. It attracts millions of viewers and generates billions of dollars in revenue each year. The league features some of the biggest names in cricket and is known for its fast-paced and high-scoring matches. With millions of fans around the world eager to secure a ticket to an IPL match, the process of booking tickets can be a daunting task. In this blog post, we'll explore the technical challenges of managing database transactions for concurrent requests during the IPL ticket booking process.
The stage is set for IPLs own El clásico. Its the clash of the two biggest titans - Mumbai Indians and Chennai Superkings. With such a big match coming its obvious that the rush for buying tickets would be huge. Hundreds of thousands of people sit on their laptops and phones waiting for the tickets to go on sale. As soon as the ticket window opens the websites booking the tickets see a sudden surge in traffic. People start booking their favourite seats in the stadium to watch the match from the best possible angle. And its the responsibility of the booking website to provide a smooth experience to the customer buying the tickets.
Concurrency problem
In the stadium there are few seats which are more in demand as compared to others due to various factors. Some may be close to the pavilion or some may provide a much better view of the game. These are the seats which most people go for and it becomes a hot spot. Since a huge number of people book the seats concurrently it becomes important that no two customers are assigned the same seat if they book at the exact same time.
How does this problem occur ? Lets us understand this by taking a very basic database table which stores the data related to the allotment of seats.
stadium_id | stand_id | seat_id | is_filled | customer_id |
101 | 51 | A5 | 0 | NULL |
101 | 51 | A6 | 0 | NULL |
101 | 51 | A7 | 1 | 61259 |
Consider a scenario where 2 customers are looking to buy the seat A6. The website shows the seat as empty to both the customers and they move forward to buy it the exact same moment. As soon as they press the "Place Order" button and API is called to reseve the seats and place the order. The very fundamental thing that happens in the API call is:
Check if the seat is available
Reserve the seat for the customer
Call payment service to receive the payment
Let us say there are two separate processes which are handling the order of these two customers. Each of these processes executes the first step simultaneously and assumes that the seat is available. Due to this both the processes will complete the booking for the same seat and one process might overwrite the data of other process in that row.
Solving concurrent reads through database read locks
We can solve the above issue by stopping other processes from reading the row if one process has already read the row and is going to update it. According to MySQL documentation there are two methods of locking reads:
SELECT ... FOR SHARE - This statement locks the row in shared mode i.e. other processes can read the data but cannot update it until your transaction commits.
SELECT ... FOR UPDATE - This statement locks the row in such a way that other transactions are not allowed to read the data in ceratin isolation levels until your transaction commits.
In our case the second option suits the best as we dont want other process to see the seat as available if one process has proceeded for booking the seat. Let us see how we can use this concept and write queries to obtain our desired result.
START TRANSACTION;
SELECT * FROM seat_availability WHERE seat_id = A6 and stadium_id = 101 and stand_id = 51 FOR UPDATE;
UPDATE seat_availability SET is_filled = 1 WHERE seat_id = A6 and stadium_id = 101 and stand_id = 51;
COMMIT;
In the above code snippet we start a trnsaction and obtain a read lock on the seat we wish to book. This prevents other processes from accessing this row and they wait until the process holding the lock has either committed or rolled back. This prevents inconsistency errors.
Comments