In the previous post, we understood isolation levels through the lens of their anomalies i.e., what goes wrong when transactions interleave too freely. But knowing what to prevent is only half the story. Databases must also decide how to prevent it.
That’s where concurrency control mechanisms come in. They are the machinery behind isolation, the algorithms that decide which conflicts to block, which to allow, and how tightly to enforce serializability.
However, not every database uses these mechanisms in their strictest form. A mechanism like MVCC can enforce Serializable Snapshot Isolation (SSI) when fully tightened, or Read Committed when relaxed. In short, the same tool can build both strong and weak isolation, depending on how it’s used.
Let’s see how each mechanism works in its strongest form and what happens when databases loosen it for performance.
1. Two-Phase Locking (2PL): Serializability Through Restraint
Two-Phase Locking (2PL) is the oldest and most intuitive concurrency control method. It enforces serializability by strictly ordering access through locks.
Each transaction operates in two phases:
| Phase | Action |
|---|---|
| Growing Phase | Acquires locks before reading or writing data. |
| Shrinking Phase | Releases all locks only after commit or abort. |
This simple rule to never release a lock before you’re done acquiring them, guarantees that no other transaction can slip between operations.
| Configuration | Anomalies Prevented | Still Possible | Concurrency |
|---|---|---|---|
| Strict 2PL (Serializable) | Dirty Read, Non-repeatable Read, Phantom Read, Write Skew | None | Low |
| Relaxed (Read Committed) | Dirty Read | Non-repeatable Read, Phantom Read | Medium |
Strict 2PL keeps the system perfectly consistent but heavily limits concurrency. Relaxed 2PL releases read locks early, trading safety for speed.
Weaker Variant - Read Committed in 2PL
How it’s done
Read locks are released right after a statement finishes instead of holding them until commit.
Consequence
Another transaction can modify the same row before this one finishes, causing non-repeatable or even dirty reads.
Example: Dirty Read
| Step | T1 (Reader) | T2 (Writer) |
|---|---|---|
| 1 | Reads balance = 100 (Acquires shared lock) |
|
| 2 | Releases read lock immediately | |
| 3 | - | Updates balance = 0 (Uncommitted) |
| 4 | Reads again -> Sees 0 |
|
| 5 | - | Rolls back |
T1 just read uncommitted data, a dirty read, because its lock was released before T2’s transaction committed.
2. Optimistic Concurrency Control (OCC): Serializability by Validation
Where 2PL relies on blocking, OCC bets on optimism. It assumes conflicts are rare, so transactions run freely and only check for problems at commit time.
| Phase | Action |
|---|---|
| Read Phase | Reads data and performs computations without locks. |
| Validation Phase | Before commit, checks whether any other transaction modified what it read. |
| Write Phase | If validation succeeds, applies changes; otherwise, aborts. |
| Configuration | Anomalies Prevented | Still Possible | Concurrency |
|---|---|---|---|
| Strict Validation (Serializable) | All | None | Medium |
| Partial Validation (Read Committed) | Dirty Read | Non-repeatable Read, Phantom Read | High |
OCC achieves serializability only if validation checks the full read set. When databases relax validation, they drift toward weaker levels like Read Committed or Repeatable Read, favouring throughput over correctness.
Weaker Variant - Read Committed in OCC
How it’s done
Skip full revalidation of earlier reads at commit. Only check that there’s no overlapping write–write conflict.
Consequence
A transaction may see different committed values at different times — a non-repeatable read.
Example: Non-repeatable Read
| Step | T1 | T2 |
|---|---|---|
| 1 | Reads price = 10 |
|
| 2 | - | Updates price = 20, Commits |
| 3 | Reads price again -> Now sees 20 |
Since OCC didn’t revalidate that the earlier read became stale, T1 observes two different committed values within one transaction, still consistent, just not stable.
3. Multi-Version Concurrency Control (MVCC): Snapshots for Everyone
MVCC trades locks for versions. Instead of blocking readers, it maintains multiple versions of each record, each tagged with a transaction timestamp. Every transaction reads from a snapshot of the database as it existed when it began.
| Step | Behaviour |
|---|---|
| Read | Returns the latest committed version as of the transaction’s start time. |
| Write | Creates a new version with the transaction’s commit timestamp. |
| Commit | Checks for concurrent write-write conflicts. |
| Configuration | Anomalies Prevented | Still Possible | Concurrency |
|---|---|---|---|
| Default (Snapshot Isolation) | Dirty Read, Non-repeatable Read, Phantom Read | Write Skew | Very High |
| Relaxed (Read Committed) | Dirty Read | Non-repeatable Read, Phantom Read | Maximum |
MVCC shines in read-heavy workloads, but serializability isn’t automatic, it needs dependency tracking (SSI) to detect cycles among snapshots. Without it, anomalies like write skew can slip through.
Weaker Variant - Read Committed in MVCC
How it’s done
Each statement runs on a fresh snapshot that includes all commits visible at that moment, rather than sharing a consistent snapshot for the whole transaction.
Consequence
Every statement sees only committed data — no dirty reads — but different statements may see different snapshots.
Example: Non-repeatable Read
| Step | T1 | T2 |
|---|---|---|
| 1 | Begins -> Snapshot sees x = 5 |
|
| 2 | Reads x = 5 |
|
| 3 | - | Updates x = 6, Commits |
| 4 | T1 executes another query -> new snapshot -> Reads x = 6 |
T1 never saw uncommitted data, but its “world” changed mid-transaction, hence Read Committed.
4. Serializable Snapshot Isolation (SSI): Serializability Without Locks
SSI builds on MVCC but adds dependency tracking to close that serializability gap. It monitors how transactions depend on one another:
• When T1 reads data that T2 later writes, a dependency edge T1 -> T2 is recorded.
• If these dependencies form a cycle, one transaction is aborted, ensuring a conflict-free serial order.
| Configuration | Anomalies Prevented | Still Possible | Concurrency |
|---|---|---|---|
| SSI (Serializable) | Dirty Read, Non-repeatable Read, Phantom Read, Write Skew | None | High |
SSI achieves true serializability without locking reads, retaining MVCC’s concurrency and OCC’s optimism. It’s one of the most practical ways to achieve strong isolation in modern systems.
Conclusion
Isolation levels describe what guarantees a database provides. Concurrency control mechanisms decide how it delivers them and how tightly it enforces those guarantees.
The same machinery can build many worlds, from strict serializability to relaxed read consistency. The art lies in how tightly you pull the rope around conflicts.