Skip to content
learn
  1. Learn
  2. High Level System Design
  3. Databases and Storage Fundamentals

System Design HLD-06.04

Isolation levels and anomalies

Dirty reads, non-repeatable reads, phantoms, lost updates and write skew; what each isolation level prevents; MVCC; and SELECT FOR UPDATE.

Advanced 16 min Last reviewed

The big idea

The I in ACID promises that concurrent transactions do not trip over each other. Taken literally, that would mean running them one at a time, which is far too slow for a busy database. So databases offer isolation levelsA setting that controls which effects of concurrently running transactions one transaction may observe, trading strictness for performance.: weaker levels allow more concurrency but let certain strange outcomes, called anomalies, through. Most databases default to a weaker level than serializable, which means many applications are exposed to some anomalies without their developers knowing.

Each anomaly is a specific interleaving that produces a wrong answer: reading data another transaction later rolled back, seeing a value change mid-transaction, two increments where one is lost, or two checks that are each valid but together break a rule. Knowing them by name lets you recognize the bug in a design review and pick the level, or the explicit lock, that prevents it.

Think of two people editing the same shared shopping list from their phones. Depending on the app's rules, you may see the other person's half-typed item, see an item vanish while you read, both "buy the last milk", or both decide someone else will buy bread.

How it works

Step 1: The anomalies

  • Dirty read: reading another transaction's uncommitted change, which may then be rolled back.
  • Non-repeatable read: reading the same row twice in one transaction and getting different values, because another transaction committed in between.
  • Phantom read: running the same query twice and getting a different set of rows, because another transaction inserted or deleted matching rows.
  • Lost update: two transactions read a value, both compute a new value from it, and the second write silently overwrites the first.
  • Write skew: two transactions read overlapping data, each checks a rule that holds, and each writes something different; together they break the rule.

Write skew is the subtle one:

Write skew: two doctors leave the on-call rota at once Alice and Bob are both on call; the rule is that at least one must remain. Alice's transaction counts two doctors on call and removes Alice. Bob's transaction, running at the same time, also counts two and removes Bob. Both commit, leaving nobody on call. Alice's transaction Database on call: Alice, Bob Bob's transaction COUNT on call -> 2, ok to leave COUNT on call -> 2, ok to leave Alice off call; COMMIT Bob off call; COMMIT nobody is on call: rule broken Figure 1. Write skew. Each transaction's check was correct in its own snapshot, and they updated different rows, so snapshot isolation sees no conflict. Together they leave nobody on call (red).

Step 2: The levels

LevelDirty readNon-repeatablePhantomLost updateWrite skew
Read uncommittedpossiblepossiblepossiblepossiblepossible
Read committedpreventedpossiblepossiblepossiblepossible
Snapshot / repeatable readpreventedpreventedprevented*prevented*possible
Serializablepreventedpreventedpreventedpreventedprevented

*As implemented in PostgreSQL's repeatable read, which is snapshot isolation. The SQL standard's definitions are looser, and databases differ: MySQL's repeatable read, for example, can still lose updates for a plain read-then-write. Always check what your database actually does.

Read committed is the default in PostgreSQL, Oracle and SQL Server. Repeatable read is MySQL InnoDB's default.

Step 3: MVCC, how snapshots work

Most databases implement read committed and snapshot isolation with MVCCMulti-version concurrency control: keeping several versions of each row so readers see a consistent snapshot without blocking writers.. An update does not overwrite a row; it writes a new version tagged with the transaction that created it. Each transaction reads the versions that were committed as of its snapshot, so readers never block writers and writers never block readers. Old versions are cleaned up once no transaction can still see them.

Try it

This interactive needs JavaScript. The diagrams and text above cover the same idea.

Try this:

  1. Lost update at read committed: both transactions sell an item, stock ends at 9 instead of 8. Switch to snapshot isolation: the second writer gets a serialization error and must retry.
  2. Write skew at snapshot isolation: both doctors leave. Switch to serializable: one transaction is aborted.
  3. Dirty read: only read uncommitted shows it. Most databases do not even implement that level.

Going deeper

Fixing anomalies without serializable

Serializable is the simplest correct choice, and modern implementations (PostgreSQL's serializable snapshot isolation) are reasonably fast, but they abort more transactions, so the application must retry. Alternatives at lower levels:

  • Atomic updates: UPDATE items SET stock = stock - 1 WHERE id = 7 AND stock > 0 does the read and write in one statement, preventing lost updates.
  • Explicit locks: SELECT ... FOR UPDATE locks the rows you read, so a concurrent transaction waits instead of reading the same old value.
  • Constraints: a unique index prevents two bookings of the same seat, whatever the isolation level.
  • Materializing the conflict: for write skew, make both transactions update a common row (a per-shift counter), so the conflict becomes a write-write conflict that snapshot isolation detects.

Distributed systems have isolation too

These same anomalies reappear, with new names, when data is replicated or partitioned across machines. Consistency models in PACELC and consistency models are the distributed cousins of isolation levels.

Trade-offs

OptionWins whenLoses when
Read committed Most web workloads with short transactions and atomic updates Read-modify-write logic and multi-row checks, without explicit locks
Snapshot isolation Consistent reports and read-modify-write with retry on conflict Write skew across different rows
Serializable Correctness by default for complex invariants Throughput under contention, and it requires retry logic

Common mistakes

  • Assuming your database's default level is serializable. It almost never is.
  • Read-then-write logic in application code (read stock, subtract, write) without an atomic update or lock.
  • Using serializable without retrying aborted transactions.
  • Trusting the level's name: "repeatable read" means different things in different databases.

Check yourself

  1. Two transactions each read stock 10 and write 9. What is this anomaly?

    Show answer

    B. Lost update Both read the same value and the second write overwrites the first; one decrement is lost.

  2. Which level is the default in PostgreSQL?

    Show answer

    B. Read committed PostgreSQL, Oracle and SQL Server default to read committed.

  3. Why does snapshot isolation allow write skew?

    Show answer

    B. The transactions write different rows, so there is no write-write conflict to detect Each check is valid in its snapshot; the conflict is between a read and another's write, which SI does not track.

  4. Which statement avoids a lost update at read committed?

    Show answer

    B. UPDATE items SET stock = stock - 1 WHERE id = 7 AND stock > 0 The read and write happen atomically inside one statement.

Recap

  • Isolation levels trade strictness for concurrency; weaker levels allow anomalies.
  • Anomalies: dirty read, non-repeatable read, phantom, lost update, write skew.
  • Read committed is the common default; snapshot isolation still allows write skew.
  • MVCC keeps row versions so readers and writers do not block each other.
  • Use atomic updates, SELECT FOR UPDATE, constraints or serializable with retries.

Get new lessons by email

Get new posts on backend and AI internals. No spam, unsubscribe anytime.

Your email is only used to send new posts. Privacy

Next up