From Frustration to Fascination: The Truth About Double Booking Problem

In 2019, I remember eagerly awaiting the release of "Avengers: Endgame." Like millions of fans around the world, I wanted to experience the epic conclusion to the Marvel saga on the opening night.
I remember sitting at my computer, the clock ticking down to the exact moment tickets went on sale. As soon as they did, I jumped into action, quickly selecting two perfect seats – center row, not too close, not too far. Seats H10, H11! My heart was racing as I clicked "confirm," imagining the triumphant moment when I'd have the tickets in hand.

But instead of a confirmation screen, I was greeted with an error message: "Those seats are no longer available." My excitement turned to frustration. How could this happen so quickly? 5 years later, after a deep dive into databases, I finally found my 'technical' answer!

---

Well, as frustrating as it might be, the booking websites employ clever technical tricks to prevent double bookings and ensure a smooth user experience. Let me try to explain in with the help of an analogy!

Analogy Time - Imagine booking a movie ticket as a high-stakes race against the other eager viewers. Whoever 'runs' through the process and clicks "confirm" first secures the perfect seat.
The problem is that with lightning-fast internet and multiple users vying for the same spot, things can get down to the wire. This is where booking websites step in as the race officials, having a powerful tool in the toolkit called database locks to ensure a fair and smooth experience.

How do we they lock down our seats??!!

Think of your ideal cinema seat – the one with ample legroom and an unobstructed view (H10,H11) in Audi 5.
When you click to select seats H10 and H11, here's what unfolds in the digital realm:

The MVP (SELECT FOR UPDATE): When you proceed to checkout, the website initiates a transaction with a special database query which goes like-

SELECT * FROM audi5 WHERE seat_id IN ('H10', 'H11') FOR UPDATE; 

This query acts like magic, targeting specific rows (seats H10 and H11) within the audi5 table stored in the database. Notice the SELECT FOR UPDATE clause at the beginning. This is what instructs the database to lock the rows retrieved by the query, but it's important to understand that this locking attempt might not always be successful.

Verifying the Lock and Retrieving Information: The SELECT FOR UPDATE not only retrieves information about the seats' availability (H10 and H11) but also attempts to lock those specific rows in the audi5 table. This lock prevents other users from seeing H10 and H11 as available while you finalize your purchase. Imagine it like putting up a temporary "reserved" sign on the digital records of seats H10 and H11.

Success or Sorry? (Depending on Lock Acquisition): Crucially, the information retrieved by the SELECT FOR UPDATE also indicates whether the lock acquisition was successful. This is because another user might have grabbed those seats (and successfully acquired the lock) before your query finished executing in the database!

Now let us say that you made it to the checkout page! What happens here? Remember I mentioned that the database initiates a transaction?

Well, Transactions guarantee that everything goes smoothly.They act like mini-programs that ensure data consistency within the database. When you confirm your booking, the website initiates a transaction in a 2 step manner:

1. It leverages the information retrieved by the SELECT FOR UPDATE query. This information includes both the availability of the seats (H10 and H11) and whether the lock acquisition was successful.

2. If the lock acquisition was successful and the seats are still free, the transaction updates the database to mark H10 and H11 as booked, associating them with your ticket purchase.

However, if something goes wrong during checkout (like the lock expiring, or the lock acquisition failing), the entire transaction is rolled back. This releases any lock attempts on H10 and H11, and the database remains unchanged, ready for someone else to try booking those seats.

For the geeks reading this, the final query will look something like this in the database (Although a lot of this is done through the code in the server)

START TRANSACTION;  -- Begin the transaction

SELECT * FROM audi5 WHERE seat_id IN ('H10', 'H11') FOR UPDATE;  -- Lock seats H10 and H11 (may fail due to timeout or other transactions)

-- Check if lock acquisition was successful and seats are available (addresses query 1)
IF @@ROWCOUNT = 2 AND available IN (1, 1) THEN  -- Check both rows returned and availability is true (1)

  UPDATE audi5
  SET available = 0,  -- Mark seats as booked
      booked_by = USER_ID()  -- Assign booked_by to current user
  WHERE seat_id IN ('H10', 'H11');

  COMMIT;  -- Commit the transaction if successful
ELSE
  ROLLBACK;  -- Rollback if lock acquisition fails or seats unavailable (addresses query 2)
END IF;

-- Additional logic can follow here, like processing payment or finalizing ticket confirmation`

Ever wonder why some websites give you a time limit to complete your ticket purchase? This isn't just about creating a sense of urgency (although it can do that too). It's also a technical safeguard (by rolling back the above-mentioned transactions) to prevent seats from being held indefinitely by someone who might not complete their purchase. This time limit ensures that seats become available again quickly for others who are eager to snag them!

So, the next time you encounter the dreaded "seats no longer available" message, remember, it's not bad luck or slow internet. It's a complex dance between you, other eager moviegoers, and the booking website's sophisticated system, all working behind the scenes to ensure there are no double bookings and it is a smooth and frustration-free experience.
Have you ever been in a similar situation? Let me know your thoughts!

Bonus:
You just might be also wondering how different ticketing platforms (Paytm, BookMyShow etc) show the same available seats? It's all thanks to channel managers! These act like middlemen, constantly checking seat availability across theaters and using special protocols to ensure all systems are in sync. This eliminates conflicts and guarantees the info you see is up-to-date. More on them later!