Why architecture matters here
Transactional architecture matters because ACID guarantees hide considerable engineering. Understanding MVCC's implications, isolation level trade-offs, and WAL mechanics is what lets you avoid subtle bugs like phantom reads, lost updates, and unexpected serialization failures.
Cost is real — MVCC needs vacuum, WAL needs disk, locks add latency. Right isolation level is a hot spot for perf-vs-correctness trade.
Reliability comes from durable WAL + recovery. Crash recovery replays WAL forward from checkpoint, giving you the ACID D — durability under any failure.
The architecture: every layer explained
Walk the diagram top to bottom.
Client. Sends BEGIN, statements, COMMIT (or ROLLBACK). Transaction is a logical unit of work.
Transaction Manager. Tracks transaction state, locks held, snapshot visibility, WAL position.
Isolation Level. Per-transaction knob. READ COMMITTED (see committed at each statement), REPEATABLE READ (snapshot at first read), SERIALIZABLE (illusion of serial execution).
MVCC. Multi-Version Concurrency Control. Writes create new row versions; reads see a snapshot based on transaction start time. No blocking readers on writers.
Lock Manager. Row locks (touch a row), table locks (DDL), predicate/gap locks (Serializable). Detects deadlocks.
WAL / Redo Log. Every change written to WAL before disk. On commit, WAL flushed. Recovery replays.
Rollback Segment. Undo info kept until commit. On rollback, apply undo.
Vacuum / Autovacuum. Postgres-specific but similar in other MVCC DBs. Removes tombstoned rows (superseded versions) after they're no longer visible to any active transaction.
2PC. Two-phase commit for distributed transactions. Prepare + commit across participants coordinated by a coordinator.
Recovery. On restart, replay WAL forward from last checkpoint, applying committed changes; roll back uncommitted.
End-to-end transaction flow
Trace a transaction. Client BEGIN. Isolation = REPEATABLE READ.
Statement 1: SELECT balance FROM accounts WHERE id=42. Reader sees the row version visible at snapshot start. Returns $500.
Concurrent transaction updates the same row. New version written. Original still visible to our snapshot.
Statement 2: SELECT balance FROM accounts WHERE id=42 again. Our snapshot unchanged; still sees $500.
Statement 3: UPDATE accounts SET balance = balance + 100 WHERE id=42. Row lock acquired. Concurrent update means WAL conflict — depends on isolation.
COMMIT: WAL flushed to disk. Transaction visible to all readers with later snapshots.
Under Serializable: predicate locks or SSI (Serializable Snapshot Isolation) detect anomalies; may return "could not serialize" and require retry.
Crash mid-transaction: on restart, WAL replayed. Committed transactions applied; ours (uncommitted) rolled back via undo.
Old versions accumulate. Autovacuum runs, removes versions no longer visible to any snapshot. Reclaims space.