Optimistic vs Pessimistic Locking Throughput Lab (Interactive)
Race a worker pool through FOR UPDATE, OCC retries, and SKIP LOCKED tick by tick. Switch concurrency strategies on a live worker pool and measure completed jobs, retry storms, lock-queue waiting ticks, and wasted CPU as contention changes.
Pessimistic vs Optimistic vs SKIP LOCKED Throughput
Run N concurrent clients against hot rows under three strategies and watch the scheduler tick by tick.
SELECT … FOR UPDATE — one exclusive row lock. The holder works, the other 7 workers sit in the lock queue every tick. Safe under brutal contention, but the hot row is serialized.
Interview rule: rare conflicts → optimistic versions (zero lock overhead); heavy contention on one row → short pessimistic locks or an atomic UPDATE … WHERE stock > 0; independent queue rows → FOR UPDATE SKIP LOCKED. Never hold a lock across an external HTTP call.
How It Works Under the Hood
Pessimistic locking acquires a row lock with SELECT FOR UPDATE before doing work: one worker per row, the rest wait, and holding the lock across an external call turns database waits into application stalls. Optimistic locking reads a version, computes without locks, then compare-and-swaps at write time; with low contention nothing is wasted, but high contention triggers retry storms that burn CPU on doomed attempts. SKIP LOCKED lets queue workers claim disjoint batches of jobs concurrently — the backbone of every Postgres-based job queue.
Core Architectural Principles
- FOR UPDATE serializes workers per row: zero wasted computation, but throughput is capped by lock-queue waiting.
- Optimistic version checks cost nothing under low contention but waste compute in retry storms under high contention.
- SKIP LOCKED hands each worker distinct unlocked jobs, giving lock-free parallel queue draining.
Give the rule of thumb: high contention with short critical sections favors pessimistic locks; low contention with long computation favors optimistic versioning. Note that optimistic loops need backoff and that a pessimistic lock must never span an external RPC. Mention FOR UPDATE SKIP LOCKED whenever the design involves workers draining a database queue.
Pessimistic locking trades latency and deadlock risk for certainty; optimistic locking trades wasted retries for lock-free reads — contention decides the winner.