Data consistency

Optimistic vs pessimistic locking

Data consistencyConcurrency controlDecision guide

Short answer

Default to optimistic locking. It costs nothing when conflicts are rare, which is the normal case, and it never blocks a reader. Switch a specific path to pessimistic locking when contention on the same row is high enough that retries waste more work than a short queue would — in practice, when the conflict rate exceeds a few percent, or when the operation is too expensive to redo.

Written and reviewed by Sahil Srivastav

What each one actually is

Optimistic locking assumes conflicts are rare and detects them at write time. Each row carries a version (or a timestamp, or the previous value), the update asserts that the version is unchanged, and an affected-row count of zero means somebody else got there first. No locks are taken, so nothing blocks — but the loser must redo its work.

Pessimistic locking assumes conflicts are likely and prevents them at read time. The reader takes an exclusive row lock (`SELECT ... FOR UPDATE`), so concurrent readers of that row wait until the lock is released. Nothing is ever redone, but throughput on that row is serialised and holders can block many waiters.

The names describe the assumption, not the mechanism: optimistic is optimistic *about the conflict rate*. That is why the choice is really a question about your data, not about your code.

Side by side

	Optimistic locking	Pessimistic locking
When conflicts are detected	At write time, after the work is done	At read time, before the work starts
Cost when there is no conflict	Effectively zero — one extra predicate	A lock acquisition and release per operation
Cost when there is a conflict	The whole transaction is redone	The waiter blocks, then proceeds
Behaviour under high contention	Degrades badly — retry storms, wasted work	Degrades predictably — a queue forms
Deadlock risk	None; no locks are held	Real, if rows are locked in inconsistent order
Works across requests / stateless clients	Yes — the version travels with the client	No; a lock cannot span HTTP requests
Failure surfaced to the caller	A conflict the caller must handle or retry	Latency, and a lock timeout at worst
Needs schema support	A version or updated_at column	None

Choose Optimistic locking when

Conflicts are rare — most rows are edited by one actor at a time
The edit spans multiple requests, such as a user loading a form and submitting minutes later, where no lock could be held anyway
You cannot tolerate readers blocking, for example on a row that dashboards read constantly
The work being redone on conflict is cheap
You want lost-update protection with no deadlock risk at all

Choose Pessimistic locking when

Many actors contend for the same row — a hot inventory count, a shared balance, a popular seat
The work is expensive enough that redoing it is worse than waiting
You must lock several rows together and need a stable order to prevent cycles
The business rule requires strict first-come-first-served ordering
Conflict rate has already been measured above a few percent and retries are visibly wasting capacity

The trade-off in detail

The trade-off is where you pay, not whether you pay. Optimistic locking moves the cost to the unhappy path and makes the happy path free; pessimistic locking charges a small fixed cost on every operation to make the unhappy path cheap. Under low contention that is a clear win for optimistic. Under high contention optimistic locking exhibits a failure mode that catches teams out: throughput can *fall* as load rises, because more concurrency means more conflicts means more redone work means more concurrency. A queue is ugly but it is monotonic.

Optimistic locking also fails in a way you must handle explicitly. A zero affected-row count is not an error the database raises — it is a value your code has to check. Code that runs the update and ignores the count has implemented nothing, and this is by far the most common way optimistic locking is broken in practice: the version column exists, the predicate is there, and nobody looks at the result.

Pessimistic locking brings deadlocks with it the moment a transaction locks more than one row. Two transactions locking the same pair in opposite orders form a cycle. This is solvable — sort the rows and always lock in one global order — but it is a real obligation that optimistic locking does not impose.

Often the best answer is neither: make the update atomic and conditional so there is nothing to lock and nothing to retry. `UPDATE inventory SET qty = qty - $1 WHERE id = $2 AND qty >= $1` enforces the invariant in the database, and a zero row count is a business outcome rather than a concurrency failure. Reach for locking only when the decision genuinely requires reading state into application code first.

-- Optimistic: assert the version, then check the count
UPDATE documents
   SET body = $1, version = version + 1
 WHERE id = $2 AND version = $3;
-- 0 rows affected => somebody else saved first. Re-read, re-apply, or tell the user.

-- Pessimistic: serialise access up front
BEGIN;
SELECT * FROM documents WHERE id = $1 FOR UPDATE;   -- concurrent readers wait here
UPDATE documents SET body = $2 WHERE id = $1;
COMMIT;

-- Often better than both: atomic and conditional, nothing to lock or retry
UPDATE inventory SET qty = qty - $1 WHERE id = $2 AND qty >= $1;

Things that are commonly said and are wrong

“Optimistic locking takes no locks, so it is not safe.” It is safe: the atomic compare-and-set in the `UPDATE` predicate is the enforcement, and the database applies it under a row lock for the duration of that statement. What it does not do is *hold* a lock across your thinking time.
“Pessimistic locking is slower.” Only per-operation, and only when there is no contention. Under heavy contention it is frequently faster overall, because it does not redo work.
“`SELECT ... FOR UPDATE` blocks readers.” It blocks other `FOR UPDATE` readers and writers. A plain `SELECT` under MVCC still reads the previous committed version without waiting — which is exactly why PostgreSQL and MySQL/InnoDB can serialise writers without stalling reads.
“Optimistic locking is the same as optimistic concurrency in HTTP.” The idea is the same, the carrier differs: HTTP uses `ETag` plus `If-Match` and returns `412 Precondition Failed`. The version lives in a header rather than a column.
“Adding a version column gives you optimistic locking.” Only if every writer includes it in the `WHERE` clause and checks the affected-row count. One writer that omits the predicate silently defeats it for everyone.

Decide it in a real repository

Gronex ships the decision as a runnable repository: inventory that oversells because the read and the write are separate. The tests hammer it with concurrent buyers and assert stock never goes negative — so whichever strategy you choose, it has to actually hold.

FAQ

Which should I use by default?

Optimistic. Conflicts are rare in most systems, the happy path costs nothing, readers never block, and there is no deadlock risk. Move a specific path to pessimistic locking when you have measured contention on it, not in anticipation.

Can I use both in one system?

Yes, and mature systems usually do — optimistic for ordinary entity edits, pessimistic for the handful of genuinely hot rows. The choice is per access path, not per application. What you must not do is mix strategies on the same row, since a writer that skips the version predicate defeats it for everyone.

How do I know contention is high enough to switch?

Measure the conflict rate: failed optimistic updates divided by attempts. Below roughly one percent, optimistic is free. Into double digits, you are burning most of your capacity redoing work and a lock will be faster. The middle is a judgement call weighted by how expensive the redone work is.

Does optimistic locking work across HTTP requests?

Yes, and this is its decisive advantage. The version travels to the client and comes back with the submission, so a user editing a form for ten minutes is safely detected. A database lock cannot span requests — holding one across user think time is how connection pools die.