Optimistic vs pessimistic concurrency control: Worked examples

This page is the exhaustive case-list for the parent topic. The parent gave you the idea (lock-first vs work-then-check) and the break-even inequality. Here we hit every corner: low/high contention, the exact break-even point, degenerate inputs ( $p = 0$ , $p = 1$ ), write skew that single-row checks miss, deadlock, and a real-world flash sale. Every number asserted on this page is re-checked by machine code in the hidden Verify block that ships with this note (the same numbers you see in each "Verify:" line).

Definition Notation used on this page

$p$ = the probability a given transaction hits a conflict. It is a probability, so its raw domain is $0 \leq p \leq 1$ . But on this page we work in $0 \leq p < 1$ — we deliberately exclude $p = 1$ up front. Why exclude it? Because the optimistic cost formula (built two boxes down) contains a $\frac{1}{1 - p}$ term, and at $p = 1$ that denominator is $0$ : the cost diverges to infinity (every transaction conflicts, forever, so it can never commit). So $p = 1$ isn't a "value you plug in" — it's a wall the formula runs into. Cell E studies exactly what happens as $p$ approaches that wall.
$L$ = lock overhead per transaction (ms). $R$ = cost of re-doing a transaction after a failed check (ms).
$p_{s t a r}$ = the break-even conflict rate: the single value of $p$ where optimistic and pessimistic cost exactly the same. Below it OCC wins, above it PCC wins. (We write it $p_{s t a r}$ in words everywhere to avoid any confusing superscript.)

Before we start, one reminder of the two quantities everything hinges on — and where the optimistic formula comes from, since the parent stated it but this page leans on it in every cell:

Formula The two cost formulas (with the retry sum re-derived)

What we compare — and what we drop. Both strategies must do the actual work: read the row, compute the new value, write it. Call that shared base cost $B$ . Since $B$ is paid once under either strategy, it appears on both sides of every comparison and cancels. So from here on $E [pess]$ and $E [opt]$ mean the overhead on top of $B$ — the lock cost vs the retry cost. That is the only thing that decides the winner.

Pessimistic: you always pay lock overhead, conflict or not: $E [pess] = L$ Optimistic: the read/compute phase is lock-free, but each attempt fails validation with probability $p$ and must retry. Count the extra attempts:

with prob $p$ you retry once (pay $R$ ),
that retry itself fails with prob $p$ , so with prob $p^{2}$ you retry a second time (pay another $R$ ),
and so on. The expected number of retries is the geometric sum $p + p^{2} + p^{3} + \dots = \frac{p}{1 - p},$ so $E [opt] = R \cdot \frac{p}{1 - p} .$

Optimistic wins when $E [opt] < E [pess]$ : $R \cdot \frac{p}{1 - p} < L ⟹ p < \frac{L}{L + R} = p_{s t a r} .$

Everything below is a specific point on the $p$ -axis or a specific anomaly shape. Let's name all the cells first.

The scenario matrix

Think of the whole topic as a grid. One axis is how likely a conflict is (the number $p$ ). The other axis is what shape the conflict has (same row? different rows? no real conflict at all?). Every worked example below is tagged with the cell it lands in.

