Transaction Isolation Levels

Transaction Isolation Levels

Definition: The ANSI SQL standard levels, Read Uncommitted, Read Committed, Repeatable Read, Serializable, that define how much of a concurrent transaction’s uncommitted or in-progress work another transaction is allowed to see.

How It Works

  • Read Uncommitted: no isolation. A transaction can see another transaction’s uncommitted writes (a “dirty read”). Rarely used; PostgreSQL doesn’t even implement it distinctly from Read Committed.
  • Read Committed: a query only ever sees data committed before that query started. Prevents dirty reads, but two queries in the same transaction can see different data if something else commits in between (a “non-repeatable read”). The default in PostgreSQL, Oracle, and SQL Server.
  • Repeatable Read: a whole transaction sees one consistent snapshot from when it started. Prevents dirty and non-repeatable reads. In some engines (MySQL InnoDB) it also prevents phantom reads via gap locking; in the ANSI standard itself, phantoms are still technically allowed at this level. The default in MySQL InnoDB.
  • Serializable: transactions behave as though they ran one at a time in some serial order, even though they actually ran concurrently. Prevents every standard anomaly, at the cost of aborted/retried transactions under contention.

Under the Hood

The same scenario, two transactions both touching account rows where region = 'EU', run at each isolation level:

Worked example 1: dirty read at Read Uncommitted

  • Given: account 1 has 500.T2beginsupdatingitto500. T2 begins updating it to 900 but hasn’t committed.
  • Step: T1, running at Read Uncommitted, reads account 1 and sees $900.
  • Step: T2 hits a constraint violation and rolls back, account 1 reverts to $500.
  • Answer: T1 made a decision based on a value that never actually existed in committed history. This is why Read Uncommitted is almost never used in practice, it trades correctness for a level of concurrency the other levels already provide safely.

Worked example 2: non-repeatable read at Read Committed

  • Given: T1 runs two SELECTs inside one transaction, several seconds apart, at Read Committed.
  • Step: between the two SELECTs, T2 commits a change to the row T1 is reading.
  • Answer: T1’s second SELECT sees the new value even though nothing in T1 itself changed, if T1’s logic assumed the value was stable across both reads, that assumption is now broken.

Worked example 3: phantom read

  • Given: T1 runs SELECT COUNT(*) FROM orders WHERE status = 'pending' at Repeatable Read, getting 40.
  • Step: T2 inserts a new row with status = 'pending' and commits.
  • Step: T1 runs the identical query again in the same transaction.
  • Answer: under strict ANSI Repeatable Read, T1 could see 41, a “phantom” row that wasn’t there before, because Repeatable Read only guarantees existing rows won’t change, not that no new matching rows can appear. PostgreSQL’s actual Repeatable Read (snapshot-based) prevents this by using one fixed snapshot for the whole transaction; MySQL InnoDB prevents it differently, using gap locks on the range being read.

Worked example 4: write skew, an anomaly Serializable alone catches

  • Given: a hospital rule requires at least one on-call doctor at all times. Two doctors, Alice and Bob, are both currently on call. Each independently, in their own transaction at Repeatable Read, checks “is at least one other doctor on call?”, sees yes, and sets themselves to off-call.
  • Step: both transactions read overlapping data (the on-call table), and each one’s individual write is valid given what it read.
  • Step: both commit. Now zero doctors are on call, the invariant is violated, even though neither transaction saw a dirty, non-repeatable, or phantom read.
  • Answer: standard Repeatable Read (snapshot isolation) does not catch this, it only checks each transaction’s own reads and writes in isolation. True Serializable, implemented via Serializable Snapshot Isolation in Postgres or true locking in other engines, detects the conflicting overlap and aborts one of the two transactions, forcing a retry.

Anomaly Reference

  • Dirty read: reading another transaction’s uncommitted write, which might later be rolled back.
  • Non-repeatable read: re-reading the same row within one transaction and getting a different value because another transaction committed a change in between.
  • Phantom read: re-running the same range query within one transaction and getting a different set of rows, because another transaction inserted or deleted a matching row in between.
  • Lost update: two transactions both read a value, both compute a new value based on it, and the second write silently overwrites the first, losing the first transaction’s change entirely.
  • Write skew: two transactions each make a locally-valid write based on overlapping reads, but the combined effect violates an invariant that spans both, see the worked example above.

Why It Matters

