Why does Snapshot Isolation (Repeatable Read) permit Write Skew (A5B) while preventing Dirty Reads and Non-Repeatable Reads?

Snapshot Isolation guarantees that a transaction reads from an immutable snapshot of committed data taken at transaction start (or statement start in Read Committed). Under Snapshot Isolation, write-write conflicts on the same physical row are detected (first-committer-wins rule: if T1 and T2 both attempt to update row X, the second is blocked or aborted). However, Write Skew (Berenson et al. anomaly A5B) occurs when two concurrent transactions read overlapping datasets, make disjoint updates to different rows or predicates to preserve an invariant (e.g. "at least one doctor on call: count(on_call) >= 1"), and both commit. Because T1 updates Doctor A and T2 updates Doctor B, their write sets do not physically intersect, so Snapshot Isolation detects zero write-write conflict and commits both, leaving zero doctors on call.

How does PostgreSQL implement Multi-Version Concurrency Control (MVCC) without in-place updates or undo logs?

PostgreSQL uses an append-only heap storage engine. Every tuple in a table contains two 32-bit transaction IDs in its header: t_xmin (the transaction that inserted the row) and t_xmax (the transaction that updated or deleted the row, or 0 if active). When a row is updated, PostgreSQL does not overwrite the old row in-place; instead, it writes a completely new tuple version to a heap page, updates t_xmax of the old tuple to the current transaction ID, and sets t_ctid on the old tuple to point to the new tuple. Visibility is evaluated against the transaction snapshot (xmin:xmax:xip_list) and commit status bits (HEAP_XMIN_COMMITTED, HEAP_XMAX_INVALID). Dead tuple versions remain in heap pages until reclaimed by background VACUUM or HOT (Heap-Only Tuples) optimization.

How does MySQL InnoDB MVCC differ fundamentally from PostgreSQL heap tuple versioning?

Unlike PostgreSQL append-only heaps, MySQL InnoDB performs updates in-place within its clustered index (Primary Key B+Tree). Historical row versions are not stored in the main data pages. Instead, each InnoDB row contains a 6-byte DB_TRX_ID (last modifying transaction ID) and a 7-byte DB_ROLL_PTR (rollback pointer). The rollback pointer references an undo log record located in the rollback segment. When a transaction requires a historical snapshot under Repeatable Read, InnoDB reads the current row from the clustered index and traverses the undo log chain backwards in time, reconstructing earlier column values on-the-fly until it finds a version visible to its ReadView. Background purge threads reclaim undo log extents once older ReadViews terminate.

How does Serializable Snapshot Isolation (SSI) detect non-serializable interleavings without blocking readers or writers?

Developed by Cahill, Röhm, and Fekete (2008) and implemented in PostgreSQL 9.1+, SSI relies on the mathematical proof that every non-serializable execution under Snapshot Isolation contains a cycle in the Direct Serialization Graph (DSG) with two consecutive read-write anti-dependency edges (T_prev -> T_pivot -> T_next). An rw-antidependency occurs when T1 reads a version of an item and T2 produces a newer version of that item. SSI introduces lightweight, non-blocking SIREAD lock flags on tuples, pages, and relations. If a transaction simultaneously exhibits an incoming rw-antidependency and an outgoing rw-antidependency (acting as a pivot), SSI flags a danger structure and aborts the latest committer with SQLSTATE 40001 (serialization_failure), guaranteeing true serializability with zero read/write blocking.

What is the performance difference between Two-Phase Locking (2PL) and Serializable Snapshot Isolation (SSI)?

Two-Phase Locking (2PL, specifically Strict SS2PL used by traditional databases) enforces serializability through mutual exclusion: shared locks (S) block exclusive locks (X), meaning readers block writers and writers block readers. In high-concurrency read-heavy environments, 2PL introduces severe lock contention, lock convoying, and frequent deadlocks. In contrast, SSI allows readers to never block writers, and writers to never block readers. All transactions proceed concurrently using MVCC snapshots. Contention is resolved post-hoc by aborting only the specific transactions that form serialization cycles (typically < 1% of transactions). SSI achieves 2x to 5x higher throughput than 2PL on read-heavy, mixed-write workloads.

How should production application code handle PostgreSQL error 40001 (serialization_failure)?

