The three read phenomena
Isolation levels exist to control which of these three things a transaction is allowed to see while other transactions are running concurrently against the same data.
- Dirty read — reading a value that another transaction has changed but not yet committed. If that other transaction rolls back, you read a value that never actually existed.
- Non-repeatable read — reading the same row twice in one transaction and getting two different values, because another transaction committed a change to it in between.
- Phantom read — re-running the same filtered query twice in one transaction and getting a different set of rows, because another transaction inserted or deleted rows matching the filter in between.
The four isolation levels
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
-- statements in this transaction now run at REPEATABLE READ
COMMIT;READ UNCOMMITTED is the loosest level — it permits dirty reads and is rarely used in practice; some databases, including PostgreSQL, accept the syntax but silently treat it identically to READ COMMITTED. READ COMMITTED guarantees you never see another transaction's uncommitted changes, but each statement re-reads current committed data, so the same row can change value between two reads in the same transaction. REPEATABLE READ locks in the values of rows you've already read for the rest of the transaction. SERIALIZABLE is the strictest: the database guarantees the outcome is equivalent to running all transactions one after another, even though they actually execute concurrently.
What each level prevents
| Isolation Level | Dirty Read | Non-Repeatable Read | Phantom Read |
|---|---|---|---|
| READ UNCOMMITTED | Possible | Possible | Possible |
| READ COMMITTED | Prevented | Possible | Possible |
| REPEATABLE READ | Prevented | Prevented | Possible* |
| SERIALIZABLE | Prevented | Prevented | Prevented |
*In practice, PostgreSQL's implementation of REPEATABLE READ (built on snapshot isolation) also prevents phantom reads, going beyond what the ANSI standard strictly requires at that level — a detail worth checking against your specific database's documentation rather than assuming the standard table applies exactly everywhere.
Default isolation level, by database
| Database | Default isolation level |
|---|---|
| PostgreSQL | READ COMMITTED |
| MySQL (InnoDB) | REPEATABLE READ |
| Oracle | READ COMMITTED |
| SQL Server | READ COMMITTED |
MySQL's InnoDB engine is the one exception here, shipping with a stricter default than the other three major databases. This matters in practice: application code written and tested against PostgreSQL's READ COMMITTED behavior can behave subtly differently if the same logic runs against MySQL's default REPEATABLE READ, particularly around what a long-running transaction sees change (or not) around it.
Key takeaways
- Three read phenomena — dirty read, non-repeatable read, phantom read — get progressively harder to trigger as isolation level increases.
- READ COMMITTED prevents dirty reads only; REPEATABLE READ also locks in previously-read row values; SERIALIZABLE prevents all three.
- PostgreSQL, Oracle, and SQL Server default to READ COMMITTED; MySQL's InnoDB defaults to the stricter REPEATABLE READ.
- Stricter isolation generally costs more in locking, blocking, or transaction retries — pick the loosest level that still gives correct results for your workload.