Tatkal / BookMyShow High-Concurrency Seat Hold Race

01 /What You Learn

During flash-sale events like IRCTC Tatkal booking at 10:00:00 AM or BookMyShow concert ticket drops, thousands of users compete for a limited inventory of physical seats. Under high concurrency, naive code patterns produce catastrophic double-allocation bugs where multiple customers are sold the exact same ticket.

In this hands-on lab, you will:

• Reproduce the classic "check-then-act" race in PostgreSQL's default Read Committed isolation: 25 workers race for Seat #1 and many of them are told SUCCESS, with no database error.
• Fix it with a single guarded UPDATE ... WHERE status = 'AVAILABLE' on Seat #2 and see that Read Committed re-checks the WHERE clause, so exactly one worker updates 1 row and the rest update 0.
• Fix it with pessimistic locking (SELECT ... FOR UPDATE) on Seat #3, and with an optimistic version check on Seat #4, and compare where each one makes you pay: waiting or wasted work.
• Hold a row lock across a 3-second payment call and watch unrelated login requests queue for about 3 seconds behind a 5-slot connection pool. Then move the call out of the transaction and watch the wait drop.
• Send the same 25 workers to 25 different berths and then to one quota row, and see why high traffic and high contention are not the same thing.

02 /Why It Matters: The Flash-Sale Overselling Trap

Imagine 25 passengers clicking "Book Berth 12" on the 12951 Mumbai Rajdhani at the exact same millisecond. In a naive implementation, each backend thread performs a two-step query:

-- Step 1: Check availability
SELECT status FROM tatkal_seats WHERE seat_id = 1;

-- Step 2: If 'AVAILABLE', execute hold
UPDATE tatkal_seats SET status = 'HELD', held_by = 'user_X' WHERE seat_id = 1;

Under PostgreSQL's default Read Committed isolation, all 25 threads read the database before any thread commits its update. Every thread sees status = 'AVAILABLE' and proceeds to confirm the booking. The application confirms 25 tickets for one physical train berth.

At the train station, 25 passengers board with valid tickets for Berth 12. The result: legal liability, operational chaos, and severe brand damage. The core architectural invariant must hold under all concurrency conditions:

03 /Architecture & Topology

The lab launches a PostgreSQL 16 container on port 15440 and executes a Python harness with 25 worker threads synchronized by a threading.Barrier(25) to fire at the exact same microsecond:

How SELECT ... FOR UPDATE Works

1. Worker 1 begins a transaction and executes SELECT ... FOR UPDATE. It acquires an exclusive row lock on Seat #3.
2. Workers 2 through 25 issue the same query. Because Worker 1 holds the row lock, Workers 2-25 are placed into PostgreSQL's internal lock wait queue.
3. Worker 1 updates status = 'HELD' and commits. Its lock is released.
4. Worker 2 acquires the lock. PostgreSQL re-evaluates the query's WHERE status = 'AVAILABLE' predicate against the newly committed row version. Because status is now HELD, the row is excluded from the result. Worker 2 receives zero rows, takes the ELSE branch, logs SEAT_TAKEN, and rolls back.
5. Workers 3 through 25 follow the exact same evaluation and reject cleanly. Exactly 1 winner emerges.

04 /Core Vocabulary

05 /Predict Before You Run

Test your understanding of transaction isolation and lock semantics against these 3 scenarios:

06 /Step-by-Step Runbook

# The zip has no top-level folder: unzip it into a new folder
mkdir tatkal-seat-concurrency && cd tatkal-seat-concurrency
unzip ~/Downloads/tatkal-seat-concurrency.zip
docker compose up -d

Select your operating system to execute the automated concurrency race benchmark:

# Make test script executable and run the benchmark
chmod +x race-test.sh
./race-test.sh

# Run the PowerShell test harness
.\race-test.ps1

07 /Expected Terminal Output

Here is a real run of all six phases, recorded on Windows with Docker Desktop (PostgreSQL 16). The winner and conflict counts in Phases 2 to 4 should match yours exactly. The Phase 1 count and every timing will differ, and on native Linux Docker the timings are much lower. See the table under the output.

08 /Inspect the State

Run these real SQL inspection queries to audit database state after the benchmark:

1. Inspect Seat Status in Inventory

docker exec -i lab-tatkal-db psql -U postgres -d tatkal_reservation -c "
SELECT seat_id, coach, berth_number, status, held_by, hold_expires_at 
FROM tatkal_seats ORDER BY seat_id;"