Under PostgreSQL SERIALIZABLE isolation, error 40001 (could not serialize access due to read/write dependencies among transactions) is not a critical system crash; it is an expected, transient concurrency signal indicating that the transaction was chosen as the victim to prevent an anomaly. Applications using SERIALIZABLE isolation MUST wrap transaction execution in an idempotent retry loop. The retry loop should inspect the SQLSTATE code, back off with randomized exponential jitter (to avoid thundering herd retry collisions), and replay the entire transaction from BEGIN. Transactions that cannot be safely retried (e.g. those performing un-rollbackable external API calls or credit card charges) must use explicit locking (SELECT FOR UPDATE) or two-phase commit patterns instead.

Verified Specification | Standardized Formulas | Instant Precision

Secure & Private (Zero Data Retention) Free Access • No Sign-Up

ANSI SQL-92 / Berenson 1995 PostgreSQL SSI / Cahill 2008 MySQL InnoDB MVCC Zero External Dependencies

Database Concurrency Control: MVCC, Write Skew & Serializable Snapshot Isolation Studio

An architectural deep-dive and real-time simulator for database transaction isolation. Dissect Multi-Version Concurrency Control (PostgreSQL heap tuple xmin/xmax vs MySQL InnoDB undo log roll pointers), simulate Write Skew (A5B) anomalies under Snapshot Isolation vs Serializable Snapshot Isolation (SSI), evaluate Two-Phase Locking (2PL) contention, and synthesize production retry loops.

REPEATABLE READ

Active Isolation Level

VULNERABLE (A5B)

Write Skew Safety

Non-Blocking Readers

MVCC Read Semantics

Lightweight SIREAD

SSI Lock Footprint

SQLSTATE 40001

Serialization Failure Code

Interactive Transaction Schedule Interleaver & Anomaly Simulator

Step through concurrent transactions line-by-line. Observe how Snapshot Isolation (PostgreSQL / MySQL REPEATABLE READ) permits devastating business logic anomalies like Write Skew (A5B), while Serializable Snapshot Isolation (SSI) and Strict Two-Phase Locking (2PL) prevent them.

Concurrency Scenario & Business Invariant

Transaction Isolation Level & Concurrency Engine

Step 0 / 8

TRANSACTION T1 (Alice)

TRANSACTION T2 (Bob)

PERSISTENT DATABASE STATE

Engine Telemetry & Anomaly Analysis Log

Initialized interleaver. Click 'Step Next Operation' to begin trace...

MVCC Storage Architecture: PostgreSQL Append-Only Heap vs MySQL InnoDB Undo Logs

Relational databases implement Multi-Version Concurrency Control using two radically different storage topologies: PostgreSQL appends new tuple versions into heap pages and manages visibility with xmin/xmax metadata, while MySQL InnoDB mutates records in-place and reconstructs historical snapshots by traversing rollback pointers into undo log segments.

PostgreSQL Tuple Header Anatomy & Visibility Engine

Every row stored in a Postgres 8KB buffer page includes a 23-byte HeapTupleHeaderData. Adjust the active transaction snapshot slider below to inspect which row versions are rendered visible, invisible (uncommitted/aborted), or invisible (dead/superseded).

Active Snapshot xmin (Completed TX Floor)

All TX < xmin are committed and visible.

Active Snapshot xmax (Unstarted TX Ceiling)

All TX ≥ xmax are uncommitted and invisible.

Active In-Flight TX List (xip_list)

In-flight transactions (invisible to snapshot).

Physical Item Pointer	t_xmin (Creator)	t_xmax (Deleter/Updater)	t_infomask Status	Tuple Data	Visibility Result

VACUUM & Table Bloat: Notice that older versions (e.g. where t_xmax < oldest_xmin) remain physically on disk taking up 8KB page space until VACUUM marks the line pointers as dead. Failure of autovacuum to keep up with high-frequency updates causes severe table and index bloat.

Serializable Snapshot Isolation (SSI) & Serialization Graph Analysis

PostgreSQL Serializable Snapshot Isolation (SSI) dynamically tracks rw-antidependencies (SIREAD locks) across active transactions. When the Direct Serialization Graph (DSG) develops two consecutive rw-antidependency edges (a "dangerous structure"), the engine aborts the pivot transaction with SQLSTATE 40001.

Dangerous Structure Detected: T1 —(rw)→ T2 —(rw)→ T1. Transaction T2 is selected as the victim and aborted with ERROR: could not serialize access due to read/write dependencies among transactions (SQLSTATE 40001).

