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.

What is the SQL Transaction Isolation Levels?

Reference for the four ANSI SQL transaction isolation levels with the read anomalies each prevents (dirty, non-repeatable, phantom) and the default level per major database. It runs free in your browser on Gera Tools, with nothing uploaded.

SQL Transaction Isolation Levels

Name: SQL Transaction Isolation Levels
Creator: Gera Tools
License: https://creativecommons.org/licenses/by/4.0/

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.

Anomaly grid

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.

What each anomaly means in practice

Dirty read — Transaction A reads a row that Transaction B has modified but not yet committed. If B rolls back, A has seen data that never officially existed. Classic scenario: a reservation system where A reads a seat as “booked” but B is mid-cancellation and rolls back.

Non-repeatable read — Transaction A reads a row, then reads it again and sees a different value because B committed an update in between. Classic scenario: an audit report that reads the same account balance twice during its run and gets different numbers.

Phantom read — Transaction A queries a set of rows, then re-queries and sees additional rows because B inserted matching rows and committed. Classic scenario: a report counting active users that grows mid-run because new signups are committed by concurrent transactions.

Setting the level

-- Per-transaction (most portable)
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
-- ... your queries ...
COMMIT;

-- Per-session (PostgreSQL)
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- Per-session (MySQL)
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

Default levels by database

Database	Default level	Implementation notes
PostgreSQL	Read Committed	Repeatable Read uses MVCC snapshots; Serializable uses SSI
MySQL InnoDB	Repeatable Read	Next-key locking prevents most phantoms at this level
SQL Server	Read Committed	RCSI (snapshot) available; Read Uncommitted usable for reporting
Oracle	Read Committed	Only supports Read Committed and Serializable
SQLite	Serializable	Single-writer model; isolation is implicit

Practical guidance

Read Committed is the correct default for most OLTP workloads. It avoids dirty reads while keeping concurrency high.
Repeatable Read is worth enabling for long-running transactions that re-read the same rows, such as complex reporting jobs.
Serializable is appropriate for financial transactions with correctness invariants that span multiple rows (e.g., double-entry accounting). Design for retry loops at this level, because the engine may abort conflicting transactions.
Read Uncommitted is almost never useful in production. It lets you read dirty data, which usually causes more bugs than the marginal throughput gain is worth. PostgreSQL silently upgrades it to Read Committed anyway.