Transaction isolation

Dirty reads, lost updates and write skew at each isolation level.

On the roadmap:Intermediate · step 1: Faster queries

Intermediateabout 15 to 20 min7 experiments

Experiments

New here? A few short steps show what happens, one at a time.

Settings

What can go wrong

T2 reads something T1 has written but not committed, and T1 then rolls back.

Isolation level

A transaction sees only committed data, but each read sees the latest, so the same read can return different answers.

At this level

Read committed

Dirty read

prevented

Turned back

nobody

T2 read 100, the last committed balance, and never saw T1's unfinished change.

Dirty read at read committed

1
T1 begins.
balance=100
2
T1 sets the balance to 0, not yet committed.
Held privately until commit.
balance=100
3
T2 begins.
balance=100
4
T2 reads the balance.
Sees 100.
balance=100
5
T1 changes its mind and rolls back.
Its writes are thrown away.
balance=100
6
T2 commits.
Its changes become visible to everyone.
balance=100

In plain words

When many transactions run at once, a database can let them see each other's work, or hide it. The isolation level is how much it hides. The weaker levels are faster and allow odd results, such as reading a change that is then undone, or two sales both counting the same stock.

Why it matters

Bugs from isolation are rare, hard to reproduce, and often involve money. Knowing what each level promises, and which anomaly it still allows, tells you when the default is enough and when a transaction needs a stronger level, a lock, or a different design. Each cell here is produced by running the scenario, not by looking it up.

How real systems do it

The simulation is a simplified model. Here is what production systems add or do.

The SQL standard defines four levels by the anomalies each allows: read uncommitted, read committed, repeatable read and serializable. Real databases differ in how they implement them.
PostgreSQL's default is read committed, and its repeatable read is really snapshot isolation, which also stops phantoms. It does not truly offer read uncommitted: that setting behaves like read committed. Its serializable level uses serializable snapshot isolation to catch write skew.
MySQL's InnoDB defaults to repeatable read and uses locks on ranges of an index to keep phantoms out. Oracle's default is read committed, and its serializable level is snapshot isolation.
Write skew is the classic reason people are surprised by snapshot isolation. The usual fixes are to run the transaction at serializable, to lock the rows that were read (SELECT … FOR UPDATE), or to put the rule in a constraint the database can enforce.