Optimistic vs pessimistic locking
Published
Two people open the same Google Sheet cell at the same moment and type different numbers. Two ticket agents read "1 seat left" and both sell it. The problem has a name, the lost update: both read the same starting value, both write a new value based on it, and the second write silently erases the first.
There are two ways to stop it. Pessimistic locking assumes a conflict will happen, so it locks the row before reading and makes everyone else wait. Optimistic locking assumes a conflict probably will not happen, so nobody waits; instead, every write checks that the row has not changed since it was read, and fails if it has.
BookMyShow at 10:00 am when a Coldplay concert opens: 500,000 people trying to grab the same few thousand seats. Would you let everyone read freely and detect clashes on save, or lock each seat as soon as someone looks at it? What goes wrong with each?
The lost update#
Pessimistic: lock first, then work#
In SQL, SELECT ... FOR UPDATE inside a transaction takes a row lock. Anyone else who tries
to lock or update that row waits until the first transaction commits or rolls back.
BEGIN;
SELECT status FROM seats WHERE id = '14C' FOR UPDATE; -- others wait here
-- application checks status = 'available'
UPDATE seats SET status = 'sold', user_id = 42 WHERE id = '14C';
COMMIT; -- lock releasedThe optimistic version needs no locks at all, which is why it works across HTTP requests that might be minutes apart. A user can open an edit form, go for chai, and come back; the version check catches anything that changed meanwhile. A pessimistic lock held across a user's coffee break would block everyone else.
Contention decides#
Neither is faster in general. It depends on how often two writers actually collide. With rare conflicts, optimistic wins: no waiting, and the occasional retry is cheap. With constant conflicts on the same row, optimistic turns into a storm of failed attempts, and orderly waiting wins.
Collisions are common enough that optimistic retries waste real work. Locks make writers queue in an orderly way instead.
The bottom row of that note is the real lesson for flash sales. If thousands of people write
the same row, the fix is to stop having one hot row: a row per seat rather than a
seats_left counter, inventory split into shards, or requests funnelled through a queue. The
IRCTC Tatkal design works through exactly this.
Transaction 1 locks seat A then wants seat B. Transaction 2 locks B then wants A. Both wait
forever. Databases detect this and kill one transaction, which your code must retry. Lock rows
in a consistent order (sort by id), keep transactions short, and never hold a lock while
calling an external API. For job queues, FOR UPDATE SKIP LOCKED lets each worker grab rows
nobody else holds instead of waiting.
| Choice | What you gain | What you pay | Pick it when |
|---|---|---|---|
| Pessimistic (SELECT FOR UPDATE) | No wasted work under heavy contention. Simple to reason about inside one transaction. | Waiting, deadlocks, locks held by slow or crashed clients, does not span user think time. | Short transactions on rows that are frequently contended: balances, inventory decrements, job queues. |
| Optimistic (version or ETag) | No locks, no waiting, works across HTTP requests and minutes of think time, scales reads. | Retries under contention, and the user may have to redo or merge their edit. | Most CRUD: profiles, documents, settings, admin edits, any form a person fills in. |
DynamoDB's conditional writes (ConditionExpression: version = :v), Elasticsearch's
if_seq_no, Redis's WATCH/MULTI, JPA's @Version and Kubernetes' resourceVersion are
all optimistic locking under other names.
In the interview#
Checkpoint#
1. How does optimistic locking detect a conflict?
2. Why is pessimistic locking a poor fit for an edit form a user keeps open for minutes?
3. Thousands of buyers per second update one stock counter row. Best fix?
- Lost update: two read, both write, the second erases the first.
- Pessimistic: SELECT FOR UPDATE, others wait. Short transactions, high contention.
- Optimistic: version column, UPDATE ... WHERE version = ?, check rows affected. Across think time.
- HTTP version: ETag + If-Match, 412 on conflict.
- A truly hot row needs redesign, not a different lock.
When two transactions read the same value and both write, the second silently erases the first, which is the lost update. Pessimistic locking prevents it by locking the row before reading, with SELECT FOR UPDATE, so others wait; it suits short transactions on frequently contended rows, but brings waiting and deadlocks, which I avoid by locking in a consistent order and keeping transactions short, and it cannot span user think time. Optimistic locking lets everyone read freely and makes each write conditional on the version it read; if zero rows are updated, someone else won, so the writer re-reads and retries or asks the user to merge. Over HTTP the same idea is an ETag with If-Match. The choice depends on contention: optimistic when collisions are rare, pessimistic when they are common, and when a single row is hammered, like a flash sale counter, I remove the hot spot by splitting it or queueing writes.