From Frustration to Fascination: The Truth About Double Booking Problem

Varun Garg — Mon, 27 May 2024 15:20:21 +0000

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:

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.
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!

Who/What is a backend engineer?

Varun Garg — Wed, 25 Oct 2023 18:53:19 +0000

Stranger: Hey Varun, what do you do for a living?

Me: Well, I work as a backend engineer!

Stranger: Sounds fascinating! What is a backend engineer?!

Me: Ah, you know those wizards behind the digital curtain making sure everything runs smoothly? That's me, weaving code spells to keep the website enchanting and the servers spellbound!

Stranger: What a weirdo! I do not understand!

Me: sigh. I work as a software engineer.

Stranger: Got it! Could have said this in the first place!

The above is just an age-old example of the problems I and many other backend engineers or software engineers face while interacting with people unfamiliar with the IT industry.

And honestly, can you blame them? If I meet a 'Hepatologist' and they try to explain what exactly they do with the liver, it will take me a million years to understand!

So I thought, why don't I answer and document this question (What is a backend engineer?) with my favorite 'Restaurant analogy' ?!

The Restaurant Analogy:

When a customer places an order after looking at the menu, the waiter takes it to the kitchen and gives it to the chefs. The chefs then prepare the food according to the customer's specifications. Once the food is ready, the chefs send it back to the waiter, who then delivers it to the customer.

In the above scenario:

The customer is the web browser.
The waiter is the front-end developer.
The chef is the backend engineer.
The kitchen is the database.
The menu is the API.
The order is the HTTP request.
The food is the HTTP response.
The lights, furniture, and utensils are the servers!

To elaborate further, the backend engineer, akin to a chef, is in charge of the kitchen (database), managing and preparing the ingredients (data) to fulfill orders (HTTP requests) from the customers (web browsers). They ensure a smooth operation of the kitchen equipment (servers), guaranteeing efficient delivery of the prepared dishes (HTTP responses) to the customers.

Sounds good?! Good! Enlightened with the above analogy, let us reimagine our initial conversation and add some grandeur to it!

Me: Well, I work as a backend engineer!

Stranger: Sounds fascinating! What is a backend engineer?!

Me: Imagine a restaurant. I'm like the chef who designs the kitchen, ensures the ingredients are always fresh and ready, and coordinates with the front of the house to ensure every dish comes out perfectly, even if you only see the waiter bringing it to your table!

Stranger: Wow! That sounds awesome!

Me:

One of the practices in my life is to use relatable analogies to describe key situations! Especially when it comes to explaining what I do for a living!

Relatable comparisons build bridges, making complex concepts understandable, and are a great segue to strike an insightful conversation!

What is your definition of a backend engineer? Let me know your thoughts!

DEV Community: Varun Garg

From Frustration to Fascination: The Truth About Double Booking Problem

Who/What is a backend engineer?