Isolation level is a direct dial between correctness and concurrency. Too weak, and rare-but-real race conditions corrupt data in ways that are painful to reproduce and debug. Too strict, and throughput drops as transactions block each other or get aborted and retried under contention. Picking the right level for each workload, not just accepting the default everywhere, is one of the highest-leverage decisions in transactional application design.

Common Pitfalls

  • Defaulting to whatever the database ships with (Read Committed in Postgres, Repeatable Read in MySQL) without considering whether the specific operation, like a decrement-then-check-then-commit inventory deduction, needs stronger guarantees.
  • Assuming “Repeatable Read” means “no phantom reads” everywhere. The ANSI standard explicitly allows phantoms at this level; only specific engines’ implementations happen to prevent them.
  • Reaching for Serializable everywhere “to be safe,” then being surprised by serialization failures under load that the application isn’t set up to retry.
  • Believing isolation levels prevent lost updates automatically. A classic read-modify-write race (balance = read(); write(balance - 10)) can still lose an update at Read Committed even without a dirty or non-repeatable read, because the read and write aren’t atomic together, this needs SELECT ... FOR UPDATE or an atomic UPDATE ... SET balance = balance - 10.
  • Not testing under real concurrency. Isolation bugs are invisible in single-threaded manual testing and only show up under simultaneous load, exactly when they’re hardest to debug in production.
  • Forgetting that isolation level is usually set per-session or per-transaction, not globally fixed, SET TRANSACTION ISOLATION LEVEL SERIALIZABLE before a single sensitive operation is often better than changing the database-wide default.
  • Treating locking hints (SELECT ... FOR UPDATE) and isolation level as interchangeable. They solve overlapping but different problems, explicit row locks prevent concurrent writers from proceeding at all, while isolation level controls what a reader is allowed to see.

Choosing a Level in Practice

A rough guide for picking a level per operation, rather than per database:

  • Read Committed: the safe default for most CRUD operations where each statement’s freshness matters more than transaction-wide consistency, dashboards, simple form submissions, most reporting.
  • Repeatable Read: multi-step business logic that reads a value more than once and needs it to stay consistent within the transaction, calculating a total from several reads, generating a report from a stable point-in-time view.
  • Serializable: financial transfers, inventory deduction under contention, booking/reservation systems, anywhere a lost update or write skew would be a real, costly bug rather than a cosmetic inconsistency, and where the application is prepared to retry on serialization failure.
  • Explicit row locking (SELECT ... FOR UPDATE): an alternative to raising isolation level when only a specific known row (or small set of rows) needs protection, cheaper than serializing the whole transaction.

Comparison

LevelDirty ReadNon-Repeatable ReadPhantom ReadTypical cost
Read UncommittedPossiblePossiblePossibleLowest (rarely used)
Read CommittedPreventedPossiblePossibleLow, high concurrency
Repeatable ReadPreventedPreventedPossible (ANSI) / Prevented (Postgres, MySQL)Moderate
SerializablePreventedPreventedPreventedHighest, more aborts/retries

Note that this table only covers the standard ANSI anomalies. Write skew and lost updates are not fully captured by the dirty/non-repeatable/phantom read columns, which is precisely why Serializable is defined as “equivalent to some serial execution” rather than as “prevents these three named anomalies,” it’s a stronger, more general guarantee than the table format can fully represent.

Example

PostgreSQL’s Repeatable Read is stricter than the ANSI minimum, it’s implemented as true snapshot isolation, so phantom reads are already prevented without reaching for Serializable. MySQL InnoDB achieves a similar practical result at Repeatable Read using next-key locking (row locks plus gap locks) rather than pure snapshotting. Applications that need genuine serializability, like double-booking prevention in a reservation system, still need to explicitly request SERIALIZABLE and handle the resulting serialization-failure retries in application code.

Oracle Database’s default “Read Committed” and optional “Serializable” are both implemented via multiversioning against undo segments rather than locking, meaning Oracle never blocks a reader behind a writer at any isolation level, a different implementation path to a similar isolation contract as PostgreSQL’s snapshot-based approach.

SQL Server additionally offers a non-standard “Snapshot” isolation level, distinct from its standard “Serializable,” which gives MVCC-style consistency without the ANSI-standard locking behavior, a useful reminder that vendor-specific levels sometimes sit outside the four-level ANSI model entirely.

Referenced by

Dig deeper