Techniques to Prevent Double Booking

Imagine: Two people try to book the same airplane seat at the same time. Who should get it? How does the system prevent both reservations from succeeding?

1. Pessimistic Locking

Principle: "Lock first, then process!"

When reading a record for a reservation, the transaction locks that record until the transaction is complete.

Advantages

• Simple and straightforward to implement
• Strong consistency (guaranteed consistency)
• No possibility of conflict because requests are handled one at a time

Disadvantages

• Bottleneck: Locks can reduce throughput when there are many parallel requests
• Risk of deadlock: Other transactions locking different rows can deadlock
• Users must wait if someone else is currently accessing the resource

Implementation Example

BEGIN;
-- Lock row with FOR UPDATE
SELECT id, status FROM seats WHERE id = 123 FOR UPDATE;

-- If status = 'available', proceed:
UPDATE seats SET status='reserved', reserved_by = 'alice' WHERE id = 123;

COMMIT; -- Lock is released after commit

Analogy: Like locking a bathroom door — others have to wait until you're done!

2. Optimistic Locking

Principle: "Read freely, update with a version check!"

Read the record along with its version. When updating, include a condition that the version is still the same. If it's different, the update fails and the application retries or gives feedback.

Advantages

• No explicit DB lock (database remains responsive)
• Higher throughput (no blocking, multiple reads)
• Good read performance (no locking)
• Ideal for low contention situations

Disadvantages

• Under high contention, many UPDATE statements will fail → requires retrying (wasting compute and creating a poor user experience)
• More complex retry logic
• Users may need to try several times

Implementation Example

Scenario: Alice and Bob try to book the same seat

Step 1: Alice reads the seat data

SELECT seat_id, status, version
FROM seats
WHERE seat_id = 1;

Result:

seat_id | status     | version
--------|------------|--------
1       | available  | 0

Step 2: Bob also reads the same data (can be simultaneous!)

SELECT seat_id, status, version
FROM seats
WHERE seat_id = 1;

Bob's result:

seat_id | status     | version
--------|------------|--------
1       | available  | 0

Step 3: Alice tries to update first

UPDATE seats
SET status = 'reserved',
    reserved_by = 'Alice',
    version = version + 1
WHERE seat_id = 1
  AND status = 'available'
  AND version = 0;  -- Success! (1 row affected)

Step 4: Bob tries to update (but too late!)

UPDATE seats
SET status = 'reserved',
    reserved_by = 'Bob',
    version = version + 1
WHERE seat_id = 1
  AND status = 'available'
  AND version = 0;  -- Failed! (0 rows affected)
                     -- Because version already changed to 1

Key Point

When updating data, you MUST compare the version. If the version is the same as when it was read, no one else has changed the data, and the user can proceed with the update.

Analogy: Like Google Docs — everyone can read, but if there's a conflict when saving, the system will let you know!

When to Use Which?

Bonus: Hybrid Approach

Combine both to get the best of each:

• Use optimistic for normal operations
• Fall back to pessimistic after N failed retries

def book_seat(seat_id, user):
    max_retries = 3

    # Try optimistic first
    for attempt in range(max_retries):
        if try_optimistic_update(seat_id, user):
            return "Success!"

    # Fallback to pessimistic if contention is too high
    return try_pessimistic_update(seat_id, user)

Result: Best of both worlds!