Transaction isolation levels — READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE: Question bank

This bank leans on the same building blocks as the parent note: the three anomalies (dirty read, non-repeatable read, phantom read), the four levels (U-C-R-S), and the two implementation families (lock-based and snapshot-based).

True or false — justify

True or false: SERIALIZABLE forces transactions to physically run one at a time.

False. It only guarantees the result equals some serial order; engines still run concurrently, often via SSI which runs optimistically and aborts offenders.

True or false: A dirty read can only happen at READ UNCOMMITTED.

True in the SQL standard's model — every level from READ COMMITTED up requires you to see only committed data, so uncommitted values are invisible.

True or false: Non-repeatable reads and phantom reads are the same anomaly with different names.

False. Non-repeatable = an existing row's value changes between two reads; phantom = the set of rows matching a predicate changes (rows appear/disappear). Content vs membership.

True or false: If your engine prevents phantoms at REPEATABLE READ, the SQL standard is broken.

False. The standard sets a minimum; engines may exceed it. MySQL InnoDB (next-key locks) and PostgreSQL (snapshots) legally prevent phantoms there.

True or false: Higher isolation is always the safer default, so SERIALIZABLE should be used everywhere.

False. Higher isolation means more blocking, deadlocks, and retries, hurting throughput. Pick the lowest level that blocks the anomalies your app cares about.

True or false: READ COMMITTED prevents you from ever reading the same value twice as different numbers.

False. It prevents dirty reads only. Each statement takes a fresh look, so a committed UPDATE between two reads gives a non-repeatable read.

True or false: Under MVCC, a reader can block a writer.

False (for the read itself). Snapshot readers see an older version and don't take row locks that block writers — "readers don't block writers" is the MVCC selling point.

True or false: The write skew anomaly is prevented at REPEATABLE READ.

False. Write skew (two transactions each read a set, then each write, breaking an invariant) slips through snapshot-based REPEATABLE READ; you need SERIALIZABLE / SSI.

True or false: A serialization_failure error means the database is broken.

False. It's the expected signal from SSI-style SERIALIZABLE that your transaction would have violated serializability; the correct response is to retry it.

Spot the error

"At READ COMMITTED, taking one snapshot at transaction start guarantees repeatable reads." — what's wrong?

READ COMMITTED takes a fresh snapshot per statement, not one at start. The "one snapshot for the whole transaction" behaviour is REPEATABLE READ.

"Dirty read and non-repeatable read both come from reading uncommitted data." — fix it.

Only the dirty read reads uncommitted data. A non-repeatable read reads committed data that a second transaction committed between your two reads.

"To stop phantoms with locks you just hold row locks on the rows you read." — what's missing?

Row locks protect existing rows but not the empty space a new row could fill. You need range / predicate (next-key) locks so nobody can INSERT into the queried range.

"PostgreSQL's default isolation level is SERIALIZABLE." — correct it.

PostgreSQL (and Oracle) default to READ COMMITTED. MySQL InnoDB defaults to REPEATABLE READ.

"SERIALIZABLE prevents dirty reads by holding locks longer than REPEATABLE READ; that's its only added guarantee." — spot the error.

Dirty reads are already gone at READ COMMITTED. SERIALIZABLE's added guarantee over REPEATABLE READ is blocking phantoms (and, via SSI, other serialization anomalies like write skew).

Why questions

Why does REPEATABLE READ still allow phantoms in the SQL standard?

Its guarantee protects the values of rows you already read, not the membership of a predicate. A brand-new committed row matches your WHERE and appears on re-query.

Why does raising READ UNCOMMITTED to READ COMMITTED kill the dirty read?

READ COMMITTED requires seeing only committed state, so the reader either blocks on the writer's lock (lock-based) or reads the last committed version (MVCC) — never the in-flight value.

Why can't you just "use locks on everything" instead of defining levels?

Locking everything gives serializability but destroys concurrency (throughput). Levels exist precisely to trade correctness for speed on a dial.

Why does SSI produce aborts rather than making transactions wait?

SSI is optimistic: it lets transactions run against snapshots and only detects a dangerous read-write dependency cycle at commit, at which point waiting can't help — it must abort and retry one.

Why do MySQL and PostgreSQL both "exceed the standard" at REPEATABLE READ?

Their implementations (InnoDB next-key locks; PG one snapshot per transaction) happen to also block many phantoms, so they deliver a stronger guarantee than the standard's minimum for free.

Why does SERIALIZABLE increase deadlock and retry rates?

It holds more/longer locks (range locks) or aborts more transactions, so concurrent transactions contend more often, producing more deadlocks and serialization failures.

Edge cases

Edge case: T1 reads a row, T2 UPDATEs and ROLLS BACK, T1 reads again at READ COMMITTED — same value both times?

Yes. READ COMMITTED never saw the uncommitted change, and the rollback restored the original, so both reads return the committed value — no anomaly.

Edge case: A read-only transaction at REPEATABLE READ — can it ever see a non-repeatable read?

No. Its consistent snapshot is fixed for the whole transaction, so every read of an existing row returns the same value regardless of other commits.

Edge case: Two transactions never touch the same rows — does isolation level matter for them?

Effectively no anomalies arise between them, but the cost (lock acquisition, snapshot management) still applies, and higher levels can still cause aborts from predicate-range conflicts.

Edge case: An empty result set (WHERE age>200 returns 0 rows) at SERIALIZABLE — can it still cause a conflict?

Yes. SERIALIZABLE tracks the predicate, so an INSERT of a matching row by another transaction is a real read-write conflict even though the initial read returned nothing.

Edge case: What happens if you run only a single transaction (no concurrency) at READ UNCOMMITTED?

Nothing bad — with no concurrent writer there is nothing uncommitted to peek at, so all levels behave identically for a lone transaction.

Edge case: Does the durability part of ACID change with isolation level?

No. Isolation levels tune the I (isolation) axis only; Atomicity, Consistency, and Durability are unaffected by which level you pick.

Recall One-line self-check before you leave

Which anomaly distinguishes REPEATABLE READ from SERIALIZABLE? Answer: the phantom read (predicate-membership change) — and, in snapshot engines, the related write skew.