💺 The Real Reason Scalable Booking Systems Don’t Update Seat Tables Directly

Deepak kumar — Sun, 26 Oct 2025 19:26:00 +0000

When designing systems like BookMyShow or airline ticketing, it’s tempting to say:
*👉 “Let’s just mark the seat as booked in the seat table.”
*
But at scale, this design creates lock contention and data inconsistency that’s hard to recover from ⚠️

*🚨 Problem: When Seat Table Becomes the Source of Truth
*
1️⃣ Locking & Scaling Issues Every user booking the same show updates the same few seat rows. Thousands of concurrent writes → row-level locks → DB slowdown. Horizontal scaling doesn’t help since all writes hit the same records. Even reads (seat map views) get blocked by locks — the system becomes sluggish.

2️⃣ Inconsistency in Payment Flow
When seat status and booking/payment updates are tightly coupled, several failure scenarios appear 👇

Case 1: Payment Fails, Seat Marked Booked
User clicks “Book” → seat immediately marked as booked in the seat table
Payment fails ❌ → seat remains blocked temporarily or until a manual refresh / cleanup runs
System shows fewer available seats even though no booking exists

Case 2: Payment Succeeds, Seat Not Updated
Payment succeeds 💰 → booking entity created successfully
But due to network or DB error, seat status never updated in seat table
Other users still see the seat as available → possible double booking

Case 3: Race Condition
Two users read the same seat as available simultaneously
Both try to book → without atomic locking, both transactions may partially succeed
System ends up with inconsistent seat states

👉 Result: Ghost bookings, double bookings, and blocked seats — classic data inconsistency

*⚙️ The Scalable Solution — Two-Layer Architecture
*
1️⃣ Source of Truth (Booking Table → CP System)
Stores all confirmed bookings and payments.
ACID transactions ensure each booking is uniquely tied to one seat.
Guarantees correctness and durability.

However, if we rely only on this table for seat availability queries, every user request must scan booking records — adding latency and increasing DB load during high traffic.

2️⃣ Fast Read Layer (Seat Availability Cache → AP System)
A separate, high-speed cache (Redis / in-memory store) built for quick lookups.
Stores the current state of each seat (available/booked/locked).
Used by the seat map or search APIs to serve instant responses, even during heavy load.
Updated asynchronously from booking events.

This dual-layer design ensures the write path stays consistent (CP) while the read path stays blazing fast (AP).

⚡ Event-Driven Sync (Eventual Consistency)
After successful booking, an event SeatBooked → published to Kafka / Queue.
Cache listener updates seat status → other users see instant change.
A few milliseconds of delay is okay — eventual consistency guarantees converge.

⏳ Temporary Seat Locking
When user starts payment → lock seat in Redis for 2–3 minutes.
Lock auto-expires if payment fails.
Prevents double booking under massive concurrency.

✅ Final Result
Booking table → Strong consistency (CP)
Cache → High availability (AP)

*Combined → Scalable, fault-tolerant design ready for millions of user🚀
*
🎥 I’ve explained this concept in detail with architecture diagrams in my latest video — check it out here 👇
🔗 https://lnkd.in/ggkswiQA

DEV Community: Deepak kumar

💺 The Real Reason Scalable Booking Systems Don’t Update Seat Tables Directly