4.4.20 · D5Databases
Question bank — Optimistic vs pessimistic concurrency control
Before the traps, this page is self-contained: every symbol and jargon word below is defined here so you never have to click away mid-answer.
These traps target the exact places where intuition quietly betrays you: the meaning of "optimistic", where locks really hide, and the difference between a lost update (same row) and write skew (different rows, shared invariant).
The figure above lays the two anomalies side by side — keep it in mind for the "Spot the error" lost-update-vs-write-skew item.
The cost curves above show why the threshold exists: the optimistic curve starts below the flat pessimistic line , then shoots up past it at — the retry-storm region.
True or false — justify
True or false: Optimistic concurrency control never takes any lock at any moment.
False — the write/commit point still needs a brief atomic check so that validate-then-write is one indivisible step; OCC only removes locks from the long read-and-compute phase. See Optimistic vs pessimistic concurrency control (index 4.4.20).
True or false: Optimistic control is always faster because nothing ever waits.
False — with no waiting it wins only at low contention; as conflict probability the retry term explodes and retry storms waste all the lock-free work repeatedly.
True or false: MVCC and optimistic concurrency control are the same thing.
False — MVCC is a storage technique (keep multiple row versions so readers see a snapshot); it can power either strategy, e.g. Postgres uses MVCC with mostly pessimistic writer locks.
True or false: Two-Phase Locking (2PL) can be made deadlock-free just by being careful.
False — with many transactions grabbing dynamic key sets, deadlock is inherent to 2PL; you need detection (a wait-for graph) or timeouts, not just discipline.
True or false: Optimistic concurrency control can deadlock.
False — OCC aborts and retries instead of waiting on another transaction, so there is no circular wait to form a deadlock; its failure mode is livelock (endless retries), not deadlock.
True or false: A single-row version check (like WHERE version = 7) prevents write skew.
False — write skew has the two transactions write to different rows, so a per-row version check on each row passes independently while the shared invariant is still violated.
True or false: Concurrency control's goal is to make transactions run truly one-at-a-time.
False — the goal is to run them concurrently while producing a result as if they ran serially (serializability, the I in ACID); real serial execution would kill throughput.
True or false: SELECT ... FOR UPDATE is an optimistic technique.
False — it acquires an exclusive lock up front so others block, which is the defining behaviour of pessimistic control.
True or false: In 2PL, once a transaction releases any lock it may still acquire new ones later.
False — the "two phases" mean a strict growing phase (only acquire) then a shrinking phase (only release); the first release ends acquisition forever, and that ordering is what guarantees serializability.
Spot the error
Spot the error: "Optimistic wins whenever ."
The break-even is , which depends on the lock-cost and retry-cost — it is only in the special case ; expensive retries push the threshold well below .
Spot the error: "The WHERE version = 7 clause makes the read atomic."
It makes the write conditional, not the read; validation lives in the UPDATE that affects zero rows if someone bumped the version, signalling a retry — the earlier SELECT was ordinary and lock-free.
Spot the error: "Under no concurrency control, both ATMs writing is a write skew."
It is a lost update, not write skew — both transactions read and wrote the same row; write skew requires writes to different rows joined by a shared condition.
Spot the error: "Pessimistic only pays its cost when a conflict actually happens."
Reversed — pessimistic pays lock overhead always (even with no contention); optimistic is the one that pays (the retry cost ) only on the rare failure.
Spot the error: "OCC validation just re-reads the data and eyeballs it; no atomicity needed."
Without an atomic validate-then-write step, two transactions could both validate successfully and then both write, re-introducing the very anomaly OCC exists to stop.
Spot the error: "Since Postgres uses MVCC, all its writes are optimistic."
MVCC only governs how versions are stored; Postgres still takes pessimistic row locks on writes, and its optimistic behaviour appears only under Serializable Snapshot Isolation layered on top.
Why questions
Why does the optimistic cost use a geometric series instead of just ?
A retry can itself conflict with probability , so you might retry again and again; the expected number of retries is geometric with mean , capturing this cascade.
Why does write skew need predicate/range locking rather than plain row locks?
The conflict is over a condition spanning rows (an invariant like "at least one doctor on call"), so locking only the rows each transaction touches misses the phantom that violates the invariant.
Why do we say the whole optimistic/pessimistic decision reduces to one number?
Because both strategies just cost something; comparing against collapses to a single inequality in the conflict probability .
Why does OCC "livelock" instead of deadlock under extreme contention?
Transactions never wait on each other (no circular hold-and-wait), so they can't deadlock; instead each keeps validating, failing, and retrying — busy but making no progress.
Why is Compare-and-Swap (CAS) the same idea as the WHERE version = 7 trick?
Both say "write my new value only if the current value still equals what I read"; CAS does it on a memory word, the version-check does it on a row — a conditional write that detects intervening changes.
Edge cases
Edge case: a flash-sale where 10,000 buyers/sec hit a single row — which control and why?
Contention on that one row is enormous, so and ; OCC would livelock on endless retries, so pessimistic locking (or a queue/atomic decrement) wins.
Edge case: a nightly reporting job where transactions almost never overlap () — which control?
Conflicts sit far below the break-even threshold, so paying lock overhead every time is wasteful; optimistic control wins because it pays only on the rare retry.
Edge case: (every transaction conflicts) plugged into the optimistic formula — what happens?
divides by zero and diverges to infinity, meaning infinitely many expected retries — the model correctly says OCC is unusable at total contention.
Edge case: (no transaction ever conflicts) — what does each cost?
Optimistic costs (never retries) while pessimistic still pays for locks nobody needed, so optimistic wins by the full lock overhead.
Edge case: two transactions both pass OCC validation at the exact same instant — is that safe?
Only if the validate-and-write step is atomic (a short lock or CAS); otherwise both writes land and the anomaly returns, which is why "lock-free" OCC still needs one instantaneous guarded commit.
Edge case: exactly — where is the break-even?
, so the two strategies tie at a 50% conflict rate; below it go optimistic, above it go pessimistic.
Recall One-line summary to lock in
Pessimistic = lock first, work later (pay always); Optimistic = work freely, validate before commit (pay only on retry). Choose optimistic exactly when .