Pessimistic Row Locking
Pessimistic locking takes real locks before reading — SELECT FOR UPDATE — so concurrent transactions block instead of conflicting later, chosen when contention is high and retrying is expensive.
Definition
Pessimistic locking acquires database locks at read time via SELECT FOR UPDATE or FOR SHARE, holding them until commit. Contention becomes waiting rather than retrying. It suits hot rows, short transactions, and flows where failure mid-sequence is costly. The counterpart, optimistic concurrency, versions rows and rejects conflicting writers.
Context and scope
Used for inventory decrements, seat holds, account balance moves, and queue claim patterns. Interacts with lock ordering, skip-locked semantics for work queues, and NOWAIT variants for fail-fast.
Common misconceptions
- Pessimistic locking serializes the whole table — row locks scope to rows; table-level effects come from lock escalation and gap locks under serializable isolation
- Blocking waits are safe — unbounded waits are deadlocks and timeouts in disguise; NOWAIT and lock_timeout bound them
- It replaces optimistic concurrency — both model the same correctness; the choice is contention economics, not correctness
Anti-patterns
- Holding row locks across user think time or remote calls and manufacturing deadlocks
- Locking in different orders across code paths and hitting deadlock detection in production traffic
- Using FOR UPDATE for work-queue claims without SKIP LOCKED and watching workers serialize on one row
Related concepts
Related examples
Authoritative source
https://www.postgresql.org/docs/current/explicit-locking.html
What it is
Two transactions updating one row cannot both win. Pessimistic policy resolves it early: the first reader locks, the second waits. Optimistic policy resolves it late: both proceed, one fails at commit. Pessimistic wins when conflicts are frequent and retries are expensive; optimistic wins when conflicts are rare.
How it works
SELECT FOR UPDATE takes exclusive row locks; FOR SHARE allows concurrent readers to hold shared locks. PostgreSQL adds FOR NO KEY UPDATE, NOWAIT, and SKIP LOCKED for queue claims that must not queue behind each other. InnoDB adds gap and next-key locks under higher isolation, which can lock ranges rather than rows.
When to use it
In-flight state machines, counters with invariants, and claim-then-process queues. Keep locked sections short, lock in a canonical order across all code paths, and set lock_timeout so waits become errors you can measure.
Pitfalls
A lock held across an external call converts contention into systemic stalls. Under MVCC, plain reads do not block writers — teams discover their read-then-write check was never protected and add FOR UPDATE after the corruption incident. Test lock ordering under concurrency, not just single-threaded flows.