Full round replay — isolation levels

Pass: Atomicity, consistency, isolation, durability. Isolation is the only one with levels — every level below serializable is buying throughput by permitting an anomaly. The others you either have or you do not.

Fail: Recites the acronym with no view on which part varies.

Pass: 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 already scanned. It matters because locking the rows you read cannot stop a row you have not seen from appearing.

Fail: Treats them as the same thing, or cannot say why the difference matters.

Pass: PostgreSQL, Oracle and SQL Server default to read committed. MySQL InnoDB defaults to repeatable read. Worth knowing exactly, because a team moving between them gets different behaviour with no code change.

Fail: Says everyone defaults to read committed, or does not know.

Pass: By the standard, yes. PostgreSQL's repeatable read is snapshot isolation and prevents them. The standard states the minimum each level must prevent, not a ceiling — implementations are free to be stricter, and it is worth saying both halves.

Fail: Gives only the textbook answer, or only the PostgreSQL answer.

Pass: None of the three. Both read committed data, read it once, and wrote a value derived from it. It is a lost update, which the standard's table does not cover and read committed permits everywhere.

Fail: Names dirty read or non-repeatable read.

Pass: Not reliably. PostgreSQL's repeatable read detects the write-write conflict and aborts; MySQL InnoDB's does not. Fix the operation instead — do the arithmetic in one statement, or take the row lock with SELECT FOR UPDATE, or use a version column and retry.

Fail: Says serializable and stops there.

Pass: Visibility, not correctness. Conflicting transactions abort, and the application must retry the whole transaction with backoff and jitter. Without a retry you have swapped silent corruption for user-facing errors and gained nothing.

Fail: Believes serializable makes the code correct by itself.

Pass: Two transactions read overlapping rows, each checks an invariant that still holds, and each writes a different row — so neither sees the other's write and the invariant breaks. Two on-call engineers each confirming someone else is on duty, then both going off. Snapshot isolation permits it; serializable does not.

Fail: Has not heard of it, or confuses it with a lost update.

Pass: When the problem is read consistency — a report reading many tables that must agree with each other wants one snapshot, which is exactly what repeatable read gives. Scope it to that transaction, never globally.

Fail: Raises it globally, or never.

Pass: It changes behaviour for every query written by someone assuming read committed, and under contention the abort rate can look like an outage. Raise it on the specific transactions with invariants across rows, and give those a retry policy.

Fail: Sees no downside.

Pass: 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 production does. Run the real engine and version in tests.

Fail: Assumes an in-memory database behaves the same.