Optimistic vs pessimistic locking: assume conflict, or hope there isn’t one?

Pessimistic locking assumes a conflict is likely, so it takes a lock before reading-to-edit — SELECT … FOR UPDATE — and anyone else who wants that row waits until the lock is released. Optimistic locking assumes conflicts are rare, so it takes no lock: it reads the row (noting a version number or timestamp), does its work, and only at write time checks "is the version still what I read?" If yes, it writes and bumps the version; if the row changed underneath it, the write is rejected and the caller retries. One prevents collisions by blocking; the other detects them after the fact.

Both schemes are correct — they prevent the lost update (two writers both reading the old value and one silently overwriting the other). The choice between them is a performance bet on contention. Under low contention, optimistic wins easily: almost every write succeeds first try, and you paid zero locking overhead for the common case. Under high contention, the bet flips: optimistic writes keep colliding and retrying, and a stampede of retries can do more wasted work than simply queueing. There, pessimistic locking serializes access cleanly — everyone waits their short turn instead of racing, failing, and racing again.

Each strategy has a failure mode that shows up exactly when you picked the wrong one. Optimistic locking’s is the retry storm: on a hot row, writers keep reading the same version, colliding, and retrying in lockstep — throughput collapses as everyone redoes work that keeps getting invalidated. Worse, the retries can synchronize into waves. Optimistic under high contention doesn’t corrupt data; it just melts down on throughput.

Pessimistic locking’s failure mode is blocking and deadlock: while one transaction holds a lock, others stall, so long-held locks tank concurrency — and if two transactions grab locks in different orders (A then B versus B then A), they can wait on each other forever, a deadlock the database must detect and kill. That’s why pessimistic locks want to be short-lived and acquired in a consistent order. In short: optimistic wastes work on conflict, pessimistic makes others wait and risks deadlock — pick the failure you can better afford.

A user edits their own profile bio. Two people editing the same profile at the same second is vanishingly rare, and there’s long "think time" while they type. This is textbook optimistic: read the row with its version, save with "update … where version = N," and in the one-in-a-thousand case someone else saved first, show "this changed, please reload." No locks, full concurrency, and the rare conflict is cheap to handle.

Now decrement the stock of one hot item during a flash sale — thousands of buyers hitting the exact same row in the same instant. Optimistic here is a disaster: nearly every write collides and retries, a full-blown retry storm. This wants pessimistic locking (or, better still, a single atomic operation like UPDATE … SET stock = stock - 1 WHERE stock > 0) so the contending writers serialize cleanly instead of racing and failing. Same "update a number" operation, opposite locking choice — because the contention is opposite.