Weak Isolation Levels

Weak isolation levels are designed to provide a balance between consistency and performance. They allow certain concurrency anomalies to occur in exchange for improved throughput and reduced latency. In this section, we will explore the different types of weak isolation levels, their characteristics, and the trade-offs they offer.

Read Committed

Read Committed is a popular isolation level that prevents dirty reads but allows non-repeatable reads and phantom reads. It uses a combination of locking and Multi-Version Concurrency Control (MVCC) to achieve this balance.

MVCC Visibility Check

The MVCC visibility check is a crucial component of Read Committed. It determines which version of a data item is visible to a transaction based on the transaction’s start timestamp and the version’s creation and expiration timestamps.

def read_committed_mvcc_visibility_check(
    row_versions: list[tuple[int, int, any]],  # list of (xmin, xmax, value)
    transaction_start_ts: int
) -> any | None:
    """
    Modern Python 3.11+ example of MVCC visibility logic for a Read Committed query.
    Each query uses its own start timestamp (statement-level snapshot).
    Returns the most recent version visible to this snapshot.
    """
    # Iterate from newest to oldest version (assumes list is sorted by creation)
    for xmin, xmax, value in reversed(row_versions):
        # Version is visible if:
        # 1. It was created by a transaction that committed BEFORE this query started (xmin < transaction_start_ts)
        # 2. It is NOT marked as deleted by a transaction that committed BEFORE this query started.
        #    (xmax is NULL OR xmax >= transaction_start_ts means still valid for this snapshot)
        if xmin < transaction_start_ts and (xmax is None or xmax >= transaction_start_ts):
            return value
    return None  # No visible version

Snapshot Isolation

Snapshot Isolation is another weak isolation level that provides a transaction-level snapshot. It prevents dirty reads and non-repeatable reads but allows phantom reads.

Snapshot Isolation Transaction Mechanics

The following Python class illustrates the transaction mechanics of Snapshot Isolation:

class SnapshotIsolationTransaction:
    def __init__(self, tid: int, start_ts: int):
        self.tid = tid
        self.start_ts = start_ts  # Single snapshot timestamp for whole transaction
        self.local_writes: dict[str, any] = {}  # Key -> new value
        self.read_set: set[str] = set()
    
    def read(self, key: str, version_map: dict[str, list]) -> any:
        """Read consistent with snapshot. Record read for conflict detection."""
        self.read_set.add(key)
        # Find latest version committed before self.start_ts
        for xmin, xmax, value in reversed(version_map.get(key, [])):
            if xmin < self.start_ts and (xmax is None or xmax >= self.start_ts):
                return value
        return None
    
    def write(self, key: str, new_value: any):
        """Buffer write locally until commit."""
        self.local_writes[key] = new_value
    
    def commit(self, commit_ts: int, conflict_tracker: dict[str, int]) -> bool:
        """Attempt commit with First-Committer-Wins rule."""
        # Check for write-write conflicts
        for key in self.local_writes:
            last_writer = conflict_tracker.get(key)
            if last_writer is not None and last_writer > self.start_ts:
                # Another transaction wrote and committed to this key after our snapshot began
                return False  # Abort
        # No conflicts, apply writes and update conflict tracker
        for key in self.local_writes:
            conflict_tracker[key] = commit_ts
        return True

The First-Committer-Wins rule ensures that only the first transaction to commit its writes succeeds when there is a write-write conflict on the same key, thereby preventing lost updates. However, it does not prevent write skew, as conflicts are only detected on overlapping write sets—not on overlapping read sets that later lead to logically inconsistent writes on disjoint keys.

Transaction Anomalies

The following table summarizes the transaction anomalies allowed or prevented by each isolation level:

Isolation Level	Dirty Reads	Non-repeatable Reads	Phantom Reads	Write Skew	Common Implementation Mechanism
Read Uncommitted	Allowed	Allowed	Allowed	Allowed	No locks for reads.
Read Committed	Prevented	Allowed	Allowed	Allowed	Locking (short read locks) or MVCC (statement snapshot).
Repeatable Read (ANSI)	Prevented	Prevented	Allowed	Allowed	Locking (hold read locks) or MVCC (snapshot with key-range locks for phantoms in some DBs).
Snapshot Isolation (not ANSI)	Prevented	Prevented	Allowed (predicate phantoms)	Allowed	MVCC (transaction snapshot) + First-Committer-Wins on writes.
Serializable	Prevented	Prevented	Prevented	Prevented	Strict 2PL or Serializable Snapshot Isolation (SSI).

For Read Committed, the choice between locking and MVCC represents a fundamental tradeoff: locking avoids storage amplification but introduces write amplification due to lock contention; MVCC avoids read-write contention by maintaining multiple versions, but at the cost of increased storage overhead.

Phantom Reads

A phantom read occurs when a transaction reads data that was inserted by another transaction after the first transaction began. The following diagram illustrates the sequence of events that leads to a phantom read under Read Committed isolation:

Diagram: Phantom Read Timeline under Read Committed

Time	Transaction T1 (Read Committed)	Transaction T2 (Committed)
t1	BEGIN;
t2	SELECT * FROM users WHERE age > 30; (Returns rows A, B)
t3		BEGIN; INSERT INTO users (age=35); COMMIT; (Inserts row C)
t4	SELECT * FROM users WHERE age > 30; (Returns rows A, B, C)
t5	COMMIT;

Description: T1 sees a new row (C) in its second read that wasn’t visible in the first, because each SELECT sees a snapshot as of its start. The insert by T2 committed between the two snapshots, causing a phantom read.

Write Skew

Write skew is a concurrency anomaly that occurs when two transactions concurrently read overlapping data, make logically conflicting updates based on what they read, and both commit. The following sequence diagram illustrates the write skew anomaly under Snapshot Isolation:

Diagram: Write Skew under Snapshot Isolation

Initial State: Table ‘doctors_on_call’ has two rows: (id=1, on_call=true), (id=2, on_call=true). Invariant: At least one doctor must be on call.

Time	Transaction T1 (Snapshot Start=ts1)	Transaction T2 (Snapshot Start=ts1)	Global State
ts1	BEGIN; (Sees both doctors on call)	BEGIN; (Sees both doctors on call)	Both on call.
…	SELECT * FROM doctors_on_call;	SELECT * FROM doctors_on_call;	…
…	(Sees row1=true, row2=true)	(Sees row1=true, row2=true)	…
…	UPDATE doctors_on_call SET on_call=false WHERE id=1;	UPDATE doctors_on_call SET on_call=false WHERE id=2;	…
…	(Buffered write)	(Buffered write)	…
…	COMMIT (ts_commit1); (Succeeds, no conflict on row2)	COMMIT (ts_commit2); (Succeeds, no conflict on row1)	Both updates applied.
Final State: (id=1, on_call=false), (id=2, on_call=false). Invariant violated.

Description: Both transactions read the overlapping set (both rows), made decisions based on that snapshot, and updated disjoint rows. Snapshot Isolation’s First-Committer-Wins rule did not trigger a conflict because they wrote to different primary keys, allowing write skew.

Sources

Based on standard database literature and isolation level specifications.