High Concurrency

Booking System Design

Handling the "1 Ticket, 1000 Buyers" problem using Optimistic Locking, Database Constraints, and Distributed Transactions.

Locking Strategies DB Schema

State Management & Inventory

The core problem is race conditions. If User A and B click "Book" at the same millisecond, only one should succeed.

User A
User B
⬇ ⬇
RACE CONDITION!
Database
Inventory: 1

Concurrency Control Strategies

How do we prevent double-booking?

Strategy Mechanism Use Case Performance
Pessimistic Locking `SELECT ... FOR UPDATE` High conflict (flash sales) Low (Blocks rows)
Optimistic Locking `version` column check Low conflict (hotel rooms) High (No DB locks)
Database Constraint `UNIQUE(seat_id, event_id)` Simple uniqueness Very High

Hardware-Level Enforcement

1. Optimistic Locking (Recommended)

We add a version column to the Inventory table.

-- 1. Read the current version (e.g., v1)
SELECT quantity, version FROM inventory WHERE id = 100;

-- 2. Decrement conditionally
UPDATE inventory 
SET quantity = quantity - 1, version = version + 1
WHERE id = 100 AND version = 1; -- <--- Fails if version changed!
2. Pessimistic Locking

Locks the row until transaction commits. Dangerous for performance.

BEGIN;
SELECT * FROM inventory WHERE id = 100 FOR UPDATE;
-- Do calculations
UPDATE inventory SET quantity = quantity - 1 WHERE id = 100;
COMMIT;

Database Schema Design

📅 Inventory Table
  • id
  • event_id
  • total_seats
  • available_seats
  • version (INT)
🎟️ Booking Table
  • booking_id (UUID)
  • user_id
  • status (PENDING, CONFIRMED, EXPIRED)
  • created_at

Distributed Locking (Redis)

For temporary "holds" (e.g., holding a seat for 10 mins while user pays), use Redis.

  • Key: `lock:seat:123`
  • TTL: 600 seconds (10 mins)
  • Pattern: Redlock algorithm ensures safety across Redis implementations.

Summary

  • Optimistic Locking: Best for most general booking systems (Hotels, Flights).
  • Pessimistic Locking: Use only when you absolutely cannot afford a retry loop (rare).
  • Constraints: `UNIQUE` indexes are your final safety net against race conditions.