Optimistic vs Pessimistic Locking: What is the Difference?
Compare optimistic and pessimistic locking in databases, how each handles concurrency, version checks, and when to use them.
Expected Interview Answer
Pessimistic locking locks a row before reading it so no one else can change it, while optimistic locking allows concurrent reads and only checks for conflicts, typically with a version number, at write time.
Pessimistic locking assumes conflicts are likely, so it acquires a lock upfront and makes other transactions wait, which is safe but can hurt throughput under high contention. Optimistic locking assumes conflicts are rare, so it lets multiple transactions read and modify data freely, then verifies at commit time that the data was not changed by someone else in the meantime, usually by comparing a version or timestamp column. If the check fails, the transaction is rejected and must retry.
- Pessimistic locking guarantees no lost updates under contention
- Optimistic locking gives higher throughput when conflicts are rare
- Version checks make optimistic conflicts explicit and retryable
- Choosing the right strategy matches workload contention patterns
AI Mentor Explanation
Pessimistic locking is like a batter reserving the crease before anyone else can even approach it — no one else can use that space until the batter is done, guaranteed safe but slow if many players are waiting. Optimistic locking is like letting players warm up on the pitch freely and only checking, right before the umpire signals play, whether anyone else already claimed that exact spot; if there is a clash, one player has to step back and retry.
Step-by-Step Explanation
Step 1
Pessimistic: acquire lock first
A transaction locks the row before reading, blocking others until it commits or rolls back.
Step 2
Pessimistic: others wait
Any transaction wanting the same row must wait in queue until the lock is released.
Step 3
Optimistic: read freely, track version
Transactions read a row along with its version or timestamp column without locking.
Step 4
Optimistic: verify at write time
On update, the transaction checks the version still matches; a mismatch means retry.
What Interviewer Expects
- Clear contrast between locking upfront vs verifying at commit
- Understanding of version/timestamp columns for optimistic checks
- Awareness of trade-offs: throughput vs guaranteed safety
- Ability to say when to use each approach based on contention
Common Mistakes
- Saying optimistic locking never locks anything at all, including at commit
- Not mentioning the version or timestamp column mechanism
- Failing to discuss the throughput vs contention trade-off
- Confusing this with isolation levels like read committed or serializable
Best Answer (HR Friendly)
“Pessimistic locking locks a record before you work on it, so nobody else can touch it until you are done, which is safe but can slow things down when many people want the same record. Optimistic locking lets everyone read and edit freely, and only checks for conflicts right when you try to save, using a version number, so it is faster but requires retry logic if a conflict is found.”
Code Example
BEGIN;
SELECT * FROM inventory WHERE id = 42 FOR UPDATE;
-- row is locked; other transactions wait here
UPDATE inventory SET quantity = quantity - 1 WHERE id = 42;
COMMIT;-- Read the row along with its version
SELECT id, quantity, version FROM inventory WHERE id = 42;
-- Application holds quantity = 10, version = 5
UPDATE inventory
SET quantity = 9, version = 6
WHERE id = 42 AND version = 5;
-- If another transaction already updated the row,
-- version no longer equals 5, zero rows are affected,
-- and the application must reload and retry.Follow-up Questions
- When would you prefer optimistic locking over pessimistic locking?
- How does a version column detect a lost update?
- What happens when an optimistic locking conflict occurs?
- How does pessimistic locking relate to SELECT FOR UPDATE?
MCQ Practice
1. Pessimistic locking works by?
Pessimistic locking acquires a lock upfront, assuming conflicts are likely, so other transactions must wait.
2. Optimistic locking typically detects conflicts using?
Optimistic locking compares a version or timestamp at write time to detect if another transaction changed the row.
3. Optimistic locking is generally preferred when?
Optimistic locking avoids upfront locking overhead, which pays off when conflicts are infrequent.
Flash Cards
What is pessimistic locking? — Locking a row before reading it so other transactions must wait until it is released.
What is optimistic locking? — Allowing concurrent reads/writes and checking for conflicts via a version column at commit time.
When to use pessimistic locking? — When conflicts are frequent and correctness under heavy contention matters most.
When to use optimistic locking? — When conflicts are rare and higher throughput/concurrency is the priority.