Transaction isolation levels simulator
Isolation levels decide what one transaction can see of another that is running at the same time. Pick an anomaly and a level below, and two transactions run the same steps against a small table, statement by statement. Every result matches PostgreSQL 16. Further down: each anomaly explained, the full table, and how real databases differ.
BEGIN ISOLATION LEVEL READ COMMITTED; Transaction started. UPDATE accounts SET balance = 0 WHERE id = 1; Updates 1 row: balance is now 0 for this transaction. BEGIN ISOLATION LEVEL READ COMMITTED; Transaction started. SELECT balance FROM accounts WHERE id = 1; Returns 100. ROLLBACK; Rolled back: its changes are discarded. COMMIT; Committed. | id | balance (committed) | T1 pending | T2 pending |
|---|---|---|---|
| 1 | 100 |
Transaction 2 read 100, the committed value, and never saw the uncommitted change.
| Dirty read happens | |
| Prevented | |
| Prevented | |
| Prevented |
How to use the simulator
Each scenario is a fixed interleaving of two transactions against a small table: the left column is transaction 1, the right column transaction 2, and statements run top to bottom. Choose an isolation level and the result of every statement appears beside it, including statements that have to wait for a row lock and statements that fail with a serialization error. The panel shows the committed table, each transaction's uncommitted changes, and whether the anomaly happened.
Press Step through to run one statement at a time. Things to try:
- Lost update at read committed, then at repeatable read: the same steps either silently lose $10 or make transaction 2 fail and retry.
- Concurrent UPDATE at read committed: transaction 2 waits for transaction 1's lock, then adds to the new value, so nothing is lost.
- Write skew at repeatable read and at serializable: only serializable notices that both doctors went off call.
Read committed, repeatable read and serializable behave as PostgreSQL does. Each scenario was run on PostgreSQL 16 at those three levels with two real connections, and the values read, the waits, the errors, which transaction failed and the final table all matched. PostgreSQL treats read uncommitted as read committed, so read uncommitted here follows SQL Server and MySQL instead, where it really does allow dirty reads.
What are transaction isolation levels?
Isolation is the I in ACID: the promise that transactions running at the same time do not get in each other's way. The strongest form, serializability, means the result is the same as if the transactions had run one after another, in some order. That is expensive, so databases offer weaker isolation levels that allow specific kinds of interference, called anomalies or phenomena, in exchange for more concurrency.
You set the level per transaction, for example BEGIN ISOLATION LEVEL REPEATABLE READ in PostgreSQL or SET TRANSACTION ISOLATION LEVEL ... before the transaction in MySQL and SQL Server. Most applications run at their database's default without thinking about it, which is why the anomalies below turn up as rare, hard-to-reproduce bugs.
The anomalies
Dirty read
Reading another transaction's uncommitted change. If it then rolls back, the reader acted on data that never existed. Only read uncommitted allows it.
Non-repeatable read
Reading the same row twice in one transaction and getting different values, because another transaction changed it and committed in between. Read committed allows it, since each statement sees the latest committed data.
Phantom read
Running the same query with a condition twice and getting a different set of rows, because another transaction inserted or deleted matching rows. The SQL standard allows phantoms at repeatable read; snapshot-based databases prevent them there.
Lost update
Two transactions read a value, each computes a new value from it, and both write it back; the second write overwrites the first as if it never happened. This is the classic bug in code like "read the balance, add to it, save it".
Write skew
Two transactions read the same data, each decides it is safe to make a change, and they change different rows. Neither overwrites the other, yet together they break a rule both checked. It was named in the 1995 paper that introduced snapshot isolation, and it is the anomaly that separates snapshot isolation from true serializability.
Which isolation level prevents which anomaly
This table is generated by running every scenario on this page at every level. The last three columns match PostgreSQL 16; other databases differ, as described below.
| Anomaly | Read uncommitted | Read committed | Repeatable read | Serializable |
|---|---|---|---|---|
| Dirty read | Happens | Prevented | Prevented | Prevented |
| Non-repeatable read | Happens | Happens | Prevented | Prevented |
| Phantom read | Happens | Happens | Prevented | Prevented |
| Lost update | Happens | Happens | Error, retry | Error, retry |
| Lost update (atomic UPDATE) | Waits, then correct | Waits, then correct | Error, retry | Error, retry |
| Write skew | Happens | Happens | Happens | Error, retry |
"Error, retry" means the database refused: one transaction failed with a serialization error and has to be run again. "Waits, then correct" means one transaction blocked on a row lock until the other finished, then carried on with the up-to-date value.
The SQL standard and its gaps
SQL-92 defines the four levels by which of three phenomena they allow: dirty reads (P1), non-repeatable reads (P2) and phantoms (P3). Read uncommitted allows all three, read committed prevents P1, repeatable read prevents P1 and P2, and serializable prevents all three.
In 1995, Hal Berenson, Phil Bernstein, Jim Gray, Jim Melton and Elizabeth and Patrick O'Neil showed in "A Critique of ANSI SQL Isolation Levels" that these definitions are ambiguous and incomplete. Preventing the three phenomena does not make a schedule serializable; lost updates and write skew slip through. The paper also described snapshot isolation, which many databases already used, and showed it sits between repeatable read and serializable: it prevents all three standard phenomena and lost updates, but allows write skew.
The practical consequence is that the level names promise less than they seem to, and different databases mean different things by the same name. Martin Kleppmann's Hermitage project tests each database at each level against a list of anomalies, and is the best place to check a specific one.
How databases implement isolation
Locking. The traditional approach, two-phase locking, takes shared locks for reads and exclusive locks for writes, and holds them until the transaction ends. Readers block writers and writers block readers. To prevent phantoms it must also lock ranges of an index, not just rows. Serializable isolation in SQL Server and in MySQL works this way.
Multi-version concurrency control (MVCC). Writers create new versions of rows instead of overwriting them, and each reader sees a snapshot: the versions committed before a certain moment. Readers never block writers or the other way round. Writers still lock rows against other writers. At read committed the snapshot is taken for each statement; at repeatable read, once per transaction.
Serializable snapshot isolation (SSI). MVCC plus tracking of read-write dependencies between concurrent transactions. When the dependencies form a pattern that could not occur in any serial order, the database aborts one transaction. PostgreSQL has used SSI for serializable since version 9.1 (2011), based on work by Michael Cahill, Uwe Röhm and Alan Fekete, and described by Dan Ports and Kevin Grittner. It blocks no more than snapshot isolation, at the cost of occasional false positives that abort transactions unnecessarily.
PostgreSQL, MySQL, SQL Server and Oracle
| Database | Default | Notable behaviour |
|---|---|---|
| PostgreSQL | Read committed | Read uncommitted behaves as read committed. Repeatable read is snapshot isolation and raises an error on concurrent updates to the same row. Serializable uses SSI. |
| MySQL (InnoDB) | Repeatable read | Plain SELECTs read a snapshot, but UPDATE and locking reads see the latest data, and there is no error on concurrent updates, so lost updates and write skew are possible at repeatable read. Serializable turns plain SELECTs into locking reads. |
| SQL Server | Read committed (locking) | Readers take shared locks unless the READ_COMMITTED_SNAPSHOT option is on (it is on by default in Azure SQL Database). A separate SNAPSHOT level gives snapshot isolation. |
| Oracle | Read committed | MVCC throughout. Its SERIALIZABLE level is snapshot isolation, so it allows write skew. |
| SQLite | Serializable | One writer at a time for the whole database, so transactions are serializable. |
Preventing lost updates
Lost updates are the anomaly most likely to hit ordinary application code at the default level. The fixes, roughly from simplest to most general:
- Let the database do the arithmetic:
UPDATE accounts SET balance = balance + 10 WHERE id = 1. The row lock makes the second update wait and apply its change to the new value, as the "Concurrent UPDATE" scenario shows. - Lock before reading:
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE. The second transaction waits at the SELECT until the first commits, then reads the new value. - Optimistic concurrency: keep a version column and write with
UPDATE ... SET balance = 110, version = 8 WHERE id = 1 AND version = 7. If no row was updated, someone else got there first: reload and try again. This works across requests, without holding a transaction open. - A stronger level: at repeatable read or serializable in PostgreSQL, the second writer gets a serialization error instead of overwriting, and the application retries.
Retrying serialization failures
Running at repeatable read or serializable in PostgreSQL means accepting that some transactions will fail with SQLSTATE 40001 (serialization_failure) and must be retried from the start, re-reading everything, because their earlier reads may be out of date. Deadlocks (40P01) are handled the same way. A minimal retry loop in Python with psycopg:
import psycopg
from psycopg import errors
def transfer(conn, src, dst, amount, attempts=5):
for attempt in range(attempts):
try:
with conn.transaction():
conn.execute("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE")
(balance,) = conn.execute(
"SELECT balance FROM accounts WHERE id = %s", (src,)).fetchone()
if balance < amount:
raise ValueError("insufficient funds")
conn.execute("UPDATE accounts SET balance = balance - %s WHERE id = %s", (amount, src))
conn.execute("UPDATE accounts SET balance = balance + %s WHERE id = %s", (amount, dst))
return
except (errors.SerializationFailure, errors.DeadlockDetected):
continue # the transaction was rolled back; run it again
raise RuntimeError("gave up after repeated serialization failures") Keep transactions short so retries are cheap, and never perform side effects outside the database, such as sending an email, inside a transaction that might be retried.
Choosing an isolation level
- Read committed suits most web applications, as long as updates are written atomically or with row locks, and code does not rely on reading the same data twice.
- Repeatable read (snapshot isolation) suits reports and exports that must see one consistent state of the database, and protects against lost updates in PostgreSQL.
- Serializable suits code that checks a condition and then acts on it, such as booking the last seat, enforcing "at least one on call", or keeping a balance from going negative across several rows, provided the application retries failed transactions.
- Read uncommitted is rarely the right choice; its only advantage, reading without waiting for locks, is what MVCC databases give at read committed anyway.
Isolation levels in interviews
Backend and database interviews often ask candidates to name the four levels and the anomaly each one prevents. Stronger answers go further: explain that the standard's table is incomplete, describe lost updates and write skew with a concrete example (double booking, two doctors going off call), say how the database you use implements each level, and show how you would fix a lost update in code. A common follow-up is "how would you stop two users booking the same seat?", where good answers mention a unique constraint, SELECT ... FOR UPDATE, or serializable isolation with retries.
Isolation level questions and answers
What are transaction isolation levels?
Isolation levels set how much concurrent transactions can see of each other’s work. The SQL standard defines four: read uncommitted, read committed, repeatable read and serializable. Lower levels allow more anomalies, such as dirty reads or lost updates, in exchange for less blocking and fewer aborted transactions; serializable guarantees the result is the same as running the transactions one at a time.
What is the difference between read committed and repeatable read?
Under read committed, each statement sees data committed before that statement began, so running the same query twice in a transaction can return different results. Under repeatable read, the transaction keeps reading from one snapshot, so repeated queries return the same rows. In PostgreSQL, repeatable read also refuses to overwrite a row that another transaction changed after the snapshot, raising a serialization error instead.
What is a dirty read?
A dirty read is reading data that another transaction has written but not committed. If that transaction then rolls back, the reader has used a value that never officially existed. Every level except read uncommitted prevents it, and PostgreSQL never allows it, even at read uncommitted.
What is a phantom read?
A phantom read happens when a transaction runs a query with a condition twice and the second run returns extra rows (or fewer) because another transaction inserted or deleted matching rows and committed in between. The standard allows phantoms at repeatable read; PostgreSQL and MySQL’s plain SELECTs avoid them at repeatable read by reading from a snapshot.
What is write skew?
Write skew happens when two transactions each read the same data, make a decision based on it, and update different rows. Each change is fine alone, but together they break a rule both checked, such as at least one doctor being on call. Snapshot isolation, which is PostgreSQL’s repeatable read and Oracle’s serializable, allows it; true serializable isolation prevents it.
What is the default isolation level?
PostgreSQL, SQL Server and Oracle default to read committed. MySQL with InnoDB defaults to repeatable read. SQLite transactions are serializable. Behaviour at the same named level also differs between databases, so check the documentation for the one you use.
How do I prevent lost updates?
Update atomically with UPDATE ... SET x = x + 1 instead of reading and writing back, lock the row first with SELECT ... FOR UPDATE, use an optimistic version column that the UPDATE checks, or run at repeatable read or serializable in PostgreSQL and retry on serialization errors.
Why does serializable isolation cause errors?
Databases that use optimistic serializable isolation, such as PostgreSQL, let transactions run and abort one when they detect that the combined result could not have come from any serial order. The application gets a serialization failure (SQLSTATE 40001) and should retry the whole transaction. Locking implementations block instead, and can deadlock.
Further reading
- PostgreSQL documentationTransaction IsolationExactly what each level does in PostgreSQL, with the serialization errors to expect.
- Berenson and others, 1995A Critique of ANSI SQL Isolation LevelsWhy the standard's definitions fall short, and the paper that defined snapshot isolation.
- Martin KleppmannHermitageTests of real databases at every isolation level, anomaly by anomaly.
- Ports and Grittner, 2012Serializable Snapshot Isolation in PostgreSQLHow PostgreSQL implements serializable isolation.
- MySQL documentationTransaction Isolation LevelsInnoDB's consistent reads, locking reads and next-key locks.
- JepsenConsistency modelsHow isolation levels relate to each other and to distributed consistency.