Skip to content
Tutorials Center
My learning

Topics

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
SQL
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:

SQL Lost update replayed step by step ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

SQL You always see your own uncommitted changes ยท database: company

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 COMMITTED is 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.