Optimistic concurrency control (OCC)

Below which conflict probability does optimism win, and how does it fail above it?

The constraint is economic

If conflicts are rare, a lock manager is a cost paid on every operation to protect against something that almost never happens. Put numbers on it.

• Pessimistic (locking) cost ≈ k × c_lock per transaction. With k = 10 items and c_lock ≈ 150 ns: 10 × 150 ns = 1.5 µs.
• Optimistic cost ≈ p_conflict × (the transaction's entire work), because a conflicting transaction must redo everything. For 100 µs of work, the two costs are equal when p × 100 µs = 1.5 µs, so p ≈ 1.5 / 100 = 1.5%.

Three phases

1. Read into a private workspace (nothing is visible to others yet).
2. Validate: did anything I read change since I read it?
3. Write: make the changes visible.

There are two directions for validation:

• Backward validation checks my read set against the write sets of transactions that committed since I started.
• Forward validation checks my write set against the read sets of transactions that are still running. Its useful property: you can choose to abort the other transaction. That is the hook by which priority and starvation avoidance are implemented.

The structural cost: validation is itself a critical section

Check-then-commit must be atomic with respect to other validations. Otherwise two transactions could each validate against a state that neither ends up in. So the scheme that abolished locking has created one global lock, and Amdahl's Law (the speedup limit set by the serial part of a job) now sets its ceiling at the reciprocal of that critical section's length.

Modern designs spend most of their engineering here. Silo (a research in-memory database) replaces the global section with per-record transaction IDs plus epoch-based group commit. This is a fixed per-commit cost deriving a natural batch size, and the batch is an epoch (a short period of time, for example 40 ms, in which commits are grouped).

How OCC fails

The failure mode is different from 2PL's.

• 2PL degrades into waiting: utilization drops, queues grow, and the system is visibly stuck.
• OCC degrades into burning: every core is at 100%, goodput (useful completed work) is near zero, because everyone aborts everyone and nobody finishes.

An aborted transaction also used a concurrency slot for its whole duration, so by Little's Law it consumed real capacity to produce nothing. If you diagnose this in production, the tell is high CPU with a falling commit rate. The metric you need is goodput, not throughput.

OCC starves the large

A long transaction's conflict window is proportional to its length, so under contention it will essentially never commit, while 2PL's first-come-first-served wait queues starve nobody. The standard fix is to fall back to pessimistic execution after N aborts. That is an admission that the scheme has one workload it likes and needs an escape for the other.

Optimism trades a certain small cost on every transaction for an uncertain total loss on a few; the crossover is the conflict probability, and past it the failure is silent because the CPUs stay busy.

A code sketch: optimistic update with a version column

Application code can use OCC even on a database that locks. Keep a version number in the row and make the update conditional on it:

-- 1. Read, remember the version.
SELECT balance, version FROM wallets WHERE owner = 'anna';   -- balance=100, version=7

-- 2. Compute in the application, then write only if nobody changed the row.
UPDATE wallets
   SET balance = 70, version = version + 1
 WHERE owner = 'anna' AND version = 7;
-- If this reports 0 rows updated, someone else got there first:
-- re-read and try again (the retry must include the read).