Locking Strategies

Two users buy the last ticket simultaneously:

Without locking:
  User A: SELECT stock FROM tickets WHERE id=1;  → stock = 1
  User B: SELECT stock FROM tickets WHERE id=1;  → stock = 1
  User A: UPDATE tickets SET stock = 0 WHERE id=1;  ✅
  User B: UPDATE tickets SET stock = 0 WHERE id=1;  ✅ (but stock was already 0!)
  → Two tickets sold, only one existed. Lost update.

With locking:
  User A: SELECT stock FROM tickets WHERE id=1 FOR UPDATE;  → stock = 1 (row locked)
  User B: SELECT stock FROM tickets WHERE id=1 FOR UPDATE;  → BLOCKED (waiting for A)
  User A: UPDATE tickets SET stock = 0; COMMIT;  → lock released
  User B: SELECT stock → stock = 0 → "Sold out!" → no sale
  → Correct behavior. One ticket, one sale.

-- Table has a version column
-- products: id=42, name="Laptop", price=999, version=3

-- User A reads:
SELECT id, price, version FROM products WHERE id = 42;
-- → price=999, version=3

-- User B reads (same time):
SELECT id, price, version FROM products WHERE id = 42;
-- → price=999, version=3

-- User A updates (includes version check):
UPDATE products SET price = 899, version = 4
WHERE id = 42 AND version = 3;
-- → 1 row affected ✅ (version matched, update succeeded)

-- User B tries to update (same version check):
UPDATE products SET price = 799, version = 4
WHERE id = 42 AND version = 3;
-- → 0 rows affected ❌ (version is now 4, not 3!)
-- → User B detects conflict, retries with fresh data

-- User A locks the row:
BEGIN;
SELECT * FROM products WHERE id = 42 FOR UPDATE;
-- → Row is now LOCKED. No one else can modify it.

-- User B tries to read the same row:
SELECT * FROM products WHERE id = 42 FOR UPDATE;
-- → BLOCKED. Waiting for User A to release the lock.

-- User A updates and commits:
UPDATE products SET price = 899 WHERE id = 42;
COMMIT;
-- → Lock released.

-- User B's query now proceeds:
-- → Reads the updated price (899)
-- → Can now modify and commit

-- No conflict possible. But User B waited.

Acquire lock:
  SET lock:payment:order-123 "worker-A" NX EX 30
  → NX: only set if key doesn't exist (atomic)
  → EX 30: expires in 30 seconds (TTL safety net)
  → Returns OK if acquired, nil if already locked

Do work:
  Process payment for order-123...

Release lock:
  Check if we still own it (value = "worker-A")
  DEL lock:payment:order-123

Why TTL?
  If worker-A crashes without releasing the lock,
  the lock expires after 30 seconds → other workers can proceed.
  Without TTL: lock held forever → deadlock.

Problem: single Redis node = single point of failure.
  If Redis crashes, the lock is lost. Two workers proceed.

Redlock solution: acquire lock on MAJORITY of N Redis nodes.

5 independent Redis nodes: [R1, R2, R3, R4, R5]

Acquire:
  1. Try SET lock NX EX 30 on ALL 5 nodes
  2. If acquired on ≥ 3 nodes (majority) within time limit → LOCKED ✅
  3. If acquired on < 3 nodes → FAILED, release all acquired locks

  Worker A acquires on R1, R2, R3 → majority (3/5) → lock granted ✅
  Worker B tries → R1, R2, R3 already locked → fails → waits

Release:
  DEL lock on ALL 5 nodes (even ones where acquire failed)

Failure scenario:
  R3 crashes → Worker A still has lock on R1, R2 (2/5, not majority)
  → Lock is effectively lost. This is a known Redlock limitation.
  → For critical systems, use a consensus-based lock (Zookeeper, etcd).

Table: orders (10M rows)

Row-level lock (PostgreSQL default):
  Tx A: UPDATE orders SET status='shipped' WHERE id = 42;  → locks row 42 only
  Tx B: UPDATE orders SET status='shipped' WHERE id = 99;  → proceeds ✅ (different row)
  Tx C: SELECT * FROM orders WHERE id = 500;               → proceeds ✅ (read, no conflict)
  → High concurrency: 3 transactions run in parallel

Table-level lock:
  Tx A: LOCK TABLE orders; UPDATE ... WHERE id = 42;       → locks ENTIRE table
  Tx B: UPDATE orders WHERE id = 99;                       → BLOCKED ❌ (table locked)
  Tx C: SELECT * FROM orders WHERE id = 500;               → BLOCKED ❌ (table locked)
  → Low concurrency: only Tx A runs, B and C wait

For 10K concurrent transactions:
  Row-level: most proceed in parallel (different rows)
  Table-level: all wait in a queue (one at a time)

User selects seat A7:
  1. Seat map (read)     → No lock (cached, read-only)
  2. Reserve seat (write) → Pessimistic lock (SELECT FOR UPDATE on seat row)
  3. Process payment      → Distributed lock (Redis, keyed on order_id)
  4. Update counter       → Optimistic lock (version column)
  5. Confirm booking      → Release all locks

Concurrency:
  50K users selecting different seats → parallel (row-level locks)
  2 users selecting same seat → one waits (pessimistic lock)
  Payment processing → one at a time per order (distributed lock)
  Counter update → retry on conflict (optimistic lock)