Optimistic vs pessimistic locking — which do you use for concurrent updates?
⚡ Short Answer
Optimistic: no lock; a version/timestamp column is checked on UPDATE and the txn retries if it changed (great for low-contention, high-read). Pessimistic: SELECT ... FOR UPDATE locks the row up front (best for high-contention hotspots), at the cost of blocking and deadlock risk.
☕Coffee Chat Question
Concept Made Simple
“Optimistic vs pessimistic locking — which do you use for concurrent updates?”
🧠Mind Map Answer
Remember It Faster
⌨️Hands-on Keyboard
Learn by Doing
-- optimistic
UPDATE account SET balance = ?, version = version + 1
WHERE id = ? AND version = ?; -- 0 rows updated → conflict, retry
-- pessimistic
SELECT balance FROM account WHERE id = ? FOR UPDATE;🔥What If?
Think Beyond the Expected
A single 'hot' inventory row gets thousands of concurrent decrements — optimistic or pessimistic?
On a hot row, optimistic locking causes a storm of version conflicts and retries (livelock-ish). Pessimistic FOR UPDATE (or an atomic UPDATE ... SET qty = qty - 1 WHERE qty > 0) serializes access cleanly — better for high contention.
😂Real World
JPA's @Version implements optimistic locking — the default for most entities; pessimistic FOR UPDATE is reserved for genuine hotspots like inventory/seat booking where conflicts are frequent.
🎯Interviewer's Expectation
Keywords they're listening for:
⚠️Common Mistakes
- ✗Optimistic locking on a high-contention hot row
- ✗Pessimistic locks held across long/external calls
- ✗No retry handling for optimistic conflicts
✅Best Practices
- ✓Optimistic for low-contention; pessimistic for hotspots
- ✓Keep pessimistic locks short
- ✓Prefer atomic conditional UPDATEs where possible
🔁Follow-up Questions
- 1How does JPA @Version detect conflicts?
- 2Why can pessimistic locking deadlock?
- 3When is a single atomic UPDATE better than either?
🧩Related Technologies
Continue Learning with AI
Take this question deeper with your favourite AI assistant. Pick a depth, copy the prompt, or open it directly — AI is your learning companion, not a shortcut.
Plain-language foundations
I'm preparing for a software engineering interview and want to understand this from scratch, as a beginner. Topic: Transactions (SQL) Interview question: "Optimistic vs pessimistic locking — which do you use for concurrent updates?" Please: 1. Explain the core idea in simple, plain language, using an everyday analogy. 2. Define any technical terms you use. 3. Walk through one small, concrete example. 4. Finish with a single sentence I can easily remember. Keep the tone friendly and assume I'm new to this topic.
Was this answer helpful?
⭐ Featured Products
Support our platform by exploring our recommended products.
As an Amazon affiliate, purchases through these links may earn us a small commission — at no extra cost to you. It helps keep Full Stack Interview Guru free.