Skip to content
Tutorials Center
My learning

Topics

Lesson 39 of 79 ยท Transactions and ACID

Isolation Levels and Concurrency Anomalies

Dirty reads, non-repeatable reads, phantoms, lost updates and write skew explained with a runnable interleaving simulator, plus SQL isolation levels and snapshots.

Perfect isolation (serializability) means concurrent transactions behave as if they ran one after another. It is also the most expensive guarantee a database can give, because it forces transactions to wait for each other or to be restarted. So SQL lets you choose a weaker isolation level, which allows some specific, well-named misbehaviours in exchange for more concurrency. To choose well, you need to recognize those misbehaviours, called anomalies or phenomena.

This lesson defines each anomaly and then lets you reproduce it. The Python simulator below is a tiny model of a database with three ways of deciding what a read can see. It is a teaching model, not a real engine, but its interleavings are the same ones you would run against PostgreSQL or MySQL with two terminal windows.

The classic anomalies

Dirty read Reading data that was never committed.
Non-repeatable read The same row changes between two reads.
Phantom New rows appear in a repeated query.
1 / 2

The anomalies

Dirty read. T1 changes a value but has not committed. T2 reads that value. T1 then rolls back, so T2 acted on data that never existed. This breaks atomicity from T2's point of view.

Non-repeatable read (fuzzy read). T1 reads a row. T2 updates that row and commits. T1 reads the row again and gets a different value. Within one transaction, the same query gave two answers.

Phantom read. T1 runs a query with a condition (all accounts above 1,000). T2 inserts or deletes a row that matches and commits. T1 repeats the query and finds a row that was not there before. The difference from a non-repeatable read is that no row T1 already read changed: the set of rows matching the predicate changed.

Lost update. Both T1 and T2 read a value, compute a new value from it, and write it back. The second write silently overwrites the first, so one update is lost. This is the classic read counter, add 1, write counter bug.

Write skew. Two transactions read overlapping data, then each writes to a different item, based on what it read. Neither write conflicts with the other, but together they violate an invariant. We will see the standard example, doctors on call, shortly.

A simulator with three visibility rules

The simulator keeps committed data in one dictionary and gives each transaction its own buffer of uncommitted writes. What a read sees depends on the mode:

  • read uncommitted: committed data plus everybody's pending writes (dirty reads possible).
  • read committed: committed data plus your own pending writes.
  • snapshot: the committed data as it was when your transaction began, plus your own writes. At commit time, first committer wins: if another transaction already committed a change to something you also wrote after your snapshot was taken, you abort.
Python Dirty read, non-repeatable read and phantom

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Read the output as a table of which mode permits which anomaly. Only read uncommitted shows the dirty read. Read committed fixes that but T1 sees x change from 100 to 200 and the count of matching rows grow from 2 to 3. The snapshot mode freezes what T1 sees, so both reads agree. That is why snapshot isolation is often described as "repeatable read that also prevents phantoms" for plain reads.

The write-side anomalies

Reads of a frozen snapshot do not protect you when you write based on what you read. Add this to the same simulator (the classes from the previous example are repeated here so this block runs on its own, in shortened form):

Python Lost update and write skew

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

In the lost update case, read committed ends with counter = 1 even though two increments ran. Under snapshot isolation the second committer is aborted, so the application must retry it, and after the retry the counter reaches 2. Databases with locking at the repeatable read level instead block the second reader-writer until the first finishes (and may deadlock, see Deadlocks).

In the write skew case each transaction checks the rule against its own snapshot, sees two doctors, and removes a different one. They write different rows, so first-committer-wins finds no clash and both commit: nobody is on call. When the same two transactions run one after another, the second sees only one doctor and stays. Only serializable isolation rejects the interleaved outcome, by tracking read-write dependencies or by locking the ranges that were read. The same cycle test you used in the precedence graph is what such implementations look for.

SQL standard isolation levels

The SQL standard defines four levels by which of three phenomena (dirty read, non-repeatable read, phantom) they permit:

Level Dirty read Non-repeatable read Phantom
READ UNCOMMITTED possible possible possible
READ COMMITTED prevented possible possible
REPEATABLE READ prevented prevented possible
SERIALIZABLE prevented prevented prevented

The table is a minimum promise. Real engines differ. The standard does not list lost update and write skew at all, and snapshot isolation, used by Oracle, PostgreSQL's REPEATABLE READ and others, sits awkwardly between the levels: it prevents dirty reads, non-repeatable reads and phantoms for plain reads, yet it permits write skew, so it is not serializable.

These server-level behaviours cannot run in the browser, so they are shown only as code. You set a level per transaction or session:

SQL
-- PostgreSQL / MySQL syntax, read-only here
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
-- ... statements ...
COMMIT;

A few facts worth remembering, with the caveat that defaults and details vary by version and configuration: PostgreSQL defaults to READ COMMITTED and treats READ UNCOMMITTED as READ COMMITTED; its REPEATABLE READ is snapshot isolation and SERIALIZABLE adds detection of dangerous read-write patterns, aborting a transaction when needed. MySQL's InnoDB defaults to REPEATABLE READ and uses next-key locks to limit phantoms. SQLite lets only one writer in at a time, so its transactions are effectively serializable. The practical guide, with locking details, is Isolation and Locking in SQL.

Avoiding lost updates in everyday code

Most lost updates come from a read-modify-write cycle in application code. The simplest fix needs no special isolation level: make the database do the arithmetic in one statement. A single UPDATE counter SET n = n + 1 reads and writes under the engine's own lock, so concurrent increments cannot overwrite each other. Other fixes are SELECT ... FOR UPDATE (lock the row you read, server engines only) and optimistic checks with a version column.

SQL Read-modify-write versus one atomic statement

Press Run (or Ctrl + Enter) to see the result here.

Both sales are counted (qty ends at 8, version 3). The stale client's guarded update changes zero rows, which is its signal to re-read and try again. This is optimistic concurrency control, covered in MVCC and Optimistic Concurrency Control.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. T1 reads the same row twice and gets different values because T2 committed an update in between. Which anomaly is this?

    A non-repeatable read is a changed value in a row that was already read. A phantom would be a new row appearing in a repeated predicate query.
  2. 2. Two transactions each read that two doctors are on call, and each takes a different doctor off call. Both commit under snapshot isolation. What happened?

    The transactions wrote different rows, so first-committer-wins saw no clash, yet together they broke the invariant. This is the signature of write skew, which only serializable isolation prevents.
  3. 3. Which statement is the simplest way to avoid a lost update on a counter?

    A single statement that computes the new value inside the database cannot be interleaved with another writer, unlike a read-modify-write cycle split between application and database.
  4. 4. According to the SQL standard table, which level still allows phantoms?

    REPEATABLE READ prevents dirty and non-repeatable reads but may still show new rows matching a repeated predicate. Only SERIALIZABLE prevents phantoms by the standard's definition, though real engines can be stricter.

Summary

  • Anomalies are named misbehaviours: dirty read, non-repeatable read, phantom, lost update and write skew.
  • The SQL standard levels (read uncommitted, read committed, repeatable read, serializable) permit fewer anomalies as they go up, at the cost of concurrency.
  • Snapshot isolation gives each transaction a frozen view and aborts the second writer of the same row, but it still allows write skew.
  • Only serializable isolation prevents all of them; read-modify-write bugs are best removed with atomic statements, locks or version checks.
  • Defaults and exact behaviour differ per engine, so check your database's documentation before relying on a level.

Next: Locks: Shared, Exclusive and Intention.