What are the isolation levels, and what does each permit?

The Answer

Say this in the room. 45 seconds.

• The levels are defined by which anomalies they permit, not by how they are implemented. That distinction is the whole question.
• Read uncommitted — dirty reads. Read committed — non-repeatable reads. Repeatable read — phantoms. Serializable — none.
• ACID's I is the one you trade. Atomicity, consistency and durability are not dials; isolation is, and every level below serializable is buying throughput with correctness.
• Defaults differ, and people get this wrong. PostgreSQL, Oracle and SQL Server default to read committed. MySQL InnoDB defaults to repeatable read.
• The standard's table describes the minimum a level must prevent, not what your database does. PostgreSQL's repeatable read is snapshot isolation and prevents phantoms, which the standard permits.
• The anomaly that actually costs money — the lost update — is not in the table at all, and raising the isolation level is not reliably the fix. SELECT ... FOR UPDATE or a version column is.

Understand It

Everything below runs against a model of a multi-version store, not a database. It exists so two transactions can be interleaved in an exact, scripted order — the interleaving is the code you read, rather than something a race has to produce. The visibility rules are the real ones; the locking, logging and index-range machinery of a real engine is not modelled.

Dirty read: seeing work that may never have happened

// One row. Two transactions. The second one reads while the first is mid-flight.
for (Level level : List.of(Level.READ_UNCOMMITTED, Level.READ_COMMITTED)) {
    var db = new Store().seed("alice", 100);
    var writer = db.begin(level);
    var reader = db.begin(level);

    writer.write("alice", 500);                       // not committed yet
    System.out.printf("  %-17s reader sees %s while the write is uncommitted%n",
        level, show(reader.read("alice")));
}

  READ_UNCOMMITTED  reader sees 500 while the write is uncommitted
  READ_COMMITTED    reader sees 100 while the write is uncommitted

The reader at read uncommitted acts on 500, and the writer may still roll back — leaving a decision made from a number that never existed. This is the only anomaly almost nobody wants, which is why read uncommitted is rare in practice and why PostgreSQL does not really implement it at all: ask for it and you get read committed.

Non-repeatable read: the same query, two answers

// The same query, twice, inside one transaction. Nothing in between is illegal:
// another transaction simply commits.
for (Level level : List.of(Level.READ_COMMITTED, Level.REPEATABLE_READ)) {
    var db = new Store().seed("alice", 100);
    var report = db.begin(level);
    System.out.printf("  %-15s first read  : %s%n", level, show(report.read("alice")));

    var transfer = db.begin(level);
    transfer.write("alice", 500);
    transfer.commit();

    System.out.printf("  %-15s second read : %s%n", level, show(report.read("alice")));
}

  READ_COMMITTED  first read  : 100
  READ_COMMITTED  second read : 500
  REPEATABLE_READ first read  : 100
  REPEATABLE_READ second read : 100

Nothing broke a rule. The other transaction committed legitimately, and read committed's promise is only that you see committed data — not that you see the same committed data twice.

This is the level most production systems run at, so it is worth being concrete about the cost: a report that reads a total, then reads the rows behind it, can print a total that does not match its own rows. Not because of a bug in either query, but because they ran at different instants.

Phantom read: the same range, two row counts

// A range query, twice. This time the other transaction INSERTS a row.
for (Level level : List.of(Level.READ_COMMITTED, Level.REPEATABLE_READ)) {
    var db = new Store().seed("order:1", 10).seed("order:2", 20);
    var audit = db.begin(level);
    System.out.printf("  %-15s first scan  : %s%n", level, audit.readRange("order:"));

    var inserter = db.begin(level);
    inserter.write("order:3", 30);
    inserter.commit();

    System.out.printf("  %-15s second scan : %s%n", level, audit.readRange("order:"));
}

  READ_COMMITTED  first scan  : [order:1, order:2]
  READ_COMMITTED  second scan : [order:1, order:2, order:3]
  REPEATABLE_READ first scan  : [order:1, order:2]
  REPEATABLE_READ second scan : [order:1, order:2]

A phantom is a non-repeatable read where the thing that changed is which rows exist, not what a row contains. It matters because the defences are different: locking the rows you read cannot stop a row you have not seen from appearing. Preventing phantoms needs something that locks the gap — a predicate or range lock — or an entire consistent snapshot.

