What's the difference between optimistic and pessimistic locking?
Take a moment.
Say your answer out loud or write it down first — retrieval is what makes it stick. Reveal when you're ready.
Short answer
Pessimistic locking takes a lock before touching data (SELECT … FOR UPDATE) — safe under contention but blocks others. Optimistic locking lets everyone proceed and checks at commit time (usually a version column) — lock-free and fast when conflicts are rare, but losers must retry.
Detailed explanation
Both solve the lost-update problem: two sessions read stock=1, both sell, both write stock=0 — one sale silently lost. Pessimistic locking assumes conflict: SELECT … FOR UPDATE locks the rows up front, so the second session waits until the first commits or rolls back. Simple to reason about and correct under heavy contention, but lock waits serialize work, idle transactions hold locks hostage, and lock ordering mistakes cause deadlocks. Optimistic locking assumes conflicts are rare: each row carries a version (or updated_at); the writer reads version 5, and commits with UPDATE … WHERE id = ? AND version = 5, incrementing the version. If another writer committed first, the predicate matches zero rows — the app detects this and retries (re-read, re-apply, re-commit). No blocking, great throughput for low-contention data like user profiles; painful under hot contention where most attempts retry (a seat-map for a popular concert wants pessimistic locks instead). Choice rule: conflict probability low → optimistic with a retry loop; a known hot row or a multi-step business invariant → pessimistic with short transactions and consistent lock ordering.
Example
-- Pessimistic: lock the row first, others wait
BEGIN;
SELECT stock FROM inventory WHERE product_id = 42 FOR UPDATE;
-- ... decide, then:
UPDATE inventory SET stock = stock - 1 WHERE product_id = 42;
COMMIT; -- keep it short: locks are held until here
-- Optimistic: check the version at write time
-- UPDATE inventory SET stock = stock - 1, version = version + 1
-- WHERE product_id = 42 AND version = 5;
-- matched 0 rows? Someone else won — re-read and retry.Interview tip
Lead with the lost-update story both solve, then state the choice rule (rare conflicts → optimistic + retry; hot rows → pessimistic + short transactions). Naming SELECT … FOR UPDATE shows hands-on knowledge.
Common mistakes
- Using optimistic locking without a retry loop, silently dropping updates.
- Holding pessimistic locks across network calls or user think-time.