What is the difference between optimistic and pessimistic locking?
Pessimistic locking locks the row up front so others must wait, assuming conflicts are likely. Optimistic locking takes no lock and checks at update time, usually with a version number, whether someone else changed the row.
Explain it simply
Pessimistic is like putting a "reserved" board on a library book before you start reading, so nobody else can touch it. Optimistic is reading freely, and at checkout the librarian checks the book's edition number, and if someone changed it meanwhile, asks you to start again.
Example
Pessimistic:
BEGIN;
SELECT stock FROM products WHERE id = 7 FOR UPDATE; -- row locked
UPDATE products SET stock = stock - 1 WHERE id = 7;
COMMIT;Optimistic, with a version column:
SELECT stock, version FROM products WHERE id = 7; -- stock=5, version=3
UPDATE products
SET stock = 4, version = version + 1
WHERE id = 7 AND version = 3;
-- if 0 rows updated, someone else changed it, so re-read and retry| Pessimistic | Optimistic | |
|---|---|---|
| Locks held | Yes, during the transaction | No |
| Best when | Conflicts are frequent | Conflicts are rare |
| Cost of conflict | Waiting, possible deadlocks | Retry |
What to say in the interview
"Pessimistic locking blocks others with SELECT FOR UPDATE. Optimistic uses a version column and a conditional update, detecting conflicts at write time. I pick optimistic for read-heavy, low-conflict workloads and pessimistic for contested rows like a last ticket."
Common mistakes
- Forgetting to check the affected row count in the optimistic update.