Seat #1 shows one held_by, the last writer. The row looks fine. The damage is in the audit log, where many users were told SUCCESS. Seats #2, #3 and #4 each show their single winner, and Seat #4 has version = 2.

2. Verify Invariant Integrity with HAVING COUNT(*) > 1

docker exec -i lab-tatkal-db psql -U postgres -d tatkal_reservation -c "
SELECT seat_id, COUNT(*) AS successful_holds
FROM booking_audit_log
WHERE result = 'SUCCESS'
GROUP BY seat_id
HAVING COUNT(*) > 1;"

Only Seat #1 comes back. Seats #2 to #6 (guarded update, FOR UPDATE, version check, and both payment variants) never have more than one SUCCESS.

3. Inspect Active Database Locks

docker exec -i lab-tatkal-db psql -U postgres -d tatkal_reservation -c "
SELECT locktype, relation::regclass, mode, granted 
FROM pg_locks 
WHERE relation = 'tatkal_seats'::regclass;"

09 /Change One Variable Drill

Explore how switching locking primitives completely transforms system throughput and queuing behavior:

Drill A: Multi-Berth Allocation with FOR UPDATE SKIP LOCKED

When users don't care which exact seat they get (e.g., "any available berth in coach B1"), queuing 25 workers on a single row is wasteful. Instead, use FOR UPDATE SKIP LOCKED:

BEGIN;
SELECT seat_id 
FROM tatkal_seats 
WHERE status = 'AVAILABLE' 
ORDER BY seat_id 
FOR UPDATE SKIP LOCKED 
LIMIT 1;

-- If FOUND, assign seat to user and commit
UPDATE tatkal_seats SET status = 'HELD', held_by = 'user_X' WHERE seat_id = ...;
COMMIT;

Workers skip locked rows instead of waiting. Point it at coach B2 (25 berths) with AND coach = 'B2' and each worker claims a different berth in parallel. When the coach runs out, the query returns no row and the user sees "Coach sold out" without queuing behind anyone.

Drill B: Fail-Fast Interactive Sales with FOR UPDATE NOWAIT

If you prefer failing immediately over making customers wait in a lock queue:

SELECT seat_id FROM tatkal_seats WHERE seat_id = 3 FOR UPDATE NOWAIT;

Worker 1 acquires the lock. Workers 2 through 25 fail instantly with error 55P03: could not obtain lock on row. The web tier catches this error and immediately renders: "Another passenger is currently booking this seat. Please choose another."

10 /Production Caveats

CREATE TABLE seat_holds (
  hold_id    BIGSERIAL PRIMARY KEY,
  seat_id    INT NOT NULL REFERENCES tatkal_seats(seat_id),
  user_id    VARCHAR(50) NOT NULL,
  status     VARCHAR(20) NOT NULL,  -- 'ACTIVE', 'CONFIRMED', 'EXPIRED'
  expires_at TIMESTAMPTZ NOT NULL
);

CREATE UNIQUE INDEX uq_one_live_hold_per_seat
ON seat_holds (seat_id)
WHERE status IN ('ACTIVE', 'CONFIRMED');

11 /Interview Talk Track

When an interviewer asks: "How do you design a high-concurrency ticket reservation engine for Tatkal or BookMyShow that guarantees zero overselling under 50,000 concurrent requests?", frame your answer across these architectural layers:

Follow-up questions the interviewer will ask

• "How do you handle hold expiration when a user abandons the payment gateway?"
  Store hold tokens with a 5-minute TTL in Redis or run a periodic background worker with: UPDATE tatkal_seats SET status = 'AVAILABLE', held_by = NULL WHERE status = 'HELD' AND hold_expires_at < NOW();. Redis key expiration events can trigger automatic seat inventory release.
• "Why not use distributed Redis locks (Redlock) instead of PostgreSQL row locks?"
  The relational database is already the single source of truth where ACID commits occur. Adding Redlock introduces additional network round-trips, clock drift vulnerabilities, and split-brain risks. Native database row locks eliminate distributed coordination overhead for single-shard workloads.
• "How does the system prevent 100,000 requests from hitting PostgreSQL at 10:00 AM?"
  Edge Admission Control: Place an edge virtual waiting room (e.g. Cloudflare Waiting Room or Redis Token Dispenser) in front of the application. Only permit traffic equal to database connection capacity (e.g. 500 req/sec) to pass through to the database tier; queue remaining users at the edge.

12 /Clean Up

Tear down the database container and remove persistent test volumes:

docker compose down -v