Optimistic vs Pessimistic Locking
Pessimistic locks the row before reading; optimistic checks for conflict at write time and retries on collision.
What it is
Pessimistic locking acquires a lock (SELECT ... FOR UPDATE) before doing work, preventing anyone else from modifying the row until the transaction commits or rolls back. Optimistic concurrency control (OCC) reads without locking, then at commit time checks whether the row changed since the read (via a version column or MVCC (Multi-Version Concurrency Control) snapshot) and aborts if so, requiring the app to retry. MVCC enables OCC because each transaction has its own snapshot — no locking needed for reads.
Why senior interviewers ask
Senior interviewers want to know if you can match the locking strategy to the contention profile. Wrong choice causes deadlocks (too pessimistic) or thrash (too optimistic under contention).
Key points
- Pessimistic: SELECT ... FOR UPDATE in SQL (Structured Query Language); explicit locks in app code.
- Optimistic: version column or last-modified timestamp; UPDATE ... WHERE version = X.
- MVCC (Postgres, MySQL InnoDB, Oracle) means readers never block writers and vice versa.
- Pessimistic shines when contention is high and retries are expensive (long transactions).
- Optimistic shines when contention is low — reads dominate writes, conflicts are rare.
- Deadlocks are a pessimistic locking failure mode; the DB picks a victim and aborts.
- Apps using OCC must implement retry loops; expect SQLSTATE 40001 in Postgres serializable.
Pros
- Optimistic: no locks held during user think time — great for web request scope.
- Optimistic: scales better under low contention (reads dominate).
- Pessimistic: predictable behavior; no retry loops in app code.
- Pessimistic: better fit for hot rows where conflict is the norm, not exception.
Cons
- Pessimistic: risk of deadlocks; holding locks across user think time is dangerous.
- Pessimistic: limits concurrency — only one writer at a time per row.
- Optimistic: under high contention, retries thrash and effective throughput collapses.
- Optimistic: app must handle conflict errors gracefully; users may see 'please retry'.
When to choose
- Optimistic: web app where users edit a record over seconds-to-minutes — lock would block others.
- Optimistic: low-contention data where conflicts are <1% of writes.
- Pessimistic: short, hot transactions (inventory decrement, ticket reservation) where you can't afford a retry.
- Pessimistic: when retry logic in the app is impractical.
When to avoid
- Optimistic: high-contention hot rows — retries will thrash the system.
- Pessimistic: long transactions or user think time — deadlocks and lock waits explode.
Real systems
Interview probe
'Two users are editing the same document. How do you handle the second save?' Answer: optimistic with a version column — second save fails with 'someone else updated this, here's a diff'. Don't pessimistically lock during user edit time.