Transactions and Isolation Levels: ACID, MVCC, and Anomalies
Isolation levels are a contract with the storage engine, and most applications are written against the wrong paragraph. We start with what a transaction actually is in a database — the log, the lock, the version chain — then define each isolation level by the anomalies it permits: dirty reads, non-repeatable reads, phantoms, write skew. We demonstrate each anomaly with a runnable scenario, then show how MVCC and row versioning implement the levels in PostgreSQL and MySQL. The payoff is practical: knowing which level your workload needs, why read committed is the default, and what serializable really costs in throughput.
Topics covered:
- ACID and the mechanics behind it
- Anomalies: dirty read, non-repeatable read, phantom
- MVCC and version chains in practice
- Level-by-level behavior and the serializable trade-off
Related articles
ACID Isolation Levels Explained
From read uncommitted to serializable — understanding phantom reads, dirty reads, and how databases enforce transaction isolation.
The Anatomy of a Database Transaction
ACID is a slogan; the write-ahead log is the mechanism. How a transaction commits, why the WAL exists, and what isolation levels actually change.
MVCC and the Multiversion House of Cards
Snapshot isolation, visibility rules, and vacuum — how databases maintain consistency without locking readers.
More in Databases
B-Trees: The Shape of Databases
Why every major database is a tree shaped like a disk page — and how to read your index's health from its shape.
WatchPostgres Internals Tour: Processes, Buffer Pool, WAL, and MVCC
A guided tour of PostgreSQL internals — process model, buffer manager, WAL, and MVCC — the mechanisms that make Postgres behave the way it does.
DetailsSQL Joins and Execution Plans: Nested Loop, Hash, and Merge
How the database executes joins — nested loop, hash join, and merge join — and how to read execution plans to see which strategy your query gets.
DetailsConnection Pools, Database-Side: What a Connection Really Costs
What actually happens to your database when connections pile up — the connection lifecycle, pool sizing math, and why max_connections is not a tuning knob.
DetailsStorage Engines: LSM-Trees vs. B-Trees
LSM-trees vs. B-trees — how each storage engine writes, compacts, and reads, and what that means for write and read amplification in your workload.
DetailsSharding Strategies: Partition Keys, Distribution, and Rebalancing
How sharding actually works — partition keys, data placement, cross-shard queries, and the operational reality of splitting one database into many.
DetailsReplication Explained: WAL Shipping, Lag, and Failover
How database replication actually works — the transaction log, the lag, and the failure modes of synchronous and asynchronous replication in production.
DetailsDatabase Indexes Visualized: B-Trees, Covering Indexes, and Planner Decisions
How database indexes actually work — B-trees, hash indexes, covering indexes, and when the planner will or won't use the index you made.
DetailsQuery Optimizer Internals: From Parse Tree to Execution Plan
What happens inside a query optimizer — parse, rewrite, join ordering, cost models, and how the planner decides the plan your query gets.
DetailsDepth, delivered weekly
One technical dispatch a week — articles and episode notes before they go public.
One technical dispatch per week. No noise.