CSE Roadmap

Core tier · about 2 hours · 5 parts

Part 4 of 5 · 2 min read

Matching isolation levels to the anomalies they prevent

Read committed stops dirty reads; repeatable read adds protection against non-repeatable reads; serializable stops all three.

Matching isolation levels to the anomalies they prevent

Think of three increasingly strict shopping-trip rules. One: never trust a price tag someone is actively changing. Two: never let your own earlier price check change mid-trip. Three: never let the whole inventory shift under you at all while you shop.

In plain words: read committed, the most common default isolation level, prevents dirty reads: it only ever lets a transaction read values that have already been committed. Repeatable read additionally prevents non-repeatable reads: once a transaction reads a row, that same row will return the same value if read again within the same transaction. Serializable, the strictest standard level, additionally prevents phantom reads as well, guaranteeing the entire schedule behaves as if transactions ran one at a time, matching the serializability definition from the previous page.

A worked example. Under read committed, a transaction's second read of a row's status can change if another transaction commits a change in between; under repeatable read, that same second read is guaranteed to return the original value instead.

The key lines.

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- this transaction is now protected against non-repeatable reads, but not necessarily phantoms

Read committed, repeatable read, serializable

Read committed prevents only dirty reads; repeatable read additionally prevents non-repeatable reads; serializable additionally prevents phantom reads, guaranteeing full schedule serializability.

Example: the three increasingly strict shopping-trip rules, from distrusting an actively changing price tag to never letting the whole inventory shift mid-trip, mirror these three levels.

Remember this

Each stricter isolation level prevents one more category of anomaly, but at a real concurrency cost covered on later pages. The right level depends on which specific anomaly actually threatens the application's correctness, a decision this level's closing page returns to directly.

Quick notes

  • A dirty read is reading another transaction's uncommitted value.
  • A non-repeatable read is one row's value changing between two reads in the same transaction.
  • A phantom read is a query's matching row set changing between two runs in the same transaction.
  • Read committed prevents dirty reads; repeatable read adds non-repeatable read protection; serializable adds phantom protection too.
  • Stricter levels prevent more anomalies but cost more concurrency, a trade-off explored later in this level.