DB Locking in Reservation Systems

So last night I was booking tickets for the movie Kashmir files for me and my family through BookMyShow (okay! enough free promotion!). And just while I was selecting the seats, a question popped into my head (a very obvious one). What if multiple people end up selecting the same seats and paying for it. Well that would end up in a drama at the cinema before the movie itself!

Well, well.. That’s not why SDEs are paid.

So then I tried this with my brother. We both tried to book the same seats for the same show at the same time. While I was already at the payment page, though I had not made a payment, my brother saw that the particular seats were already booked!

After some time, he refreshed the page and saw that the seats were again available. This happened because the website didn’t see any payment activity from my side. It made the seats available for a more deserving candidate.

Now how is this sanity achieved?! Through database locking!

Let’s assume the flow as -

1. A user sees the scheme of the cinema hall with vacant and booked seats.
2. The user selects the seats and sends a request to the server.
3. Seats are reserved for a time bracket, during which the user must make the payment.
4. If the user doesn’t make the payment, seat status will be set to vacant again else it will be marked as booked.

From the above flow it can be deduced that it will be best if we perform locking at step 2 itself, i.e. when the user selects the seats and sends a request to proceed to the payment page.

Lock will be acquired for 5–10 minutes, during which either user will pay for the seats or cancel it.

After some time the lock will be released. If the user pays for the seats, UI will display seats as booked, else it will be made available for someone else to book.

Pessimistic Locking

Single User Booking for a Single Seat -

In terms of dev language, let’s assume, there’s a seat-booking table at the backend where each row corresponds to one seat at one auditorium. Along with that it holds columns like movie session, seat status (booked or vacant) and customer id (null, if vacant).

//A single transaction to book a tickets
begin;
seatsAvailable() -> Select * from booking where seat_number = ‘A1’ FOR UPDATE
bookTickets() -> update booking set customer_id = ‘101’ , seat_status = ‘booked’ where seat_number = ‘A1’;
commit;

Let’s say I select a seat and proceed to the payment page. At this point the session will acquire a lock, so that no other session is able to read the record or make changes to it. Such kind of locking is called ‘Exclusive Locking’. For any other user session trying to access the locked records from the table, it will have to wait for the lock to be released for the particular record in the table.

We use ‘FOR UPDATE’ along with SELECT query to acquire exclusive lock.

Another type of locking is ‘Shared Lock’ where a session can read the row while it is already being lock-acquired by some other session. However, it cannot make an update until the first session has released the lock.

We use ‘FOR SHARE’ along with SELECT query to acquire shared lock.

This kind of locking technique would not be preferable in our scenario. Imagine, multiple users will be reading a particular seat as ‘available’ and paying for it. But due to shared lock implementation, only the user session which acquired the lock first, will be getting the seat.

Multiple Users Booking Multiple Seats Concurrently -

Now let’s say I wanted to book seats for my entire family. I could see seats A1 to A4 as vacant so I selected them and proceeded to the payment page.

//Transaction where user-1 books seats A1 to A4
begin;
seatsAvailable() -> Select * from booking where seat_number IN  (‘A1’,‘A2’, A3’, ‘A4’) FOR UPDATE
bookTickets() -> update booking set customer_id = ‘101’ , seat_status = ‘booked’ where seat_number IN  (‘A1’,‘A2’, A3’, ‘A4’) ;
commit;

Meanwhile another user selected seats A3 to A5.

At this moment, one proposition is to reject the entire transaction since all the seat requirements can’t be fulfilled.

Or, confirm booking for seat A5 for the second user instead of waiting for the lock to be released from A3 and A4 seats.

We use ‘FOR UPDATE SKIP LOCKED’ along with SELECT query to skip locked rows.

//A single transaction where user-2 tries to book seats A3 to A5
begin;
seatsAvailable() -> Select * from booking where seat_number IN  (‘A3’, A4’, ‘A5’) FOR UPDATE SKIP LOCKED;
bookTickets() -> update booking set customer_id = ‘102’ , seat_status = ‘booked’ where seat_number IN  (‘A3’, A4’, ‘A5’)
commit;

This is not an ideal behaviour in a movie ticket booking system because incomplete results are not tolerable. However, under a scenario where the target is to get maximum possible confirmed seats, this logic will help to expedite and make the process more efficient. (Say a train reservation system?)

If you had noticed above, I used the term ‘pessimistic’ locking, which means ‘optimistic’ locking exists as well. Optimistic locking does not actually lock the data but performs versioning on it.

Let’s look into it in the next article. Stay tuned!