Isolation levels
Isolation — the 'I' in ACID — isn't all-or-nothing; it's a DIAL. Turn it all the way up and every transaction acts as if it's completely alone, never glimpsing anyone else's half-done work — perfectly safe, but slower because transactions have to wait for each other. Turn it down and they run faster by peeking at each other — but now three sneaky read problems can bite you. This lesson names those three problems, then shows the four standard settings of the dial, from loosest to strictest.
Problem 1 — the dirty read: reading undo-able work
A dirty read is when you read a change that another transaction made but hasn't committed yet — and then that transaction rolls back, so the value you read never really existed. Story: Transaction B is in the middle of giving Maya a $500 bonus. It has written $500 into her row but hasn't committed. Right then, your transaction reads Maya's balance and sees the $500. You make a decision based on it — maybe you let her buy something. But then B hits an error and rolls back: the $500 is undone, gone. You acted on a number that was never actually true. That's a dirty read — reading someone's dirty (uncommitted) laundry.
Problem 2 — the non-repeatable read: a row changes under you
A non-repeatable read is when you read the SAME row twice inside one transaction and get two different values, because someone else changed and committed it in between. Story: Your transaction reads Maya's balance: $200. You do some other work. Then, still inside the same transaction, you read Maya's balance again — and now it's $700, because Transaction B added money and committed in the gap. You asked the same question twice in one breath and got two different answers. Nothing you did changed it; the ground shifted under you. For logic that assumes a value stays put while it works, that's a real bug.
Problem 3 — the phantom read: new rows appear
A phantom read is like the non-repeatable read, but it's whole ROWS appearing (or vanishing), not one value changing. You run the same search twice in one transaction and the second time there are extra rows that weren't there before. Story: Your transaction counts 'all users in Pune' — it finds 10. You do some work, then count again, still in the same transaction — now it's 11, because Transaction B inserted a new Pune user and committed in between. An eleventh row materialized like a ghost — a 'phantom'. The rows you'd already seen didn't change; a brand-new one just showed up to ruin your count.
The four levels: a dial from loose to strict
Databases let you pick how strict isolation is, with four standard settings, loosest to strictest: • READ UNCOMMITTED — anything goes, even dirty reads. Fastest, least safe. Almost nobody uses it. • READ COMMITTED — you only ever read committed data, so no more dirty reads. But a row can still change between two reads (non-repeatable reads happen). A very common default. • REPEATABLE READ — any row you've read stays frozen for the rest of your transaction, so no dirty reads AND no non-repeatable reads. But brand-new rows can still appear (phantoms). • SERIALIZABLE — the strictest. Transactions behave as if they ran one-at-a-time, in a line. None of the three problems can happen — but transactions wait on each other the most, so it's the slowest. The pattern: each step up the ladder kills one more problem, by making transactions hold things still longer and wait for each other more. Safer costs speed.
It's a tradeoff
| Option | 👍 Pro | 👎 Con |
|---|---|---|
| Looser level (e.g. Read Committed) | Transactions wait on each other less, so the database handles more at once and feels faster. Great when occasional stale or shifting reads don't hurt. | You can hit non-repeatable reads and phantoms — a value or row can change under you mid-transaction. Risky for logic that needs a steady picture. |
| Stricter level (e.g. Serializable) | Rock-solid correctness: transactions act as if run one at a time, so none of the three read problems can bite. You reason as if you're alone. | Transactions block and wait on each other far more, so throughput drops — and conflicts can force the database to abort and retry a transaction. |
Questions you might have
▸Why would anyone ever pick a LOOSER level if it lets bugs happen?
Speed and scale. Stricter isolation makes transactions wait in line, which slows everything down. For lots of real work — showing a product page, counting likes — a slightly stale read is harmless, so a looser level is a great trade: much faster, and the 'bug' never actually matters. You crank it up only where correctness truly demands it, like money.
▸What's the difference between a non-repeatable read and a phantom read? They sound the same.
Both are 'the data changed while I was working,' but at different scopes. Non-repeatable read = a row you ALREADY read changed its value the second time you look. Phantom read = new ROWS that match your search appeared (or disappeared) the second time you run the query. One is an existing row mutating; the other is the set of rows growing or shrinking.
▸Is Serializable always the right choice since it's the safest?
Not always. It's the safest but also the slowest — transactions wait on each other the most, and the database may have to abort and retry ones that conflict. Many systems use Read Committed or Repeatable Read by default and reserve Serializable for the few transactions that truly can't tolerate any anomaly. Safety has a price, so you pay it where it's worth it.
▸Does the isolation level change WHAT my transaction does, or just what it can see?
Just what it can see (and how long it waits). The steps you wrote run the same way; the level only controls whether you're allowed to glimpse other transactions' in-progress or just-committed changes, and how strongly the database holds rows still for you. It's a visibility-and-timing dial, not a logic change.
Best read after: ACID transactions