Pointer Walk

Transaction Isolation Levels

Four levels, and the anomaly each one still allows: dirty reads, non-repeatable reads, phantoms.

DatabasesAdvanced12 min read

Many transactions, one database

A transaction is a group of statements that should behave as one step. "Take 50 from account A and add 50 to account B" must happen completely or not at all.

Alone, a transaction is easy to reason about. The trouble is that databases run hundreds of them at the same time, on the same rows. The perfect behaviour would be as if they ran strictly one after another. That is safe, and slow, because everyone waits.

So databases offer a dial. Turn it down and transactions wait less but can see each other's half-finished work. Turn it up and they are better protected but slower. The settings on that dial are the isolation levels.

Each level is defined by which strange effects, called anomalies, it still allows.

The anomalies

Step through each one. Time runs from top to bottom.

Dirty readInteractive
Anomaly
Transaction A
Database
balance = 100
Transaction B

Two transactions touch the same data at the same time. Time runs from top to bottom.

Dirty read

Reading a change that another transaction has made but not committed. If that transaction rolls back, you acted on data that never officially existed.

Dirty readInteractive
Transaction A
Database
balance = 100
Transaction B

Two transactions touch the same data at the same time. Time runs from top to bottom.

Non-repeatable read

Reading the same row twice in one transaction and getting different values, because someone else committed a change in between.

Non-repeatable readInteractive
Transaction A
Database
balance = 100
Transaction B

Two transactions touch the same data at the same time. Time runs from top to bottom.

Phantom read

Running the same query twice and getting a different set of rows, because someone else inserted or deleted a row that matches your condition.

Phantom readInteractive
Transaction A
Database
2 orders over 100
Transaction B

Two transactions touch the same data at the same time. Time runs from top to bottom.

The difference from the previous one: there, a row you had read changed. Here, no row you read changed. A new one appeared.

Lost update

Two transactions read a value, each calculates a new one, and each writes it back. The second write overwrites the first, as if it never happened.

Lost updateInteractive
Transaction A
Database
stock = 10
Transaction B

Two transactions touch the same data at the same time. Time runs from top to bottom.

The four levels

The SQL standard names four levels. Each one rules out one more anomaly than the one before.

LevelDirty readNon-repeatable readPhantom read
Read uncommittedPossiblePossiblePossible
Read committedPreventedPossiblePossible
Repeatable readPreventedPreventedPossible
SerializablePreventedPreventedPrevented
  • Read uncommitted: you may see other transactions' unfinished work. Rarely used.
  • Read committed: each statement sees only committed data, but two statements in the same transaction can see different committed states.
  • Repeatable read: rows you have read will not change under you for the rest of the transaction.
  • Serializable: the result is the same as if the transactions had run one at a time.

What to do in practice

  1. Know your default level

    And therefore which anomalies your code can meet.

  2. Make read-then-write a single step where you can

    UPDATE stock SET count = count - 1 WHERE id = 7 cannot lose an update. Reading the count into your program and writing it back can.

  3. Lock the row when you must read first

    SELECT ... FOR UPDATE makes other writers wait until you are done.

  4. Use serializable for the few things that must be exactly right

    Money movements, seat bookings. Be ready to retry: at this level the database may cancel a transaction rather than allow a conflict.

Key takeaways

  • Isolation levels trade safety between concurrent transactions against speed.
  • Dirty read: seeing uncommitted data. Non-repeatable read: a row changes between two reads. Phantom: new rows appear in a repeated query.
  • The four standard levels are read uncommitted, read committed, repeatable read and serializable.
  • Most databases default to read committed or repeatable read, not serializable.
  • Prefer single atomic statements, lock rows you read before writing, and retry when the database cancels a transaction.

Your progress

Check your progress

0%

  1. Read the page50% of this page

    When you have gone through the page and its animations, tick it off.

  2. Take the quiz50% of this page

    9 questions, from easy to hard. Take it as often as you like: the questions change, and only your best score counts.

Saved on this device only. See all your progress