SQL Reference ยท Transactions and Performance
SQL transaction isolation levels
SQL transaction isolation levels explained: dirty reads, non-repeatable reads, phantoms, lost updates and write skew, the four standard levels and each database's defaults.
Isolation decides how much one running transaction can see of another's work. Perfect isolation makes concurrent transactions behave as if they ran one after another. That is safe but limits throughput, so databases let you pick a weaker isolation level that allows some surprises in exchange for less waiting.
You set the level per transaction (or per session). The SQL standard defines four levels in terms of three read anomalies. Real databases add subtleties, so the same level name can behave differently between products.
The anomalies
| Anomaly | What happens |
|---|---|
| Dirty read | You read data another transaction has changed but not committed. If it rolls back, you saw something that never existed. |
| Non-repeatable read | You read a row twice in one transaction and get different values because another transaction committed a change in between. |
| Phantom read | You run the same query twice and the second run returns extra (or fewer) rows because another transaction inserted or deleted matching rows. |
| Lost update | Two transactions read a value, both compute a new one and both write; the second write overwrites the first. |
| Write skew | Two transactions read overlapping data, each updates a different row based on what it read, and together they break a rule neither broke alone (for example, two doctors both go off call, leaving nobody on call). |
The four standard levels
| Level | Dirty read | Non-repeatable read | Phantom read |
|---|---|---|---|
READ UNCOMMITTED |
Possible | Possible | Possible |
READ COMMITTED |
No | Possible | Possible |
REPEATABLE READ |
No | No | Possible (by the standard) |
SERIALIZABLE |
No | No | No |
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; -- before the first statement START TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- PostgreSQL, MySQL
Seeing the problem without a second session
A browser example has one connection, so it cannot truly run two transactions at once, but you can replay the interleaving by hand. This one reproduces a lost update caused by reading first and writing a stale value afterwards, then fixes it with a single atomic statement:
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Inside a transaction you always see your own changes. Whether other sessions can see them before the commit is what the isolation level controls.
How databases implement it
- Locking: readers take shared locks and writers take exclusive locks, so conflicting sessions wait. Simple to reason about, but readers and writers block each other.
- Multiversion concurrency control (MVCC): each write creates a new row version and each reader sees a consistent snapshot. Readers do not block writers. PostgreSQL, Oracle, and MySQL's InnoDB work this way, and SQL Server can with snapshot options.
Dialect differences
| Database | Default | Notes |
|---|---|---|
| PostgreSQL | READ COMMITTED |
READ UNCOMMITTED behaves as READ COMMITTED. REPEATABLE READ is snapshot isolation (no phantoms, but write skew is possible). SERIALIZABLE detects conflicts and aborts a transaction with a serialization failure that you must retry. |
| MySQL (InnoDB) | REPEATABLE READ |
Uses a snapshot for plain reads and next-key locks for locking reads. READ UNCOMMITTED really allows dirty reads. |
| SQL Server | READ COMMITTED (locking), or snapshot-based when READ_COMMITTED_SNAPSHOT is on (the default in Azure SQL Database) |
Adds a SNAPSHOT level. |
| Oracle | READ COMMITTED |
Offers only READ COMMITTED, SERIALIZABLE (snapshot isolation in practice) and READ ONLY. |
| SQLite | SERIALIZABLE |
One writer at a time. PRAGMA read_uncommitted = 1 allows dirty reads only between connections that share a cache. |
Usage notes
- Pick the weakest level that is correct.
READ COMMITTEDis fine for most pages; use stronger levels for money, inventory and uniqueness rules. - Retry on failure. Higher levels (and deadlocks) abort transactions with errors such as serialization failures; your code must retry the whole transaction.
- Prefer atomic statements (
SET qty = qty - 1) and constraints over relying on a high isolation level. - Explicit locks (
SELECT ... FOR UPDATE) fix specific cases without raising the level for everything. See locking and deadlocks. - Long transactions hurt. Under MVCC they keep old row versions alive, and under locking they hold locks.