Optimistic vs pessimistic concurrency control

WHAT is concurrency control?

The enemy we are defending against: lost updates, dirty reads, write skew — anomalies that appear when concurrent transactions interleave. Note these differ in shape: a lost update happens when two transactions read-modify-write the same row; write skew is subtler — two transactions ==read overlapping data and then write to different rows==, each individually valid but jointly violating an invariant (e.g. both doctors read "2 on-call" and each goes off-shift, leaving zero). So our defense must cover more than single-row contention.

WHY two different strategies?

The whole decision comes down to one number: the probability a conflict actually occurs.

We can make this precise. Let:

$p$ = probability a given transaction conflicts,
$L$ = overhead of locking (acquire + hold + release) per transaction,
$R$ = cost of re-doing a transaction after a failed optimistic validation.

Formula Expected cost per transaction (derived from first principles)

Pessimistic: you always pay the lock overhead, regardless of conflict: $E[\text{pess}] = L$ Optimistic: you pay the lock overhead never, but with probability $p$ you must retry (and that retry may itself conflict, giving a geometric series): $E[\text{opt}] = R\cdot\big(p + p^2 + p^3 + \dots\big) = R\cdot\frac{p}{1-p}$

Why the geometric series? A retry succeeds only if it doesn't conflict (prob $1-p$ ), so the number of retries is geometric with mean $\dfrac{p}{1-p}$ .

Optimistic wins when $E[\text{opt}] < E[\text{pess}]$ : $R\cdot\frac{p}{1-p} < L \quad\Longrightarrow\quad p < \frac{L}{L+R}$

This single inequality is the 80/20 of the whole topic: if conflicts are rare relative to the lock-vs-retry cost ratio, go optimistic.

Figure — Optimistic vs pessimistic concurrency control

HOW each one works

Pessimistic Concurrency Control (PCC)

Implemented with locks. Most common scheme is Two-Phase Locking (2PL):

Shared (S) lock for reads, Exclusive (X) lock for writes.
Cost: risk of deadlock (T1 holds A waits B, T2 holds B waits A) → needs detection/timeout.
SELECT ... FOR UPDATE in SQL is explicit pessimistic locking.
Write skew needs predicate/range locking (or serializable isolation), not just row locks, because the conflicting writes touch different rows.

Optimistic Concurrency Control (OCC)

Three phases, no locks during work:

The classic implementation is version checking:

-- read
SELECT balance, version FROM accounts WHERE id = 1;  -- balance=100, version=7
-- ... compute new balance = 100 - 30 = 70 ...
-- write WITH validation baked into WHERE:
UPDATE accounts SET balance = 70, version = 8
WHERE id = 1 AND version = 7;     -- affects 0 rows if someone bumped version → retry!

Worked Examples

Common Mistakes (Steel-manned)

Recall Feynman: explain to a 12-year-old

Imagine one library book everyone wants. Pessimistic way: whoever takes it first locks it in a box — everyone else waits in line. Nobody ever fights over it, but the line can be slow, and two careful people might wait on each other's boxes forever (that's a deadlock). Optimistic way: we photocopy the book so everyone reads at once. When you want to give back your edited copy, the librarian checks: "did anyone else change this page while you had it?" If yes, sorry — redo your edits on the new page. Great when people rarely edit the same page, terrible when everyone scribbles on page 1.

Flashcards

What is the core assumption of pessimistic concurrency control?

That conflicts are likely, so it locks data before accessing it to prevent them.

What is the core assumption of optimistic concurrency control?

That conflicts are rare, so transactions run freely and conflicts are only checked at commit time.

What are the three phases of OCC?

Read (work on private copy + record versions), Validate (check nothing read has changed), Write (commit or abort+retry).

In OCC, how does the validation usually get implemented in SQL?

A conditional UPDATE ... WHERE version = <old>; if it affects 0 rows, a conflict occurred and you retry.

Derive when optimistic beats pessimistic.

When

R\frac{p}{1-p} < L

, i.e. conflict probability

p < \frac{L}{L+R}

Why does the expected optimistic cost have a

\frac{p}{1-p}

