What are the four ANSI SQL isolation levels?

From weakest to strongest: Read Uncommitted, Read Committed, Repeatable Read and Serializable. Each higher level prevents more concurrency anomalies at the cost of more locking or aborts, so stronger isolation trades throughput for correctness.

What is a dirty read?

A dirty read is when a transaction reads data written by another transaction that has not yet committed. If that other transaction rolls back, the reader has seen a value that never officially existed. Only Read Uncommitted permits dirty reads in the ANSI model.

What is the difference between a non-repeatable read and a phantom read?

A non-repeatable read is re-reading the same row and getting a different value because another transaction updated it. A phantom read is re-running the same query and getting different rows because another transaction inserted or deleted matching rows. Repeatable Read stops the former; Serializable stops both.

What isolation level does PostgreSQL default to?

PostgreSQL defaults to Read Committed. Its Repeatable Read is implemented with snapshot isolation, which also blocks phantoms in most cases, and its Serializable uses Serializable Snapshot Isolation (SSI) to abort transactions that would break serial order.

Why does MySQL InnoDB default to Repeatable Read?

InnoDB chose Repeatable Read as its default and uses next-key locking so that, unlike the plain ANSI definition, it also prevents most phantom reads. This gives stronger guarantees than Read Committed while remaining the historical default for replication consistency.

SQL Transaction Isolation Levels

Email me this result

Get this tool's output sent to your inbox, plus one useful tool a week. No spam, unsubscribe any time.

Choosing an isolation level

A transaction’s isolation level controls how much it is shielded from concurrent transactions. The ANSI SQL standard defines four levels — Read Uncommitted, Read Committed, Repeatable Read and Serializable — each ruling out more read anomalies as you go stronger, in exchange for more locking, blocking or aborts. This reference shows which anomalies each level allows and the default level chosen by the major databases.

How it works

The standard is defined by three anomalies a level may permit:

Level             Dirty read   Non-repeatable   Phantom
Read Uncommitted  possible     possible         possible
Read Committed    prevented    possible         possible
Repeatable Read   prevented    prevented        possible*
Serializable      prevented    prevented        prevented

* Several engines (PostgreSQL snapshot, MySQL InnoDB next-key locks) also block phantoms at Repeatable Read, exceeding the bare ANSI requirement. Set it per session or per transaction:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN; -- ... ; COMMIT;

Tips and notes

Stronger isolation costs concurrency: Serializable may abort transactions you must retry, so design for retry loops at that level.
“Repeatable Read prevents phantoms” depends on the engine — true in InnoDB and PostgreSQL snapshots, not guaranteed by ANSI alone.
Read Committed is the pragmatic default for most OLTP workloads.
Read Uncommitted is rarely useful and is effectively unavailable in PostgreSQL (it behaves as Read Committed).
For correctness-critical invariants spanning multiple rows, prefer Serializable or explicit row locks over hand-rolled checks at weaker levels.