Optimistic vs pessimistic concurrency control: Foundations

This page assumes you have seen nothing. We build every word, letter, and squiggle the parent topic note leans on, in an order where each brick rests on the one before it.

1. What is a "transaction"?

Picture a bank transfer: "take $30 from Asha, add $30 to Bharat." That is one transaction with two steps. If the machine crashes after step one, you must not be left with Asha $30 poorer and Bharat unchanged. The bundle is the unit we reason about.

Figure — Optimistic vs pessimistic concurrency control

Why the topic needs it. Concurrency control is entirely about what happens when two of these bundles run at the same moment. If we could not name "one bundle," we could not talk about "two bundles overlapping." The all-or-nothing rule is the A (Atomicity) in ACID properties.

2. Commit and abort — how a transaction finishes

Every transaction ends in exactly one of these two ways. Think of the bank transfer as a pencil draft: while you are working, your edits are in pencil (tentative). Commit = ink them in for good. Abort = erase the whole draft back to blank.

Why the topic needs it. The whole difference between "safe" and "dangerous" reads depends on when another transaction commits or aborts. Optimistic control's retry happens at the commit point; pessimistic locks are held right up until commit. Without these two words the timing of §3 is meaningless.

3. "Concurrent" and the picture of interleaving

The word concurrent just means "running during the same stretch of time." It does not promise they run on different CPUs — it only means their steps are shuffled together like two decks of cards.

Why the topic needs it. Every anomaly the parent page fears (lost update, write skew) is a bad shuffle of steps. Without the picture of interleaving you cannot see how "read, read, write, write" produces a wrong answer.

4. The read-modify-write cycle (where clashes are born)

Most work follows one shape:

Read — fetch a value, e.g. balance = 100.
Modify — compute a new value in your head, e.g. 100 - 30 = 70.
Write — store it back.

A second anomaly hides in the same cycle:

So the three anomalies the parent page names are all specific bad timings of read-modify-write: lost update (two writes clobber each other), dirty read (reading uncommitted work), and write skew (defined in §9). This cycle is the atom of every example on the parent page.

5. Serializable — the gold standard we defend

Think of it as the honest referee: "I don't care how you shuffled the steps, as long as the outcome matches some clean take-turns ordering." This is the I (Isolation) in ACID properties, and it is defined precisely in Serializability and Isolation Levels.

Why the topic needs it. Both strategies exist only to guarantee serializability while still allowing overlap. That is the promise; locking and validation are two ways to keep it.

6. A "lock" — the pessimistic tool

A Shared (S) lock = "I'm only reading; other readers may share." Many S-locks can coexist.
An Exclusive (X) lock = "I'm writing; nobody else touches this." Only one X-lock at a time.

Why the topic needs it. Pessimistic control is locks. Two-Phase Locking (2PL) is the rule for when to grab and release them. And when two transactions each wait on a lock the other holds, you get a deadlock — the price of the pessimistic approach.

7. A "version number" — the optimistic tool

If you read version = 7, do your work, and at commit the row still says version = 7, then nobody touched it — safe to write. If it now says 8, someone slipped in — you must retry. This is exactly the parent's UPDATE ... WHERE version = 7 trick, and it is a database cousin of Compare-and-Swap (CAS). Keeping several versions of each row around so readers always see a stable snapshot is a separate storage idea called MVCC (Multi-Version Concurrency Control) — see MVCC.

Why the topic needs it. Optimistic control replaces "wait in line" with "check a number at the end." The version number is how the check is performed.

8. The letters $p$ , $L$ , $R$ — reading the cost formula

The parent page's whole decision rests on three symbols. Here is each, from zero:

What "probability" means, pictured. Imagine running the transaction $100$ times. If it clashes on $20$ of those runs, $p = 20/100 = 0.2$ . It is just a fraction of runs that go wrong.

Intuition Why a geometric series shows up

Each attempt clashes with probability $p$ and needs another retry. Let $N$ be the number of retries. The chance you need at least $k$ retries is the chance you clashed $k$ times in a row, which is $p^{k}$ (so $P (N \geq k) = p^{k}$ ).