And here the model tells you something true that the standard does not. The SQL standard permits phantoms at repeatable read. This model prevents them, because it reads from a snapshot taken when the transaction began — and so does PostgreSQL, whose repeatable read is snapshot isolation. The standard is stating a minimum each level must prevent, not a ceiling. A database is free to be stricter, and the good ones are.

That is why "repeatable read allows phantoms" is simultaneously the correct textbook answer and wrong about the database in front of you. Both halves are worth saying.

The anomaly that is not in the table

// Two people top up the same account by 50 at the same time. Read, add, write.
// Nothing here is a dirty read, a non-repeatable read or a phantom — and money
// still disappears.
for (Level level : List.of(Level.READ_COMMITTED, Level.REPEATABLE_READ, Level.SERIALIZABLE)) {
    var db = new Store().seed("alice", 100);
    var a = db.begin(level);
    var b = db.begin(level);

    int seenByA = a.read("alice");                 // both read 100
    int seenByB = b.read("alice");

    a.write("alice", seenByA + 50);
    a.commit();
    b.write("alice", seenByB + 50);
    b.commit();

    int balance = db.begin(Level.READ_COMMITTED).read("alice");
    String verdict = b.state().equals("aborted")
        ? "B is told to retry, and nothing is lost"
        : "one top-up vanished; 200 was correct";
    System.out.printf("  %-16s A %s, B %-9s balance %d — %s%n",
        level, a.state(), b.state() + ",", balance, verdict);
}

  READ_COMMITTED   A committed, B committed, balance 150 — one top-up vanished; 200 was correct
  REPEATABLE_READ  A committed, B committed, balance 150 — one top-up vanished; 200 was correct
  SERIALIZABLE     A committed, B aborted,  balance 150 — B is told to retry, and nothing is lost

Two customers each add 50 to a 100 balance. Both transactions committed successfully. The balance is 150.

Nothing here is a dirty read, a non-repeatable read, or a phantom. Both transactions read committed data, read it once, and wrote a value derived from it. The pattern is read, modify, write — the most common thing an application does — and the standard's three anomalies have nothing to say about it.

The third line is the important one. At serializable, B's commit is refused, and the balance is still wrong at 150. That is the correct behaviour: the database has told the application that its assumption no longer holds, and the application must retry. Serializable does not make the code correct on its own — it converts a silent wrong answer into an error you are obliged to handle. Code that ignores the exception is no better off.

The whole table, generated rather than remembered

// Run every anomaly at every level and print what actually happened.
record Anomaly(String name, Predicate<Level> happens) {}
var anomalies = List.of(
    new Anomaly("dirty read",          l -> dirtyRead(l)),
    new Anomaly("non-repeatable read", l -> nonRepeatableRead(l)),
    new Anomaly("phantom read",        l -> phantomRead(l)),
    new Anomaly("lost update",         l -> lostUpdate(l)));

System.out.printf("  %-20s", "");
for (Level level : Level.values()) System.out.printf("%-18s", level);
System.out.println();
for (var a : anomalies) {
    System.out.printf("  %-20s", a.name());
    for (Level level : Level.values())
        System.out.printf("%-18s", a.happens().test(level) ? "happens" : "prevented");
    System.out.println();
}

                      READ_UNCOMMITTED  READ_COMMITTED    REPEATABLE_READ   SERIALIZABLE      
  dirty read          happens           prevented         prevented         prevented         
  non-repeatable read happens           happens           prevented         prevented         
  phantom read        happens           happens           prevented         prevented         
  lost update         happens           happens           happens           prevented         

Two rows in this table are not the textbook table, and both are the point.

Phantoms show as prevented at repeatable read because this model, like PostgreSQL, is snapshot-based. The standard permits them there; the implementation is stricter.

Lost update happens at repeatable read here, matching MySQL InnoDB and the standard's silence. PostgreSQL's repeatable read is stricter again: it detects the write-write conflict and raises could not serialize access due to concurrent update. So the sentence "repeatable read protects me from lost updates" is true on one popular database and false on another, which makes it a bad thing to rely on and a good thing to be asked about.

Reference