factor?

Retries form a geometric series (each retry succeeds with prob

1-p

), giving mean retries

\frac{p}{1-p}

What guarantees serializability in pessimistic 2PL?

The two-phase rule: a growing phase (only acquire locks) then a shrinking phase (only release), no acquire after any release.

Which scheme can deadlock, and which aborts instead?

Pessimistic 2PL can deadlock (needs detection/timeout); OCC never deadlocks — it aborts and retries.

Is MVCC the same as optimistic concurrency control?

No. MVCC is a storage technique (multiple versions for snapshot reads) that can underpin either optimistic or pessimistic control.

For a flash-sale hot row with p→1, which control is appropriate and why?

Pessimistic (or queue/atomic), because OCC's

\frac{p}{1-p}

retry cost explodes into retry storms/livelock.

How does write skew differ from a lost update?

A lost update is two read-modify-writes on the same row; write skew is two transactions reading overlapping data then writing to different rows, jointly breaking an invariant.

What is needed to prevent write skew?

Predicate/range locking or serializable isolation — plain row locks and single-row version checks don't catch it because the writes hit different rows.

Connections

ACID properties — isolation (I) is what concurrency control enforces.
Two-Phase Locking (2PL) — the canonical pessimistic implementation.
MVCC — version storage that powers snapshot isolation.
Serializability and Isolation Levels — the correctness target.
Write Skew and Phantoms — anomaly that needs predicate locking / serializable.
Deadlocks — a hazard unique to pessimistic locking.
Compare-and-Swap (CAS) — hardware-level analogue of optimistic version checking.

Concept Map

Hinglish (regional understanding)

Intuition Hinglish mein samjho

Dekho, concurrency control ka matlab hai jab do transactions ek hi data ko same time pe touch karte hain, to database kaise ensure kare ki result waisa hi aaye jaise woh ek-ek karke chale ho. Iske do philosophies hain. Pessimistic bolta hai "bhai conflict hoga hi hoga, isliye pehle se lock laga do" — jaise ek hi pen ho aur baaki sab line mein wait karein. Optimistic bolta hai "conflict rarely hota hai, to aaram se kaam karo aur commit ke time check kar lena ki kisi ne beech mein change to nahi kiya".

Optimistic ka asli jugaad ek simple SQL line hai: UPDATE ... WHERE version = 7. Jab tum data padhte ho to version note kar lete ho (maan lo 7). Agar tumhare commit se pehle kisi aur ne update kar diya, to woh version 8 kar dega, aur tumhara WHERE version = 7 0 rows ko match karega — matlab conflict ho gaya, ab tumhe retry karna padega. Pessimistic mein deadlock ka risk hota hai (T1 A pakde B chahe, T2 B pakde A chahe), lekin optimistic kabhi deadlock nahi karta, woh bas abort karke dobara try karta hai. Ek important baat: write skew wala anomaly thoda alag hai — ismein do transactions overlapping data padhte hain aur fir alag-alag rows pe likhte hain, to simple row-lock ya single-row version check kaam nahi aata; uske liye predicate locking ya serializable isolation chahiye.

Decision ka pura 80/20 ek hi formula mein hai: optimistic tab better hai jab conflict probability $p < \frac{L}{L+R}$ , jahan $L$ lock ka cost aur $R$ retry ka cost hai. Simple words mein — kam contention (jaise reporting, read-heavy apps) mein optimistic, aur high contention (jaise flash sale ka ek hi hot row) mein pessimistic use karo. Agar sab log ek hi row pe ladd rahe hain to optimistic retry-storm mein phass jayega, isliye wahan locking better hai. Bas yaad rakho: "P = Prevent, O = Oops-then-retry".

Optimistic vs pessimistic concurrency control

WHAT is concurrency control?

WHY two different strategies?

HOW each one works

Pessimistic Concurrency Control (PCC)

Optimistic Concurrency Control (OCC)

Worked Examples

Common Mistakes (Steel-manned)

Flashcards

Connections

Concept Map

Hinglish (regional understanding)

Go deeper — visual, from zero

Test yourself — Databases

Connections