Isolation Levels

When multiple transactions run at the same time against the same data, the database has to decide how much they are allowed to see of each other's uncommitted or concurrent work. That decision is a trade-off: stricter isolation gives every transaction a cleaner, more predictable view of the data, but it does so by locking more and letting fewer things run in parallel. Looser isolation allows more concurrency and better throughput, at the cost of transactions occasionally seeing surprising results.

The SQL standard defines four isolation levels, ordered from loosest to strictest, each one preventing a specific set of "anomalies" that concurrent transactions can otherwise produce.

The three classic anomalies

Anomaly	What happens
Dirty read	A transaction reads data written by another transaction that has not committed yet — and that data may later be rolled back, meaning you read something that never really existed.
Non-repeatable read	A transaction reads the same row twice and gets different values, because another transaction committed an update to that row in between.
Phantom read	A transaction re-runs the same query twice and gets a different set of rows, because another transaction committed an insert or delete that changed which rows match.

The four standard isolation levels

Level	Dirty reads	Non-repeatable reads	Phantom reads
READ UNCOMMITTED	Possible	Possible	Possible
READ COMMITTED	Prevented	Possible	Possible
REPEATABLE READ	Prevented	Prevented	Possible
SERIALIZABLE	Prevented	Prevented	Prevented

Each level up the table prevents strictly more anomalies than the one before it, and generally costs more in terms of locking, blocking, or the database having to detect and reject conflicting transactions.

Setting the isolation level

Choosing a level for one transaction

SQL

BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

SELECT balance FROM accounts WHERE id = 1;
-- ... other work ...
SELECT balance FROM accounts WHERE id = 1;
-- guaranteed to return the same balance both times

COMMIT;

PostgreSQL does not implement all four levels literally

PostgreSQL only has three distinct internal implementations: requesting READ UNCOMMITTED is silently treated as READ COMMITTED (it never actually allows dirty reads), and its REPEATABLE READ already prevents phantom reads using a snapshot-based mechanism, which is stronger than the ANSI standard strictly requires for that level. Its SERIALIZABLE is implemented via a technique called serializable snapshot isolation, which detects conflicting transactions and forces one to retry rather than using heavy locking throughout. Other databases (SQL Server, MySQL/InnoDB) do implement all four levels more literally, so always check your specific database's documentation rather than assuming the standard's guarantees apply verbatim.

Serialization failures

At the strictest level, a database may not be able to guarantee correctness through locking alone without hurting concurrency too much. Instead, it lets transactions proceed optimistically and aborts one of them if it later detects that the concurrent execution could not have produced the same result as running the transactions one at a time. Application code that uses SERIALIZABLE needs to be prepared to catch this error and simply retry the transaction.

READ COMMITTED is the practical default

Most applications never change the isolation level and run on their database's default, which for PostgreSQL, Oracle, and SQL Server is READ COMMITTED. It eliminates dirty reads — the anomaly most likely to cause visibly wrong data — while still allowing high concurrency. Reach for REPEATABLE READ or SERIALIZABLE only when a specific operation genuinely needs a stable, consistent snapshot across multiple statements, such as financial reconciliation or report generation that must not see partial updates.