Why the expected count is a sum of tail probabilities. For any random variable $N$ that only takes whole-number values $0, 1, 2, \dots$ there is a neat identity: $E [N] = \sum_{k = 1}^{\infty} P (N \geq k) .$ Why is this true? Each outcome $N = n$ contributes $n$ to the average. But $n$ is just how many of the thresholds ${N \geq 1}, {N \geq 2}, \dots, {N \geq n}$ that outcome satisfies. So instead of counting "value $n$ , once," we count "one point for each threshold it clears." Summing across all outcomes, threshold $k$ collects one point from every outcome with $N \geq k$ — i.e. $P (N \geq k)$ . Adding those gives exactly $E [N]$ . Plugging $P (N \geq k) = p^{k}$ : $E [N] = \sum_{k = 1}^{\infty} p^{k} = p + p^{2} + p^{3} + \dots$ Deriving the closed form. Call the sum $S = p + p^{2} + p^{3} + \dots$ . Multiply every term by $p$ : $pS = p^{2} + p^{3} + p^{4} + \dots$ . Subtract the second line from the first — every term cancels except the very first $p$ : $S - pS = p ⟹ S (1 - p) = p ⟹ S = \frac{p}{1 - p} .$ (This division is legal because $0 \leq p < 1$ , so $1 - p > 0$ .) As $p \to 1$ the denominator $\to 0$ and $S \to \infty$ .

The edge case $p = 1$ . If every attempt conflicts, the formula is undefined (division by zero) and the true meaning is that retries never stop — an optimistic transaction would loop forever (a livelock). So at $p = 1$ optimistic control is infeasible; you must go pessimistic (or use a queue / atomic decrement), exactly as the parent's flash-sale example concludes.

The figure below turns that into a picture: it plots $\frac{p}{1 - p}$ (the expected retries) against the conflict rate $p$ .

How to read the figure. The lavender curve is the expected number of retries; it is nearly flat and tiny while $p$ is small, then rockets upward as $p$ approaches $1$ (right edge). The dashed coral line marks the break-even conflict rate $p^{*}$ (derived next); in the green shaded region to its left, optimistic is cheaper, and to its right, pessimistic wins.

Why the topic needs it. This single inequality is the parent page's punchline. Every worked example just plugs numbers into it.

9. Write skew — the anomaly that needs more than row locks

Two doctors both read "2 on call," each safely goes off-shift (writing their own row), and now zero remain. Neither touched the other's row, so a single-row version check or row lock never fires. You need predicate locking or serializable isolation.

Why the topic needs it. It proves the defence must cover conditions across rows, not just single cells — a subtlety the parent stresses.

Prerequisite map

The diagram below shows how each foundation feeds the next, ending in "pick a strategy."

Text version (in case the diagram does not render):

Transaction (all-or-nothing bundle) → ends in a Commit or Abort.
Commit/Abort → makes Concurrent / interleaving meaningful (timing matters).
Interleaving → the Read-Modify-Write cycle → the three Anomalies (lost update, dirty read, write skew).
Transaction also feeds ACID (Atomicity + Isolation) → the Serializable goal.
Anomalies + Serializable goal → two defences: Pessimistic (Locks + 2PL), which can lead to Deadlocks, and Optimistic (Version check), a cousin of Compare-and-Swap.
Both defences feed the cost model ( $L$ , $R$ , $p$ ) → the break-even $p^{*} = \frac{L}{L + R}$ → the final decision: pick a strategy.

Equipment checklist

Test yourself — cover the right side and answer each before revealing.

What does "all-or-nothing" mean for a transaction?

Either every step inside it takes effect, or none of them do.

What does it mean to commit a transaction?

To make all its changes permanent and visible to everyone; there is no undo after commit.

What does it mean to abort (roll back) a transaction?

To cancel everything it did and restore the data as if the transaction never happened.

What does "concurrent" actually promise?

Only that the steps overlap in time (interleave); not that they run on separate CPUs.

Name the three steps of the cycle where clashes are born.

Read, Modify, Write.

What is a dirty read?

Reading a value another transaction wrote but has not yet committed — if that transaction aborts, you read a value that never officially existed.

What does "serializable" require?

The overlapping result must match some one-at-a-time (serial) ordering.

Difference between an S-lock and an X-lock?

S = shared read (many allowed); X = exclusive write (only one).

What does a version number let you ask at commit time?

"Has this row changed since I read it?" (7 vs 8).

What does the acronym MVCC stand for?

Multi-Version Concurrency Control — keeping several versions of each row so readers see a stable snapshot.

In the cost model, what is

p

The probability a single transaction conflicts, a number between 0 and 1.

Why does the base execution cost

B

not appear in the comparison?

Both paths pay it, so it cancels; we compare only the extra overhead

L

R \cdot \frac{p}{1 - p}

Why does optimistic cost use

\frac{p}{1 - p}

It is the expected number of retries when each retry may itself clash (geometric series).

What happens to optimistic control at

p = 1

It is infeasible — retries never stop (livelock); you must go pessimistic.

State the break-even rule in words.

Go optimistic when the conflict rate

p

is below

p^{*} = \frac{L}{L + R}

Why do single-row version checks miss write skew?

The two transactions write to different rows, so no single row's version changes for both.