Optimistic vs Pessimistic Locking

Pradeep P
3 days ago
3 min read

Series: Modern System Design · Layer 4 — Data

Layer 4 · Post 4 of 11

← Previous: Isolation Levels → Next: Eventual Consistency

Layer 4 — Data · Post 43 of 88

Pessimistic locking blocks others before you edit. Optimistic locking lets everyone try, then checks for conflicts at commit time.

What you'll learn

SELECT FOR UPDATE versus a version column (or ETag) — when each is the right tool
Why low contention favors optimistic locking and hot rows favor pessimistic
How locking finishes the isolation story without turning every query into SERIALIZABLE

The idea in one minute

Isolation tells you what you see. Locking tells you who is allowed to change a row while you are thinking.

Pessimistic: grab the row first, then edit. Others wait.

BEGIN SELECT * FROM seats WHERE id = 42 FOR UPDATE -- nobody else can take seat 42 until COMMIT UPDATE seats SET user_id = $me WHERE id = 42 COMMIT

Optimistic: read a version, edit a copy, write back only if the version is unchanged. If someone else won, you retry.

UPDATE seats SET user_id = $me, version = version + 1 WHERE id = 42 AND version = $old -- 0 rows updated --> conflict, retry

Same goal: no two customers get the same seat. Different bet: "conflicts are rare" vs "this row is a fistfight."

Why it matters

Interviewers will give you a hot row (inventory of one, a seat map, a wallet) and wait to see if you only say "put it in a transaction." A transaction without a lock or a version check still loses updates under Read Committed.

This is also where you decide latency vs abort rate. Pessimistic adds wait time and deadlock risk. Optimistic adds retries. Neither is free.

How it works

Pessimistic in Postgres/MySQL is row locks: FOR UPDATE, FOR SHARE, sometimes SKIP LOCKED for work queues (workers grab unlocked jobs and skip the rest). Locks last until commit. Deadlocks happen when T1 holds A and wants B while T2 holds B and wants A. The engine aborts one. Your app retries.

Optimistic is usually a version integer, updated_at, or a hash. HTTP APIs use ETag / If-Match the same way. DynamoDB ConditionExpression on a version attribute is optimistic locking. Elasticsearch's _seq_no / _primary_term is the same idea.

Hybrid: read without a lock (optimistic), then take FOR UPDATE only on the hot path when you are about to write.

A simple example

A concert has 20,000 seats, almost all free. Optimistic: each checkout reads version, updates if it matches. Collisions are rare; you do not lock the table.

The last 10 tickets for a drop: everyone hits the same rows. Optimistic abort storms. Switch those SKUs to SELECT FOR UPDATE (or a single inventory counter row) so waiters queue instead of all retrying.

Stripe-style idempotency keys are a cousin: you lock or uniquely constrain the key so two retries do not double-charge.

Common mistakes

Optimistic without a retry loop. Returning 409 forever is not a locking strategy.

Pessimistic lock, then call a network API. You just serialized your checkout behind 3D Secure. Lock, write, commit, then call Stripe.

Locking the wrong grain. Locking every products row when you only needed inventory for one SKU. Or locking nothing because you updated with WHERE id = $id and no version.

Ignoring SKIP LOCKED for jobs. Using FOR UPDATE on a jobs table without skip means workers pile up on the same row.

How this shows up in real systems

Postgres / MySQL: FOR UPDATE, FOR UPDATE SKIP LOCKED, advisory locks for app-level mutexes.
DynamoDB: conditional writes (attribute_not_exists, version equals).
Hibernate / JPA: @Version (optimistic) vs LockModeType.PESSIMISTIC_WRITE.
HTTP: If-Match on documents; Google Docs-style OT/CRDT is optimistic at a different layer.

Recap

Pessimistic blocks then writes. Optimistic writes if the version matches.
Pick based on contention, not fashion. Retry is part of the design.
When the second system is not this database, locking this row is not enough. Next: eventual consistency — the world where replicas disagree on purpose.