Q27 — What is optimistic locking vs pessimistic locking — and when does each break?

What problem does locking solve?

Two users simultaneously try to update the same row in a database. Both read the row — say, a product with 10 units in inventory. Both decide to decrement it by 1. Both write back 9. The database now shows 9 units in stock, but 2 were actually sold. One sale is lost.

This is the lost update problem — a classic race condition where concurrent reads followed by concurrent writes produce a result that neither writer intended and the data cannot justify. The database's atomicity guarantees that each individual write succeeds, but it says nothing about the logical correctness of their combined effect.

Locking is the mechanism for enforcing that concurrent modifications to the same data are handled in a controlled, serialized way. The two main strategies — pessimistic and optimistic — differ in when they acknowledge that conflict might occur and how they respond to it.

Pessimistic locking — assume conflict, prevent it upfront

Pessimistic locking assumes that if two transactions want the same data, they probably will conflict. Better to prevent the conflict before it happens.

The mechanism: before reading data you intend to modify, acquire an exclusive lock on it. No other transaction can read-for-update or modify that data until you release your lock (at commit or rollback).

In SQL this is SELECT ... FOR UPDATE:

BEGIN;
SELECT inventory FROM products WHERE id = 42 FOR UPDATE;
-- the row is now locked; no other transaction can SELECT FOR UPDATE it
UPDATE products SET inventory = inventory - 1 WHERE id = 42;
COMMIT;

Transaction B that tries to SELECT ... FOR UPDATE on the same row will block — it waits at its SELECT until Transaction A commits. Then B proceeds with the now-current value. The two transactions execute as if they were sequential. No lost update.

When pessimistic locking works well: High-contention scenarios — many concurrent writers fighting over the same row. A seat booking system where the last seat is being claimed by dozens of simultaneous users. A bank account that receives frequent transfers. In these cases, conflicts are genuinely frequent, and letting them happen only to detect and retry them later (optimistic locking) would mean constant retries. Better to serialize upfront.

When pessimistic locking breaks down:

Deadlocks. Transaction A holds a lock on row 1 and waits for row 2. Transaction B holds a lock on row 2 and waits for row 1. Neither can proceed. The database detects the cycle and kills one transaction as the victim. The victim must retry. At high concurrency with complex lock graphs, deadlocks become frequent, and the overhead of detection and retry erodes throughput.

Long-held locks block everyone. If your transaction reads a row, then calls an external API, then writes — the lock is held during the external API call. While your transaction waits for the API, every other transaction trying to touch that row is frozen. A slow external call becomes a system-wide bottleneck. Pessimistic locking is only safe when the lock is held for the minimum possible time — ideally just the database write, not anything involving network calls or user input.

Throughput collapses under scale. Locks serialize access. Serialization is the enemy of parallelism. On a system with thousands of concurrent users, heavy pessimistic locking can reduce effective throughput to near single-threaded levels for hot rows.

Optimistic locking — assume no conflict, detect it at write time

Optimistic locking takes the opposite bet: conflicts are rare. Let transactions proceed without blocking each other. At write time, check whether anything changed since you read the data. If it did, someone else modified it — the conflict is detected and the current transaction retries.

The mechanism: add a version column (or use a timestamp) to each row. When you read a row, record its current version number. When you write, include the version in the WHERE clause:

-- Read:
SELECT id, inventory, version FROM products WHERE id = 42;
-- Returns: inventory=10, version=7

-- Write (only succeeds if nobody else modified the row since we read it):
UPDATE products
SET inventory = 9, version = 8
WHERE id = 42 AND version = 7;

-- Check rows affected:
-- 1 row updated → success, no conflict
-- 0 rows updated → conflict detected, someone else changed it first → retry

If two transactions both read version 7 and both try to write version 8, only one will find version = 7 still true. The other will update 0 rows. That transaction detects the conflict, re-reads the current data, recomputes its change, and retries.

No locks are held between the read and the write. Multiple transactions can read the same row simultaneously with no blocking. The cost of conflict is only paid when a conflict actually occurs.

When optimistic locking works well: Low-contention scenarios — reads dramatically outnumber writes, and simultaneous writes to the same row are rare. User profile edits: you read your profile, spend 30 seconds editing it, then save. It's very unlikely someone else edited your profile in that 30-second window. Optimistic locking lets many such long-running operations proceed in parallel without any blocking.

When optimistic locking breaks down:

High contention causes starvation. If many transactions compete for the same row, only one wins each round. All others fail, re-read, and try again — only to find someone else won again. Under extreme contention, a transaction might retry dozens of times before succeeding. This is livelock — the system is doing enormous work (many reads, many failed writes, many retries) while making very slow forward progress. The user experience is unpredictable latency.

Retry logic must be application-implemented. The database tells you "0 rows updated" — it's your application code that must detect this, decide whether to retry, re-read the current state, re-execute the business logic, and attempt the write again. This logic is easy to get wrong, especially when the operation has side effects that shouldn't be repeated (sending an email, charging a card) — those effects must be separated from the data modification and guarded by idempotency.

The two fixes, side by side

Same race, both strategies. Step through each and notice who waits versus who retries.

How to choose

The question is really: what is the cost of a conflict?

If conflicts are rare and retrying is cheap → optimistic. You avoid the overhead of locking on every read and gain maximum parallelism for the common case.

If conflicts are frequent or retrying is expensive (complex computation, external calls, user-facing latency) → pessimistic. Pay the serialization cost upfront to guarantee forward progress without wasted work.

In practice, most modern applications default to optimistic locking via ORMs (Hibernate, ActiveRecord all support version columns natively) because most data is not heavily contended. Pessimistic locking is reached for explicitly when a specific hot spot — limited inventory, a shared counter, a competitive resource — genuinely requires it.

The mental model

Lost update problem:
  Read A → Read B → Write A (loses B) → Write B (loses A)
  Both writes succeed, one update is silently dropped

Pessimistic locking:
  Lock row before read → only one writer at a time
  Pro: no conflicts possible, guaranteed forward progress
  Con: deadlocks, lock held during slow operations, reduces parallelism
  Use when: high contention, short transactions, conflicts are frequent

Optimistic locking:
  Read row + version → write with WHERE version = N → 0 rows = conflict → retry
  Pro: no blocking on read, maximum parallelism for the common case
  Con: starvation under high contention, retry logic in application
  Use when: low contention, reads >> writes, long operations between read and write

Rule of thumb:
  Default → optimistic (let the DB and ORM handle version columns)
  Hot spots with genuine contention → pessimistic with minimal lock hold time

The choice is a bet about conflict frequency. Pessimistic says "I know conflicts will happen, pay the cost now." Optimistic says "conflicts are rare, detect them cheaply when they occur." Both are correct in the right context.