The correct implementation, the configuration, and the migration path. Copy from here.

Defaults, which are not the same

-- Find out rather than assume.
SELECT current_setting('transaction_isolation');   -- PostgreSQL
SELECT @@transaction_isolation;                    -- MySQL 8
DBCC USEROPTIONS;                                  -- SQL Server

Setting it, at the right scope

// Spring: per method, and it must be a level the driver actually supports.
@Transactional(isolation = Isolation.REPEATABLE_READ)
public void reconcile(long accountId) { ... }

// Isolation.DEFAULT means "whatever the datasource says" — which is the
// database default, not a Spring default. Read it as "unspecified".
@Transactional(isolation = Isolation.DEFAULT)

Raise the level on the specific transaction that needs it, never globally. A global change alters the behaviour of every query in the application, including the ones written by people who assumed read committed.

Fixing a lost update, which the isolation level will not do for you

-- 1. Pessimistic: take the row lock at read time. Everyone else waits.
SELECT balance FROM account WHERE id = ? FOR UPDATE;
UPDATE account SET balance = ? WHERE id = ?;

// The JPA equivalent.
@Lock(LockModeType.PESSIMISTIC_WRITE)
@Query("select a from Account a where a.id = :id")
Account findForUpdate(@Param("id") Long id);

// 2. Optimistic: no lock, but the write refuses to apply to a stale row.
@Entity
class Account {
    @Id Long id;
    @Version long version;          // Hibernate adds "and version = ?" to every update
    BigDecimal balance;
}
// A concurrent write throws OptimisticLockException. Catching it and retrying
// is not optional — it IS the concurrency control.

-- 3. Best of all: do not read-modify-write. Let the database do the arithmetic.
UPDATE account SET balance = balance + 50 WHERE id = ?;

The third option removes the anomaly rather than defending against it, because there is no gap between the read and the write for another transaction to fit into. Reach for it first; it is available far more often than people assume.

Choosing a level

Retrying, because serializable makes that your job

// At serializable — and at PostgreSQL's repeatable read — commits fail under
// contention as a matter of routine. This is not an error path; it is the
// design, and code that does not retry is worse off than code at a lower level.
@Retryable(
    retryFor = { CannotSerializeTransactionException.class, ConcurrencyFailureException.class },
    maxAttempts = 4,
    backoff = @Backoff(delay = 50, multiplier = 2, random = true))   // jitter matters
@Transactional(isolation = Isolation.SERIALIZABLE)
public void transfer(long from, long to, BigDecimal amount) { ... }

The retry must wrap the whole transaction, not a step inside it. A retry inside the transaction re-runs against the same doomed snapshot.

Scenarios

Real situations, with the decision and the argument.

1. A nightly report's totals do not match the rows it lists.

Both queries are correct in isolation and ran at different instants under read committed, so a transaction committed between them. Nothing is corrupt; the report simply has no consistent view.

Wrap the whole report in one repeatable-read transaction, so every query reads the same snapshot. This is the case where raising the level is genuinely the right fix — the problem is read consistency, which is exactly what the level controls. Keep it scoped to the report, and expect it to hold a snapshot open for the duration, which on PostgreSQL delays vacuum on those tables.

2. Two support agents credit the same customer, and one credit disappears.

A lost update, and the isolation level is not where to look. Both transactions read the same balance and wrote a value derived from it; on MySQL's repeatable read that is permitted, and on read committed it is permitted everywhere.

Fix the operation, not the level: UPDATE ... SET balance = balance + ? if the change is relative, SELECT ... FOR UPDATE if you must read first, or a @Version column with a retry. Raising to serializable also works and is the most expensive of the four, because it makes every other transaction on those rows pay for a problem that lives in one statement.

3. Someone proposes setting the whole application to serializable to be safe.

Understandable and usually wrong. Serializable does not make code correct — it makes conflicts visible, as aborted transactions the application must retry. An application that does not retry gets a new class of user-facing error in exchange for a class of silent corruption, and under contention the abort rate can be high enough to look like an outage.

The counter-proposal is to identify which transactions actually need it — those with invariants spanning rows — and raise only those, with a retry policy. Push back specifically on the global change, because it also silently alters every query written by someone who assumed read committed.

4. It works on H2 in tests and fails on PostgreSQL in production.

