The Four Isolation Levels

When an interviewer asks "name the four isolation levels", the real test is whether you can explain the trade-off: stronger isolation means fewer anomalies but lower concurrency.

The SQL standard defines four levels, ordered from weakest to strongest:

• READ UNCOMMITTED
• READ COMMITTED
• REPEATABLE READ
• SERIALIZABLE

Each level permits or forbids a specific set of read anomalies. This lesson covers the levels; the next covers the anomalies in detail.

You set isolation per transaction or per session. The syntax is nearly identical across engines.

If you do not set it, every database has a default. Knowing the defaults is a frequent interview question, so we will cover them at the end.

READ UNCOMMITTED is the weakest level. A transaction can read rows that another transaction has modified but not yet committed. These are called dirty reads.

If that other transaction rolls back, you have read data that never officially existed. This is dangerous for anything that must be correct.

Note: Postgres treats READ UNCOMMITTED the same as READ COMMITTED, so it never actually does dirty reads. SQL Server and MySQL do honor it.

READ COMMITTED guarantees you only ever read data that has been committed. No dirty reads.

However, each statement sees the latest committed snapshot. If you run the same query twice in one transaction, another committed transaction in between can change the result. That anomaly is a non-repeatable read.

This is the default in Postgres, Oracle, and SQL Server, and it is a sensible balance for most applications.

REPEATABLE READ ensures that if you read a row twice in the same transaction, you get the same value both times. It takes a consistent snapshot at the start of the transaction.

It prevents dirty reads and non-repeatable reads. The standard still permits phantom reads: new rows matching your WHERE clause that appear on re-query.

Important: this is the default in MySQL/InnoDB, and InnoDB's implementation also blocks most phantoms via next-key locks.

SERIALIZABLE is the strictest level. The database guarantees that the result of running transactions concurrently is identical to running them one after another in some serial order.

It prevents dirty reads, non-repeatable reads, and phantoms. The cost: more locking or, in Postgres, serialization-failure aborts that you must retry.

Interview phrasing: "SERIALIZABLE gives the illusion that every transaction ran alone, at the price of reduced concurrency and possible retries."

The single most useful thing to memorize is which anomaly each level allows. "Yes" means the anomaly can occur.

• READ UNCOMMITTED: dirty=Yes, non-repeatable=Yes, phantom=Yes
• READ COMMITTED: dirty=No, non-repeatable=Yes, phantom=Yes
• REPEATABLE READ: dirty=No, non-repeatable=No, phantom=Yes (per standard)
• SERIALIZABLE: dirty=No, non-repeatable=No, phantom=No

Each step up forbids one more anomaly. That progression is the whole answer.

A senior-level distinction: the SQL standard defines levels by which anomalies they must prevent, not how. Real engines often prevent more.

• Postgres REPEATABLE READ uses snapshot isolation and blocks phantoms too, though it can still hit write-skew.
• MySQL/InnoDB REPEATABLE READ blocks phantoms with next-key locking.
• Postgres SERIALIZABLE uses SSI (Serializable Snapshot Isolation), aborting on conflict rather than heavy locking.

Mentioning this shows you know the standard is a floor, not the exact behavior.

Defaults come up constantly. Commit these to memory:

• PostgreSQL: READ COMMITTED
• Oracle: READ COMMITTED (no dirty reads ever)
• SQL Server: READ COMMITTED
• MySQL (InnoDB): REPEATABLE READ

The MySQL outlier is a favorite gotcha. If asked "what is the default isolation level?" always clarify the engine first.

How do you decide? Frame it as risk versus throughput.

• Use READ COMMITTED for typical OLTP; it is fast and avoids dirty reads.
• Use REPEATABLE READ when a transaction reads the same data multiple times and must stay stable (reports, multi-step calculations).
• Use SERIALIZABLE for correctness-critical logic where any anomaly is unacceptable, and design retry logic for aborts.

Almost never use READ UNCOMMITTED in production.

Once you list the levels, interviewers probe with rapid follow-ups. Have crisp answers ready:

• "Which level prevents dirty reads but allows non-repeatable reads?" READ COMMITTED.
• "What is the only anomaly REPEATABLE READ still permits per the standard?" Phantom reads.
• "Why not always use SERIALIZABLE?" It reduces concurrency and can force transaction retries on serialization failures.
• "Does a higher level cost more?" Yes, in locking or in abort-and-retry overhead.

Answering these instantly proves the ladder is internalized, not memorized.

One of these is the most-tested default-level fact.

The four isolation levels form a ladder from weakest to strongest: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE. Each step up forbids one more anomaly (dirty, non-repeatable, phantom) at the cost of concurrency.

Remember the defaults (READ COMMITTED everywhere except MySQL's REPEATABLE READ), and note that real engines often prevent more than the standard requires. Next, we examine the three read anomalies these levels are designed to stop.