Pessimistic vs Optimistic Locking: Choosing by How Often Your Writes Collide

Two users open the same record, both edit, both save. Without a rule, the second save silently erases the first. The two classic rules differ in when they pay: before the work, with a lock, or after, with a check.

The lost update

t1  Alice reads balance = 100
t2  Bob   reads balance = 100
t3  Alice writes 100 + 50 = 150
t4  Bob   writes 100 - 30 =  70   <- Alice's deposit is gone

Both transactions were valid in isolation. The failure is in the gap between reading and writing.

Pessimistic: lock first

Take the lock when you read, hold it until you commit.

BEGIN;
SELECT balance FROM accounts WHERE id = 7 FOR UPDATE;   -- row locked
UPDATE accounts SET balance = balance + 50 WHERE id = 7;
COMMIT;                                                  -- lock released

Bob’s SELECT ... FOR UPDATE blocks until Alice commits, then sees 150. No lost update, no retry logic.

Strengths: predictable, each transaction succeeds once, safe when work between read and write has side effects. Costs: waiting, reduced throughput, possible deadlocks, and long locks if code does slow work inside the transaction.

Optimistic: check at the end

No lock. Add a version column and make the write conditional.

-- read
SELECT balance, version FROM accounts WHERE id = 7;     -- 100, version 4

-- write, only if nobody changed it
UPDATE accounts
SET balance = 150, version = version + 1
WHERE id = 7 AND version = 4;
-- rows affected = 0  ->  conflict: reload and retry, or tell the user

Many ORMs implement this with a version or timestamp annotation. In HTTP APIs, the same idea is the ETag plus If-Match header: update only if the resource is still the version you saw.

Strengths: no waiting, great read concurrency, scales well when conflicts are rare, works across requests and user think-time without holding locks. Costs: conflicts surface as errors you must handle, wasted work on retry, and poor behaviour on hot rows.

Choosing

Question	Optimistic	Pessimistic
How often do writers collide on the same row?	Rarely	Often
Cost of a retry?	Cheap, work is repeatable	Expensive, or has side effects
Read-to-write gap?	Long, user in the loop	Short, inside one transaction
Throughput priority?	High	Correctness over speed
Typical fit	Profiles, documents, settings, admin edits	Inventory of last items, seat booking, ledgers, job claiming

Practices that apply to both

1. Keep the critical section short

   Do external calls, slow computation and user interaction outside the transaction. The shorter the window, the fewer collisions and waits.

2. Lock at the finest useful granularity

   Rows, not tables. Use indexes so the engine locks only the rows you mean.

3. Order lock acquisition consistently

   When taking locks on several rows, always take them in the same order, for example by ascending id, to avoid deadlocks.

4. Retry with limits and backoff

   For optimistic conflicts and deadlock victims, retry a bounded number of times with jitter, then fail clearly. Make the operation idempotent so a retry is safe.

5. Measure conflict rates

   Count version-check failures and lock wait times. A rising retry rate on optimistic paths is the signal to redesign or switch.

Third options worth knowing

• Atomic statements: UPDATE stock SET qty = qty - 1 WHERE sku = ? AND qty > 0 needs neither strategy for simple counters.
• SELECT ... FOR UPDATE SKIP LOCKED: lets workers claim queue rows without blocking each other.
• Unique constraints: let the database reject the second insert instead of checking first.

Takeaway

Both strategies prevent lost updates, and they pay at different times. Optimistic locking pays rarely but sharply, in a retry. Pessimistic locking pays continuously, in waiting. Estimate how often your writers collide and what a retry costs, then choose, and keep every transaction short either way.