Isolation behaviour is one of the least portable things in SQL, and an in-memory test database is not a model of your production engine. Levels are implemented differently, defaults differ, and H2 will happily accept a level it does not really enforce.

This is the strongest argument for Testcontainers over H2: run the same engine and version as production. If that is not possible, at minimum write down which level the code assumes and assert it at startup, so the assumption is visible rather than inferred.

5. After moving to serializable, throughput dropped and errors appeared under load.

Both are the expected consequence, which is worth establishing before anyone calls it a regression. Serializable detects conflicts and aborts transactions; more concurrency means more conflicts means more aborts.

Two things to check. Are the transactions as short as possible — no remote calls, no user think-time, no work that could happen outside? And is there a retry with backoff and jitter, so aborted transactions do not all return at the same instant and collide again? If both are already true and the abort rate is still high, the access pattern is genuinely contended and the answer is to reduce the contention — narrower rows, finer-grained keys, or moving the arithmetic into a single statement.

Interviewer's Next Move

The question after the question. This is what the round is actually testing.

1. "What are the isolation levels?" Read uncommitted, read committed, repeatable read, serializable — defined by which anomalies each permits, not by how they are built. Dirty read, non-repeatable read, phantom, and then none.

2. "What does ACID stand for, and which letter is negotiable?" Atomicity, consistency, isolation, durability. Isolation is the only one with a dial — the levels are all weakenings of it. The others are properties you have or do not.

3. "What is the difference between a non-repeatable read and a phantom?" A non-repeatable read is the same row changing value between two reads. A phantom is the set of rows changing — a new row appearing in a range you already scanned. The distinction matters because locking rows you have read cannot prevent a row you have not seen.

4. "Which level does your database default to?" PostgreSQL, Oracle and SQL Server: read committed. MySQL InnoDB: repeatable read. Worth knowing precisely, because a team moving between the two gets different behaviour with no code change.

5. "Does repeatable read allow phantoms?" By the standard, yes. In PostgreSQL, no — its repeatable read is snapshot isolation and prevents them. The standard specifies a minimum each level must prevent, and implementations are free to be stricter.

6. "Two transactions each add 50 to a balance and one is lost. Which anomaly is that?" None of the three in the standard's table. It is a lost update, and read committed permits it everywhere. Raising the level is not reliably the fix — PostgreSQL's repeatable read catches it, MySQL's does not.

7. "So how do you fix it?" Do the arithmetic in one statement — SET balance = balance + ? — so there is no gap between read and write. If you must read first, SELECT ... FOR UPDATE or a @Version column with a retry.

8. "When would you actually use serializable?" When correctness depends on an invariant across rows that no single row lock protects — the write skew case, like two on-call engineers each checking that someone else is on duty before going off. Scope it to that transaction, and retry, because it will abort.

9. "What does serializable cost?" Throughput, and a new obligation. Conflicting transactions abort and must be retried by the application, so the failure mode moves from silent corruption to visible errors under load. Code that does not retry has traded one problem for another.

10. "Why is it risky to test this on H2?" Isolation is among the least portable parts of SQL. Defaults differ, implementations differ, and H2 will accept a level it does not enforce the way your production engine does. Run the real engine in tests.

Code traps

Trap A — predict before you run:

@Transactional
public void credit(long id, BigDecimal amount) {
    Account a = repository.findById(id).orElseThrow();
    a.setBalance(a.getBalance().add(amount));
    repository.save(a);
}

Trap B:

@Transactional(isolation = Isolation.SERIALIZABLE)
public void transfer(long from, long to, BigDecimal amount) {
    debit(from, amount);
    credit(to, amount);
}

Trap C:

@Transactional(isolation = Isolation.REPEATABLE_READ)
public Report build() {
    var totals = repository.totals();
    var rows = restClient.fetchEnrichment();   // 4 seconds
    return new Report(totals, rows);
}

Common wrong answers

Check Yourself

Q1. Two transactions each read a balance of 100, add 50, and commit successfully. The balance is 150. Which of the standard's anomalies is this?

Q2. Your colleague says "repeatable read permits phantom reads". Are they right?

Q3. After switching a service to serializable, error rates rose sharply under load. Is that a bug?

Practice