A frame for placing any mechanism
You may find the mechanisms of this topic listed in three groups: concurrency control schemes, transaction mechanics and distributed transactions. That is a good table of contents, but a poor way to understand a new mechanism you have never seen. A better frame is two independent decisions plus one prior fork.
The prior fork: one version of each item, or many?
- A single-versioned system has exactly one copy of each item. A reader and a writer must therefore contend for it, because the one copy can be in only one state at a time.
- Multiversioning (MVCC) keeps old versions. A reader reads the version that was current when it started; a writer adds a new version. The entire read-write conflict class disappears: readers never block writers and writers never block readers.
Multiversioning buys this by moving cost into space and into a garbage collector (a background process that deletes versions nobody can see any more). That is an instance of a pattern you see all over storage: immutable (never-changed-in-place) storage grows an indirection layer plus a collector. Flash drives have a translation layer and cleaning; LSM-trees (a write-optimized storage structure) have compaction; table formats on object storage have snapshot expiry; and now MVCC has version chains plus vacuum. The same design appears again and again, with the same failure mode each time: the collector falls behind and the system degrades in a way that looks nothing like what caused it.
One more warning. Write-write conflict survives multiversioning untouched. If two transactions update the same row, one must wait or abort. "MVCC means writers do not block" is half-true folklore.
Decision one: when is conflict detected, relative to the work?
| Timing | Mechanism | What it pays in |
|---|---|---|
| Before execution (order first) | Deterministic execution, timestamp ordering | Loss of flexibility: you must know the read and write sets up front |
| During execution (block) | Two-phase locking (2PL), strict 2PL | Latency, and deadlock as a new problem |
| At commit (validate) | Optimistic concurrency control (OCC), SSI, CAS / conditional write, manifest commit | Wasted work: the whole transaction is thrown away |
| After commit (repair) | Sagas, compensating transactions | Semantics: the repair is application code and may not exist |
(Locking and Multiversion and optimistic schemes explain each row. Note that when a conflict is detected and what is done about it turn out to be independent choices; see "How each scheme relates to the graph" in Locking.)
Decision two: on what unit is conflict detected?
The ladder is: row → page → predicate or range → partition → file or manifest → table.
This is granularity mismatch generates amplification in a new setting.
- Coarse units produce false conflicts: transactions that never touched the same row abort anyway, because they touched the same page or file.
- Fine units produce metadata: a lock for every row means the lock manager (the part of the database that remembers who holds which lock) becomes a contended resource itself. A shared resource used by many users behaves like a queue whose delay grows as 1/(1 − ρ), where ρ is how busy it is (80% busy means a delay of 1/0.2 = 5 times the base time).
Lock escalation (replacing many fine locks by one coarse lock) is the same escape as choosing a bigger block size: give up precision when the cost of tracking exceeds the cost of false conflicts.
Every mechanism fits in a cell of this grid. Even the data-lake mechanisms are not exceptions: an Iceberg or Delta table commit is validate-at-commit detection at file-or-partition granularity. That is why its abort rate is governed by how much the partitions of different writers overlap, and why two writers writing to the same partition are the thing that breaks it (Transaction mechanics).