ACID & Database Isolation Levels Explained (with Anomalies)
ACID made simple — Atomicity, Consistency, Isolation, Durability — plus the four isolation levels, the anomalies they prevent (dirty, non-repeatable, phantom reads), and how MVCC softens the trade-off.
🔵 Fundamentals · Fresher-friendly → 🔴 goes deep
You've probably heard databases described as "ACID compliant" and nodded along. But ACID is the exact set of promises that lets you move money, book seats, and place orders without corrupting data — and isolation levels are the dial that trades some of those promises for speed. This is bread-and-butter database knowledge that comes up constantly in interviews. Let's make every letter click, then go deep on the part most people fumble: isolation.
What's a transaction?
A transaction is a group of database operations treated as one unit. The classic example: transfer $100 from A to B = subtract from A and add to B. Those two must happen together — never one without the other. ACID is the set of promises that makes that safe.
ACID, letter by letter
- A — Atomicity: all-or-nothing. Either every operation in the transaction happens, or none does. If the "add to B" step fails, the "subtract from A" is undone (rolled back). Money never vanishes.
- C — Consistency: a transaction moves the database from one valid state to another, never breaking its rules (constraints, foreign keys). Invalid data can't be committed.
- I — Isolation: concurrent transactions don't step on each other; each behaves as if it ran alone. This is the subtle one — more below.
- D — Durability: once committed, it's permanent — even if the server loses power a second later, the data survives (it's on disk / in a log).
Why isolation is hard
🟠Atomicity, Consistency, and Durability are fairly intuitive. Isolation is where the real depth lives, because databases run many transactions at the same time for performance. If two transactions touch the same data simultaneously without care, you get weird anomalies. Perfect isolation (as if everything ran one-at-a-time) is safest but slowest — so databases offer levels of isolation, each preventing more anomalies at the cost of some concurrency.
The three anomalies to know
🔴 These are the bugs isolation levels exist to stop — name them and you sound senior:
| Anomaly | What happens |
|---|---|
| Dirty read | You read data another transaction wrote but hasn't committed — and it might roll back, so you read something that never truly existed. |
| Non-repeatable read | You read a row twice in one transaction and get different values, because another transaction changed it in between. |
| Phantom read | You run the same query twice and new rows appear (or vanish), because another transaction inserted/deleted matching rows. |
The four isolation levels
🔴 Each level blocks more anomalies. Going down the list = safer but slower:
- Read Uncommitted: almost no protection; allows dirty reads. Rarely used.
- Read Committed: you only see committed data (no dirty reads). PostgreSQL's default.
- Repeatable Read: a row you read won't change if you read it again. MySQL/InnoDB's default.
- Serializable: the strictest — transactions behave as if run one after another, blocking every anomaly. Safest, but the most locking/contention.
The trade-off, and how modern DBs cheat it
🔴 Higher isolation = fewer anomalies but more locking, more waiting, lower throughput. So you pick the lowest level that's still correct for your use case — Read Committed is fine for most apps; a money transfer might warrant Serializable.
The clever part: modern databases (Postgres, MySQL/InnoDB, Oracle) use MVCC — Multi-Version Concurrency Control — to get strong isolation without readers blocking writers. Instead of locking a row for reading, the DB keeps multiple versions and shows each transaction a consistent snapshot. Readers never block writers and vice-versa, so you get high isolation and good concurrency. Dropping "MVCC" into an answer shows you understand how the trade-off is actually softened in practice.
The interview-ready summary
"ACID = Atomicity (all-or-nothing), Consistency (stays valid), Isolation (concurrent transactions don't interfere), Durability (survives crashes). Isolation is a dial with four levels — Read Uncommitted, Read Committed, Repeatable Read, Serializable — each blocking more anomalies (dirty, non-repeatable, phantom reads) at the cost of concurrency. I'd use the lowest level that's still correct — Read Committed for most things, Serializable for money — and rely on MVCC, which most databases use to give strong isolation without readers blocking writers." That covers the letters, the levels, the anomalies, and the real-world mechanism."
What to read next
- Distributed Transactions & Saga — what happens when ACID can't span services
- SQL vs NoSQL — who offers ACID and who trades it away
- Consistency Models — the distributed cousin of isolation
- ← The complete System Design guide (hub)
← Reverse proxy vs load balancer · WebSockets, long polling & SSE →