Dependency Edge Type	Formal Mathematical Definition	Physical Engine Meaning	Impact on Serialization Graph
ww (Write-Write)	`w1[x] < w2[x]`	T2 overwrote or updated a tuple version previously written by T1.	Must serialize T1 → T2. In standard MVCC, handled by first-committer-wins lock.
wr (Write-Read)	`w1[x] < r2[x]`	T2 read a committed tuple version originally written by T1.	Must serialize T1 → T2. Standard dependency edge; does not cause non-serializability alone.
rw (rw-Antidependency)	`r1[x] < w2[x]`	T1 read a tuple version that was subsequently superseded or deleted by T2.	CRITICAL: Indicates T1 should serialize before T2. Two consecutive rw-edges form a dangerous anomaly cycle!

Standard vs Modern Transaction Isolation & Anomaly Matrix

The original ANSI SQL-92 isolation definitions were heavily critiqued by Berenson et al. (1995) for omitting Snapshot Isolation and failing to formalize Write Skew (A5B) and Lost Updates (P4). Modern production databases implement isolation via MVCC rather than simple table locks.

Isolation Level	Dirty Read (P1)	Non-Repeatable Read (P2)	Phantom Read (P3)	Lost Update (P4)	Read Skew (A5A)	Write Skew (A5B)	Locking / Concurrency Mechanism
Read Uncommitted	Allowed	Allowed	Allowed	Allowed	Allowed	Allowed	Zero read locks. Readers read dirty uncommitted shared memory buffers. (Postgres treats as Read Committed).
Read Committed	Prevented	Allowed	Allowed	Allowed	Allowed	Allowed	Statement-level MVCC snapshot refreshed at the start of every single SQL query. Default for PostgreSQL.
Repeatable Read (Snapshot Isolation)	Prevented	Prevented	Prevented*	Prevented	Prevented	Allowed (A5B)	Transaction-level MVCC snapshot. In PostgreSQL, blocks phantoms for reads. In MySQL, uses Next-Key Locks. Vulnerable to Write Skew!
Serializable (PostgreSQL SSI)	Prevented	Prevented	Prevented	Prevented	Prevented	Prevented	Non-blocking SIREAD locks on tuples/pages/tables. Aborts dangerous rw-antidependency cycles with SQLSTATE 40001.
Serializable (Strict 2PL)	Prevented	Prevented	Prevented	Prevented	Prevented	Prevented	Shared (S) and Exclusive (X) locks held until COMMIT. Readers block writers, writers block readers. Zero anomalies, high deadlock risk.

Production Concurrency & Transaction Retry Blueprints

Implementing Serializable Snapshot Isolation (SSI) or explicit locking requires hardened application patterns: handling SQLSTATE 40001 retry loops with jitter, row-level locking ladders, and deadlock prevention.

Database Concurrency Control: MVCC, Write Skew & Serializable Snapshot Isolation Studio

Interactive Transaction Schedule Interleaver & Anomaly Simulator

TRANSACTION T1 (Alice)

TRANSACTION T2 (Bob)

PERSISTENT DATABASE STATE

MVCC Storage Architecture: PostgreSQL Append-Only Heap vs MySQL InnoDB Undo Logs

PostgreSQL Tuple Header Anatomy & Visibility Engine

MySQL InnoDB In-Place Clustered Index & Undo Log Traversal

Serializable Snapshot Isolation (SSI) & Serialization Graph Analysis

Standard vs Modern Transaction Isolation & Anomaly Matrix

Production Concurrency & Transaction Retry Blueprints

🔍 Companion Workbenches & Verification Tools

PostgreSQL Write-Ahead Log (WAL), Replication & CDC Architecture Studio

B-Tree, B+ Tree & Storage Engine Internals Studio

Linux io_uring & Asynchronous Zero-Copy I/O Architecture Studio

Linux PSI (Pressure Stall Information), cgroups v2 & systemd-oomd Architecture Studio

Why Smart Users Are Switching to This Free Toolkit

The Browser Feature Most People Never Use

Stop Paying for Software You Can Get Free

File Conversion Tricks That Save Hours Every Week

This Free Tool Replaced 5 Paid Apps for Our Team

What Your Browser Can Do Without Any Extensions