Isolation Levels

You now know four ways concurrency can hurt you.

Here's the uncomfortable part: preventing all four costs speed. Sometimes a lot of it.

So the database doesn't decide for you. You choose.

Why & what

Not every query needs perfect safety. A dashboard counting logins can tolerate a slightly stale number. A fee payment cannot tolerate a lost update, ever.

So SQL gives you a dial, from "fast and risky" to "slow and airtight."

An isolation level is a setting that decides which concurrency problems your transaction is protected from. Higher levels prevent more problems and run slower.

How it works

Four levels, weakest first. Each one adds protection to the one above it.

1. READ UNCOMMITTED — you can read other transactions' uncommitted changes. All four problems possible, dirty reads included. Almost never used deliberately.
2. READ COMMITTED — you only ever read committed data, so dirty reads are gone. But if you read the same row twice, it may have changed in between. This is the default in most databases, including PostgreSQL, Oracle and SQL Server.
3. REPEATABLE READ — any row you've read is frozen for the rest of your transaction. Non-repeatable reads gone. But new rows matching your search can still appear, so phantoms remain possible. This is MySQL/InnoDB's default.
4. SERIALIZABLE — the strictest. The result must be as if transactions ran one after another. All four problems gone, including phantoms. Slowest, and most likely to make transactions block or abort.
5. Choose by the cost of being wrong. Aisha's fee payment → SERIALIZABLE or at least REPEATABLE READ, because a lost update is real money. A "students online right now" counter → READ COMMITTED is plenty.

Pause here. Your transaction sees a row change between two reads. Which level would you move up to?

REPEATABLE READ — that's exactly the problem it removes.

Notice: every row down the list is safer and slower — that is the whole trade.

Common confusion

People think higher isolation is simply "better." It isn't free, and it isn't free in a way that shows up in production: higher levels hold locks longer, so more transactions wait, and SERIALIZABLE will sometimes abort your transaction and make you retry.

Second: the defaults differ between databases. PostgreSQL, Oracle and SQL Server default to READ COMMITTED. MySQL's InnoDB defaults to REPEATABLE READ. Knowing that one fact is a small thing that makes an answer sound experienced.

Third: SERIALIZABLE doesn't mean "one at a time." Transactions still overlap. It only guarantees the result matches some one-at-a-time order.

Interview angle

1. "List the isolation levels and what each prevents." — Four levels from READ UNCOMMITTED to SERIALIZABLE, each removing one more problem. Naming which problem drops out at each step is the whole answer.
2. "Which is the default?" — Usually READ COMMITTED; MySQL's InnoDB uses REPEATABLE READ. Say the level you'd pick depends on how expensive a wrong answer is.
3. "Why not always use SERIALIZABLE?" — Locks held longer, more waiting, and transactions may be aborted and need retrying. It's a throughput cost, not a correctness one.

Recap

Isolation levels are a dial from fast-and-risky to slow-and-safe — READ UNCOMMITTED allows everything, READ COMMITTED stops dirty reads, REPEATABLE READ stops non-repeatable reads, SERIALIZABLE stops phantoms too.