Jonas Notes

Levels of Isolation

5 min read

Levels of Isolation

Isolation levels define what one transaction is allowed to observe while other transactions are running concurrently.

The clean mental model:

  • MVCC is an implementation technique.
  • Isolation level is the behavioral contract.
  • Different databases implement the same isolation-level names differently, so always check the specific system.

Common anomalies

Dirty read

A transaction reads data written by another transaction that has not committed yet.

-- T1
BEGIN;
UPDATE products SET stock = stock - 10 WHERE id = 1;
-- not committed yet
 
-- T2
SELECT stock FROM products WHERE id = 1;
-- if T2 sees T1's uncommitted stock change, this is a dirty read

If T1 rolls back, T2 read a value that never really became part of the database.

Non-repeatable read

A transaction reads the same row twice and sees different committed values because another transaction committed between the two reads.

-- T1
BEGIN;
SELECT price FROM products WHERE id = 1; -- 100
 
-- T2
UPDATE products SET price = 120 WHERE id = 1;
COMMIT;
 
-- T1
SELECT price FROM products WHERE id = 1; -- 120 under READ COMMITTED
COMMIT;

Phantom read

A transaction reruns the same predicate/range query and sees a different set of matching rows because another transaction inserted, deleted, or updated rows that match the predicate.

-- T1
BEGIN;
SELECT * FROM orders WHERE customer_id = 123; -- 2 rows
 
-- T2
INSERT INTO orders (id, customer_id) VALUES (456, 123);
COMMIT;
 
-- T1
SELECT * FROM orders WHERE customer_id = 123; -- 3 rows if phantoms are allowed
COMMIT;

Write skew

Two transactions read overlapping data, make individually valid decisions, and write different rows, producing a globally invalid result.

Example rule: at least one doctor must be on call.

Initial state:
Alice on_call = true
Bob   on_call = true
 
T1 sees two doctors on call and sets Alice off call.
T2 sees two doctors on call and sets Bob off call.
 
Final state:
Alice on_call = false
Bob   on_call = false

Each transaction looked correct from its own snapshot, but there is no serial order where both transactions should have been allowed to commit.

1. Read Uncommitted

Weakest isolation. Allows dirty reads.

Most serious MVCC databases do not expose true dirty reads. For example, PostgreSQL accepts the READ UNCOMMITTED name but treats it like READ COMMITTED.

Use this mainly as a theory/interview term.

2. Read Committed

Prevents dirty reads, but allows non-repeatable reads and phantom reads.

MVCC mental model:

  • Each statement gets a fresh snapshot of committed data.
  • You never read another transaction’s uncommitted write.
  • Two SELECTs inside the same transaction may see different committed data.

Example:

T1 first SELECT sees balance = 100.
T2 updates balance to 200 and commits.
T1 second SELECT sees balance = 200.

This is a non-repeatable read, not a dirty read.

PostgreSQL’s default isolation level is READ COMMITTED.

3. Repeatable Read

Prevents dirty reads and non-repeatable reads. In snapshot-based systems, the transaction reads from one stable snapshot.

MVCC mental model:

  • The transaction uses a stable snapshot, usually established at the first read.
  • Re-reading the same row returns the same version.
  • New committed rows from other transactions do not suddenly appear in ordinary snapshot reads.

Example:

T1 first SELECT sees balance = 100.
T2 updates balance to 200 and commits.
T1 second SELECT still sees balance = 100.

Important nuance:

  • In the SQL standard, REPEATABLE READ is often described as still allowing phantom reads.
  • PostgreSQL REPEATABLE READ prevents phantom reads for ordinary reads because it uses a stable transaction snapshot.
  • InnoDB REPEATABLE READ also uses consistent reads and has additional locking behavior, such as next-key locks for locking reads.

So the interview-safe answer is: “Repeatable Read prevents non-repeatable reads; phantom behavior depends on the database implementation.”

4. Serializable

Strongest isolation level.

Guarantee:

The final committed result must be equivalent to some one-at-a-time ordering of the transactions.

Serializable is stronger than Repeatable Read because a stable snapshot alone does not prevent all anomalies. In particular, snapshot isolation can still allow write skew.

Doctor-on-call example:

Initial state:
Alice and Bob are both on call.
 
T1 reads: two doctors are on call.
T2 reads: two doctors are on call.
 
T1 sets Alice off call.
T2 sets Bob off call.

Under snapshot isolation / some Repeatable Read implementations, both transactions may commit because they wrote different rows.

Under Serializable isolation, one transaction must be blocked, aborted, or retried because there is no serial order that explains both transactions seeing two doctors on call and both committing.

Modern MVCC databases often implement Serializable as:

  • MVCC snapshot visibility
  • plus conflict detection, predicate locks, dependency tracking, next-key locks, or similar mechanisms

Application code must be prepared to retry transactions that fail with serialization errors.

Summary table

Isolation levelDirty readsNon-repeatable readsPhantom readsNotes
READ UNCOMMITTEDAllowed in theoryAllowedAllowedOften not truly supported by MVCC databases
READ COMMITTEDPreventedAllowedAllowedSnapshot per statement
REPEATABLE READPreventedPreventedDepends on DBSnapshot per transaction in MVCC systems
SERIALIZABLEPreventedPreventedPreventedEquivalent to some serial order; may abort/retry

Interview answer

Say this:

MVCC keeps multiple row versions so readers can read a consistent snapshot without blocking writers. The isolation level defines which snapshot a transaction sees and what anomalies are allowed. In Read Committed, each statement sees a fresh committed snapshot, so dirty reads are prevented but non-repeatable reads and phantoms can happen. In Repeatable Read, the transaction sees a stable snapshot, so repeated reads are consistent. Serializable is stronger: the database guarantees the final result is equivalent to some serial execution, often by detecting conflicts and aborting one transaction when concurrent reads and writes would create an impossible ordering.

Backlinks