Cell	Class	Concrete input	What it stresses
A	$p = 0$ (no conflict ever)	read-only report, $p = 0$	degenerate low end: does the formula stay finite?
B	$0 < p < p_{s t a r}$ (low contention)	$p = 0.02$ , $L = 2$ , $R = 8$	OCC should win comfortably
C	$p = p_{s t a r}$ (exact break-even)	$p = 0.2$ , $L = 2$ , $R = 8$	the tie — both cost the same
D	$p_{s t a r} < p < 1$ (high contention)	$p = 0.5$ , $L = 2$ , $R = 8$	PCC should win
E	$p \to 1$ (limiting / degenerate high end)	flash sale, $p \to 1$	OCC cost $\to \infty$ (livelock)
F	conflict shape = same row	lost update, two ATMs	row lock / version check catches it
G	conflict shape = different rows	write skew, two doctors	single-row check misses it
H	deadlock (PCC's own failure mode)	T1↔T2 cross-lock	why OCC never deadlocks
I	real-world word problem	inventory microservice	pick a strategy from a story
J	exam twist	"is MVCC optimistic?"	trap that mixes storage with strategy

We now walk every cell. Cells A–E are points along the contention axis; F–J are conflict-shape and reasoning cells.

Reading the figure below (in words): the horizontal axis is $p$ from $0$ to $1$ . The flat blue line at height $2$ is the pessimistic cost $E [pess] = L = 2$ ms — flat because it never depends on $p$ . The rising orange curve is the optimistic cost $E [opt] = R p / (1 - p)$ — it starts at $0$ on the far left, climbs slowly, and shoots up toward the right edge as $p$ nears $1$ . The two curves cross at the dashed gray vertical line $p_{s t a r} = 0.2$ . Left of the crossing (green band) OCC is cheaper; right of it (red band) PCC is cheaper. Each red dot is labelled with its cell letter (A–E) right on the plot, so the "far-left dot" / "high dot" pointers in the text below always match a labelled point in the graphic.

Figure — Optimistic vs pessimistic concurrency control

Worked examples

Cell A — $p = 0$ : no conflict ever

Worked example Read-only nightly report, zero conflicts

A reporting job runs at 3 AM. Nothing else writes. So $p = 0$ . Lock overhead $L = 2$ ms, retry $R = 8$ ms. Compute the expected cost of each strategy.

Forecast: OCC should be essentially free here — guess its cost before reading on.

Optimistic cost. Plug into $E [opt] = R \cdot \frac{p}{1 - p} = 8 \cdot \frac{0}{1 - 0} = 0$ ms. Why this step? With $p = 0$ the retry term vanishes — you never fail validation, so you never pay $R$ .
Pessimistic cost. $E [pess] = L = 2$ ms, always. Why this step? Pessimistic pays lock overhead unconditionally, even when nobody contends — that is its defining weakness.
Decision. $0 < 2 \Rightarrow$ OCC wins.

Verify: $\frac{p}{1 - p}$ at $p = 0$ is $0/1 = 0$ , finite and well-defined — no division blow-up. Units: ms compared to ms. ✅ This is the red dot labelled A at the far left of the figure, sitting on the axis at height $0$ .

Cell B — $0 < p < p_{s t a r}$ : low contention

Worked example Analytics workload, 2% conflicts

$p = 0.02$ , $L = 2$ ms, $R = 8$ ms. First find the break-even $p_{s t a r}$ , then decide.

Forecast: guess whether $0.02$ is above or below the tipping point.

Break-even. $p_{s t a r} = \frac{L}{L + R} = \frac{2}{2 + 8} = 0.2$ . Why this step? $p_{s t a r}$ is the single threshold separating "OCC wins" from "PCC wins"; compute it once and compare.
Compare $p$ to $p_{s t a r}$ . $0.02 < 0.2$ , so we expect OCC to win. Why this step? The inequality $p < p_{s t a r}$ is exactly the OCC-wins condition.
Confirm with cost. $E [opt] = 8 \cdot \frac{0.02}{0.98} \approx 0.163$ ms vs $E [pess] = 2$ ms. Why this step? Comparing the actual expected costs double-checks the threshold logic.

Verify: $8 \times 0.02/0.98 \approx 0.163 < 2$ ✅ OCC is ~12× cheaper. Units ms vs ms. This is the red dot labelled B hugging the bottom of the figure, well inside the green band.

Cell C — $p = p_{s t a r}$ : exact break-even

Worked example The tie: conflict rate sits exactly on the threshold

$p = 0.2$ , $L = 2$ ms, $R = 8$ ms. What happens at the knife-edge?

Forecast: guess the ratio of the two costs.

Optimistic cost. $E [opt] = 8 \cdot \frac{0.2}{0.8} = 8 \cdot 0.25 = 2$ ms. Why this step? This is the value $p_{s t a r}$ was defined to produce — plug it back to see the equality.
Pessimistic cost. $E [pess] = 2$ ms.
Decision. Costs are equal ( $2 = 2$ ) — a genuine tie; pick based on secondary concerns (deadlock risk, code simplicity). Why this step? At break-even the formula gives no preference; the tie-breaker is engineering, not arithmetic.

Verify: solving $R \cdot \frac{p}{1 - p} = L$ for the given $L, R$ gives $p = 0.2$ exactly, and both sides equal $2$ . ✅ This is the red dot labelled C, exactly where the orange curve crosses the blue line, on the dashed gray vertical.

Cell D — $p_{s t a r} < p < 1$ : high contention

Cell E — $p \to 1$ : the limiting degenerate case

Worked example Flash sale on one row, contention → 1

10,000 buyers hit the same inventory row per second. Effectively $p \to 1$ (but never exactly $1$ — recall we excluded $p = 1$ as the wall the formula runs into). What does OCC cost in the limit?

Forecast: guess the limit of $E [opt]$ as $p \to 1$ .

Take the limit. $p \to 1 lim R \cdot \frac{p}{1 - p} = R \cdot \frac{1}{0 ^{+}} = + \infty$ . Why this step? The denominator $1 - p$ shrinks to $0$ , so the expected number of retries explodes.
What "explodes" looks like: livelock.

Definition Livelock

A livelock is when transactions are busy but never finishing: each one keeps re-reading, re-computing, and failing validation because someone else committed first — like two people in a hallway stepping side-to-side forever, always moving, never passing. (Contrast a deadlock, where everyone is frozen and not moving.) Here every buyer's WHERE version = ... matches 0 rows over and over, so each restarts endlessly.
Numeric feel at $p = 0.99$ . $E [opt] = 8 \cdot \frac{0.99}{0.01} = 8 \cdot 99 = 792$ ms. Why this step? Showing $p = 0.99$ makes the "explosion" concrete: ~99 retries on average.
Decision. $792 ≫ 2$ ms $\Rightarrow$ use pessimistic (a single queue / atomic decrement per the Compare-and-Swap (CAS) pattern or a serialized lock).

Verify: $8 \times 0.99/0.01 = 792$ ✅ and the closed form diverges as $p \to 1$ . This is the red dot labelled E at the far right, where the orange curve rockets upward toward the top of the plot.

Cell F — conflict shape = same row (lost update)

Worked example Two ATMs, one account (deep re-run of the parent's Example 1)

Account balance $= \$ 100 $, v er s i o n$ = 7 $. A T M - 1 an d A T M - 2 b o t h w i t h d r a w$ $30 $. C or r ec t f ina l ba l an ce$ = $40$. Trace both strategies.

Forecast: without any control both write $\$ 70 $(w r o n g) . G u ess h o w e a c h f i x r e a c h es$ $40$.

No control (the bug). Both read $100$ , both compute $100 - 30 = 70$ , both write $70$ . One update is lost. Why this step? Establishes the anomaly we must defeat — a same-row read-modify-write race.
PCC fix. ATM-1 takes SELECT ... FOR UPDATE (X-lock), writes $70$ , commits. ATM-2 was blocked; it now reads the fresh $70$ , computes $70 - 30 = 40$ , writes $40$ . Why this step? The exclusive lock serializes the two — Two-Phase Locking (2PL) guarantees this order.
OCC fix. ATM-2 runs UPDATE ... SET balance=70, version=8 WHERE id=1 AND version=7. But ATM-1 already bumped version to $8$ , so ATM-2's WHERE version=7 matches 0 rows → abort, re-read $70$ , recompute $40$ , write with version=9. Why this step? The version mismatch is the validation failure — a conditional WHERE acts as the check.

Verify: correct final balance $= 100 - 30 - 30 = 40$ ; both fixes land on $40$ , not $70$ . ✅ Version sequence $7 \to 8 \to 9$ is strictly increasing.

Cell G — conflict shape = different rows (write skew)

Worked example Two on-call doctors — single-row checks MISS this

Invariant: at least 1 doctor on call. Currently Alice and Bob are both on call (2 rows, on_call = true). Each independently requests to go off-shift. Each transaction reads "count = 2 ≥ 1, safe" then sets its own row to false. Result: 0 on call — invariant broken.

Forecast: guess why a plain per-row version check does NOT save you here.

Why plain OCC / row locks fail. Alice writes row Alice; Bob writes row Bob. They touch different rows, so neither version check nor row X-lock collides. Why this step? This is exactly the Write Skew and Phantoms pattern — the conflict is over a condition across rows, not a single cell.
PCC fix. Use predicate/range locking: lock the set "doctors where on_call = true". The second transaction's read-lock on that predicate blocks (or is caught) → serialized → one doctor stays on. Why this step? The invariant lives on a predicate, so the lock must too.
OCC fix (serializable snapshot isolation). Validation tracks the read set (the predicate "count on call"), not just written rows. If Bob's write changed a row Alice read for her decision, one commit is aborted. Why this step? SSI layers optimistic validation over MVCC and covers read-write dependencies — see Serializability and Isolation Levels.

Verify: with the fix, exactly one of {Alice, Bob} commits their off-shift; final on-call count $= 2 - 1 = 1 \geq 1$ (invariant holds). ✅ Without it, count $= 2 - 2 = 0 < 1$ (violated).

Cell H — deadlock: PCC's own failure mode

Worked example Cross-locking two rows

T1 locks row $A$ then wants $B$ . T2 locks row $B$ then wants $A$ . Neither releases (both in the growing phase of 2PL). Trace what OCC would do instead.

Forecast: guess how many transactions make progress under pure 2PL here.

PCC outcome. T1 holds $A$ , waits on $B$ ; T2 holds $B$ , waits on $A$ . Circular wait → deadlock. Progress $= 0$ until a detector/timeout aborts one. Why this step? Under 2PL you may not acquire a new lock after releasing one, so neither transaction can voluntarily back off and release mid-way — a wait-for cycle forms and freezes both.
PCC resolution. A deadlock detector finds the cycle in the wait-for graph, aborts (say) T2. T1 then finishes; T2 retries from scratch. Net: 1 abort forced by the lock mechanism, not by validation. Why this step? Shows PCC still sometimes retries — but its retries are triggered by deadlock detection, a cost invisible in the simple $E [pess] = L$ model.
OCC outcome — and why it makes progress. OCC holds no locks during the read/compute phase, so no transaction ever waits on another's lock → the wait-for graph has no edges → no cycle → 0 deadlocks. Validation runs at the instant of commit (the write phase) and is atomic: whichever transaction reaches its commit-time version check first wins and commits; the loser sees a bumped version, aborts, and retries. Why this step? This is the precise contrast — PCC can freeze in a cyclic wait; OCC cannot, because nobody blocks on a held lock, so there is nothing to form a cycle out of.
Why OCC still guarantees forward progress. At every commit round some transaction reaches the atomic check first, so some transaction always commits. The loser never blocks the winner — it simply re-runs. So the system as a whole always advances (no permanent freeze), even though an individual unlucky transaction may retry. Why this step? Distinguishes "no deadlock" (structural) from "makes progress" (a live winner exists each round) — both hold for OCC; only the second can fail under extreme contention as livelock (Cell E).

Verify: OCC holds no locks during the read phase, so its wait-for graph has no edges → no cycle → 0 deadlocks. Under PCC exactly 1 of the 2 transactions is aborted to break the cycle. ✅

Cell I — real-world word problem

Worked example Inventory microservice: pick a strategy from the story

An e-commerce catalog has 1,000,000 SKUs. Orders arrive uniformly across all SKUs at 5,000 orders/sec. Two orders touch the same SKU only rarely. The measured conflict rate is $p = 0.001$ . Overheads $L = 2$ ms, $R = 8$ ms. Which control?

Forecast: with a million spread-out SKUs, guess whether conflicts clear the threshold.

Break-even. $p_{s t a r} = \frac{L}{L + R} = \frac{2}{2 + 8} = 0.2$ . Why this step? Establishes the tipping point to compare the measured $p$ against.
Compare. $p = 0.001 ≪ 0.2$ , so OCC is favoured. Why this step? Load spread over a million rows means tiny $p$ — the exact situation OCC was built for.
Cost check. $E [opt] = 8 \cdot \frac{0.001}{0.999} \approx 0.008$ ms vs $E [pess] = 2$ ms. Why this step? Confirms OCC is ~250× cheaper; a per-SKU version column suffices, no lock overhead.
Decision. Optimistic (version-column check per SKU). Why this step? Both the threshold test and the raw cost agree — a robust pick.

Verify: $8 \times 0.001/0.999 \approx 0.00801 < 2$ ✅ (Contrast with Cell E's flash sale on one SKU where $p \to 1$ flips the same shop to pessimistic — same business, opposite answer, driven purely by $p$ .)

Cell J — exam twist (the trap)

Worked example "Since MVCC keeps versions, MVCC = optimistic. True or false?"

Forecast: guess before reading — versions feel optimistic.

Separate the two axes. MVCC is a storage technique (keep multiple row versions so readers see a consistent snapshot). Optimistic/pessimistic is a strategy (when to check for conflicts). These are two independent choices. Why this step? The trap conflates how data is stored with how conflicts are resolved.
Counter-examples both ways. PostgreSQL uses MVCC with mostly-pessimistic writer locks. Serializable Snapshot Isolation uses MVCC with optimistic validation on top. Why this step? One storage layer powers both strategies → the claim can't be an identity.
Answer. False. MVCC ≠ optimistic; it's orthogonal. This still delivers the I (isolation) of ACID properties, but by either route. Why this step? Names the actual relationship (orthogonal, not equal) and ties it back to the isolation goal.

Verify: logical check — if MVCC implied optimistic, then no MVCC system could use pessimistic locks; but Postgres (MVCC + row X-locks) is a working counterexample, so the implication is false. ✅

Recall Which cell is which — self-test

$p = 0$ , cost of OCC ::: $0$ ms (retry term vanishes; formula stays finite). Why is $p = 1$ excluded up front ::: because $E [opt]$ has a $1/ (1 - p)$ term that diverges to infinity there — it's a wall, not a value. What shared cost cancels from the comparison ::: the base read/compute/write cost $B$ , paid once under both strategies. Break-even $p_{s t a r}$ with $L = 2, R = 8$ ::: $0.2$ . OCC cost at exact break-even $p = 0.2$ ::: $2$ ms — a tie with PCC. OCC cost at $p = 0.5$ (hot counter) ::: $8$ ms — PCC wins. Limit of OCC cost as $p \to 1$ ::: $+ \infty$ (livelock). Anomaly where writes hit different rows ::: write skew (needs predicate/serializable, not row check). Which strategy can deadlock ::: pessimistic (2PL); OCC aborts instead, never deadlocks. When does OCC validation run ::: at the atomic commit instant (write phase); the first to reach it wins, guaranteeing progress. Cell I decision (million SKUs, p=0.001) ::: optimistic — conflicts far below threshold. Is MVCC the same as optimistic ::: No — MVCC is storage, orthogonal to the strategy.