Isolation Levels

Isolation defines what one transaction can see of the work of other transactions running at the same time. Stronger isolation removes anomalies but causes more blocking or more transaction aborts.

The SQL standard defines four levels by the anomalies they permit. PostgreSQL, MySQL, Oracle and SQL Server implement them differently, so the actual behaviour matters more than the names.

Every multi-session example below uses two sessions, A and B, running against these tables (created in Example 1). Session output was captured from two real psql sessions on PostgreSQL 16.

Example 1: Setting and checking the isolation level

CREATE TABLE accounts (
  id      int PRIMARY KEY,
  owner   text NOT NULL,
  balance numeric(10,2) NOT NULL CHECK (balance >= 0)
);
INSERT INTO accounts VALUES (1, 'Asha', 500), (2, 'Ben', 300);

CREATE TABLE stock (
  item    text PRIMARY KEY,
  qty     int NOT NULL,
  version int NOT NULL DEFAULT 1
);
INSERT INTO stock VALUES ('widget', 10, 1);

CREATE TABLE doctors (name text PRIMARY KEY, on_call boolean NOT NULL);
INSERT INTO doctors VALUES ('Alice', true), ('Bob', true);

SHOW default_transaction_isolation;

BEGIN ISOLATION LEVEL REPEATABLE READ;
SHOW transaction_isolation;
COMMIT;

BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SHOW transaction_isolation;
COMMIT;

 default_transaction_isolation 
-------------------------------
 read committed
(1 row)

 transaction_isolation 
-----------------------
 repeatable read
(1 row)

 transaction_isolation 
-----------------------
 serializable
(1 row)

The level is chosen per transaction with BEGIN ISOLATION LEVEL ... or SET TRANSACTION ISOLATION LEVEL ... (before the first query). SET default_transaction_isolation = '...' changes the default for a session.

Example 2: Anomalies and levels, standard versus PostgreSQL

The anomalies:

What the SQL standard allows at each level:

What PostgreSQL actually does:

The standard's table does not mention lost update or write skew; the later examples show them. PostgreSQL REPEATABLE READ is stronger than the standard requires because it is snapshot isolation (Example 6).

Example 3: Dirty read (not possible in PostgreSQL)

Session A changes a balance without committing. Session B asks for READ UNCOMMITTED and still sees the committed value, because PostgreSQL treats READ UNCOMMITTED as READ COMMITTED.

-- Session A (not committed)
BEGIN;
UPDATE accounts SET balance = 0 WHERE id = 1;

-- Session B (asks for READ UNCOMMITTED)
BEGIN ISOLATION LEVEL READ UNCOMMITTED;
SELECT balance FROM accounts WHERE id = 1;
COMMIT;

 balance 
---------
  500.00
(1 row)

-- Session A
ROLLBACK;

Session B saw 500.00, not 0.00. MySQL InnoDB, SQL Server and other engines do allow dirty reads at READ UNCOMMITTED; Oracle has no such level.

Example 4: Non-repeatable read

At READ COMMITTED every statement gets a fresh view of committed data. Session B reads the same row twice in one transaction and gets two different answers:

-- Session B
BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT balance FROM accounts WHERE id = 1;

 balance 
---------
  500.00
(1 row)

-- Session A (autocommit)
UPDATE accounts SET balance = 450 WHERE id = 1;

-- Session B (same query, different result)
SELECT balance FROM accounts WHERE id = 1;
COMMIT;

 balance 
---------
  450.00
(1 row)

At REPEATABLE READ the transaction keeps the view it had at its first query:

-- Session B
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1;

 balance 
---------
  500.00
(1 row)

-- Session A (autocommit)
UPDATE accounts SET balance = 450 WHERE id = 1;

-- Session B (the snapshot ends with the transaction)
SELECT balance FROM accounts WHERE id = 1;
COMMIT;
SELECT balance FROM accounts WHERE id = 1;

 balance 
---------
  500.00
(1 row)

 balance 
---------
  450.00
(1 row)

Example 5: Phantom read

A phantom is a new row that matches a condition already queried. At READ COMMITTED the count changes inside the transaction:

-- Session B
BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT count(*) FROM accounts WHERE balance >= 300;

 count 
-------
     2
(1 row)

-- Session A (autocommit)
INSERT INTO accounts VALUES (3, 'Chen', 400);

-- Session B (a new row appears)
SELECT count(*) FROM accounts WHERE balance >= 300;
COMMIT;

 count 
-------
     3
(1 row)

At REPEATABLE READ PostgreSQL returns the same count, so phantoms do not occur (the standard would allow them):

-- Session B
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM accounts WHERE balance >= 300;

 count 
-------
     2
(1 row)

-- Session A (autocommit)
INSERT INTO accounts VALUES (3, 'Chen', 400);

-- Session B (no phantom)
SELECT count(*) FROM accounts WHERE balance >= 300;
COMMIT;

 count 
-------
     2
(1 row)

Example 6: Snapshot isolation

PostgreSQL implements REPEATABLE READ as snapshot isolation. A snapshot is the set of transactions that had committed at one moment. Row versions written by anything else are invisible (see MVCC in the internals lessons).

Readers never block writers and writers never block readers. Snapshot isolation still allows write skew (Example 11).

MySQL InnoDB defaults to REPEATABLE READ and also reads from a snapshot for plain SELECT. For locking reads and writes (SELECT ... FOR UPDATE, UPDATE, DELETE) it uses next-key locks, which lock index records and the gaps between them to block inserts into the range. PostgreSQL has no gap locks. Oracle and PostgreSQL default to READ COMMITTED; SQL Server also defaults to READ COMMITTED, using locks unless read-committed snapshot is enabled.

Example 7: Lost update

Two sessions each read qty = 10, compute 10 - 1 in the application, and write 9. Two sales happened but the stock dropped by one.

-- Session A (application reads 10)
BEGIN;
SELECT qty FROM stock WHERE item = 'widget';

 qty 
-----
  10
(1 row)

-- Session B (application reads 10)
BEGIN;
SELECT qty FROM stock WHERE item = 'widget';

 qty 
-----
  10
(1 row)

-- Session A (writes 10 - 1)
UPDATE stock SET qty = 9 WHERE item = 'widget';
COMMIT;

-- Session B (writes 10 - 1)
UPDATE stock SET qty = 9 WHERE item = 'widget';
COMMIT;

-- Session A (two sales, one decrement)
SELECT qty FROM stock WHERE item = 'widget';

 qty 
-----
   9
(1 row)

The final value should be 8.

Example 8: Fix with an atomic UPDATE

Let the database compute the new value from the current row. The second UPDATE waits for the first to commit, then applies to the new value.

-- Session A
BEGIN;
UPDATE stock SET qty = qty - 1 WHERE item = 'widget';

-- Session B (blocks on A's row lock) [blocks]
BEGIN;
UPDATE stock SET qty = qty - 1 WHERE item = 'widget';

-- Session A
COMMIT;

-- Session B: the blocked statement now completes

-- Session B
COMMIT;
SELECT qty FROM stock WHERE item = 'widget';

 qty 
-----
   8
(1 row)

This is the simplest fix and works at READ COMMITTED. It applies when the new value is an expression of the old one.

Example 9: Fix with SELECT FOR UPDATE

When the application must read, decide, and then write, lock the row at read time. The second SELECT ... FOR UPDATE waits, then returns the value committed by A.

-- Session A (reads 10 and locks the row)
BEGIN;
SELECT qty FROM stock WHERE item = 'widget' FOR UPDATE;

 qty 
-----
  10
(1 row)

-- Session B (blocks) [blocks]
BEGIN;
SELECT qty FROM stock WHERE item = 'widget' FOR UPDATE;

-- Session A
UPDATE stock SET qty = 9 WHERE item = 'widget';
COMMIT;

-- Session B: the blocked statement now completes

 qty 
-----
   9
(1 row)

-- Session B (computed from the fresh value 9)
UPDATE stock SET qty = 8 WHERE item = 'widget';
COMMIT;
SELECT qty FROM stock WHERE item = 'widget';

 qty 
-----
   8
(1 row)

Example 10: Fix with optimistic locking

A version column detects that a row changed after it was read. The write includes AND version = <version read>; zero affected rows means a conflict, and the application re-reads and retries. No lock is held between the read and the write.

SELECT qty, version FROM stock WHERE item = 'widget';

-- client 1 writes first
UPDATE stock SET qty = 9, version = version + 1
WHERE item = 'widget' AND version = 1
RETURNING qty, version;

-- client 2 also read version 1: no row matches
UPDATE stock SET qty = 9, version = version + 1
WHERE item = 'widget' AND version = 1
RETURNING qty, version;

-- client 2 re-reads and retries with the current version
SELECT qty, version FROM stock WHERE item = 'widget';
UPDATE stock SET qty = 8, version = version + 1
WHERE item = 'widget' AND version = 2
RETURNING qty, version;

 qty | version 
-----+---------
  10 |       1
(1 row)

 qty | version 
-----+---------
   9 |       2
(1 row)

 qty | version 
-----+---------
(0 rows)

 qty | version 
-----+---------
   9 |       2
(1 row)

 qty | version 
-----+---------
   8 |       3
(1 row)

The second UPDATE returns no rows: client 2 lost the race and must retry. Optimistic locking suits low-contention data and long user think time (edit forms, APIs with ETags).

The same conflict at REPEATABLE READ is reported by PostgreSQL itself. Session B updates a row that changed after its snapshot:

-- Session A
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT qty FROM stock WHERE item = 'widget';

 qty 
-----
  10
(1 row)

-- Session B
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT qty FROM stock WHERE item = 'widget';

 qty 
-----
  10
(1 row)

-- Session A
UPDATE stock SET qty = 9 WHERE item = 'widget';
COMMIT;

-- Session B (stale snapshot)
UPDATE stock SET qty = 9 WHERE item = 'widget';

ERROR:  could not serialize access due to concurrent update

-- Session B
ROLLBACK;

The transaction must be rolled back and run again from the start.

Example 11: Write skew

The rule: at least one doctor must stay on call. Alice and Bob each check that two doctors are on call, then each takes themselves off. They update different rows, so there is no row conflict, and snapshot isolation lets both commit.

-- Session A (Alice checks: 2 on call)
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM doctors WHERE on_call;

 count 
-------
     2
(1 row)

-- Session B (Bob checks: 2 on call)
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM doctors WHERE on_call;

 count 
-------
     2
(1 row)

-- Session A
UPDATE doctors SET on_call = false WHERE name = 'Alice';
COMMIT;

-- Session B
UPDATE doctors SET on_call = false WHERE name = 'Bob';
COMMIT;

-- Session A (nobody is on call)
SELECT name, on_call FROM doctors ORDER BY name;

 name  | on_call 
-------+---------
 Alice | f
 Bob   | f
(2 rows)

The rule is broken. Neither transaction wrote to a row the other wrote, so REPEATABLE READ (snapshot isolation) does not detect it.

Example 12: Fix write skew with SERIALIZABLE

SERIALIZABLE in PostgreSQL uses serializable snapshot isolation (SSI). It tracks which transactions read data that others wrote and aborts one when the pattern could not occur in any serial order. The abort is SQLSTATE 40001.

-- Session A
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT count(*) FROM doctors WHERE on_call;

 count 
-------
     2
(1 row)

-- Session B
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT count(*) FROM doctors WHERE on_call;

 count 
-------
     2
(1 row)

-- Session A
UPDATE doctors SET on_call = false WHERE name = 'Alice';
COMMIT;

-- Session B
UPDATE doctors SET on_call = false WHERE name = 'Bob';

ERROR:  could not serialize access due to read/write dependencies among transactions
DETAIL:  Reason code: Canceled on identification as a pivot, during write.
HINT:  The transaction might succeed if retried.

-- Session B
COMMIT;

-- Session B (after ROLLBACK by the failed COMMIT)
SELECT name, on_call FROM doctors ORDER BY name;

 name  | on_call 
-------+---------
 Alice | f
 Bob   | t
(2 rows)

Session B was aborted and rolled back; the application retries it. A retry now sees one doctor on call and can refuse. Every transaction at this level needs retry logic. See transactions and ACID.

Example 13: Fix write skew with explicit locks

A second way is to lock the rows the decision depends on, at any isolation level, so the transactions run one after another:

-- Session A (locks every on-call row)
BEGIN;
SELECT name FROM doctors WHERE on_call FOR UPDATE;

 name  
-------
 Alice
 Bob
(2 rows)

-- Session B (blocks) [blocks]
BEGIN;
SELECT name FROM doctors WHERE on_call FOR UPDATE;

-- Session A
UPDATE doctors SET on_call = false WHERE name = 'Alice';
COMMIT;

-- Session B: the blocked statement now completes

 name 
------
 Bob
(1 row)

-- Session B (B now sees only Bob on call)
SELECT count(*) AS still_on_call FROM doctors WHERE on_call;
ROLLBACK;

 still_on_call 
---------------
             1
(1 row)

B waited for A, then saw that only Bob is left on call, and can refuse. Locking is covered in locking and deadlocks.

Example 14: Which fix for which anomaly

Higher levels cost retries, not just speed. Use the lowest level whose guarantees the transaction needs, and use SERIALIZABLE where correctness depends on multi-row invariants.

Interview points

• Name the four standard levels and the anomalies each allows; then say what PostgreSQL really does: READ UNCOMMITTED is READ COMMITTED, REPEATABLE READ is snapshot isolation with no phantoms, SERIALIZABLE is SSI.
• Defaults: PostgreSQL, Oracle and SQL Server use READ COMMITTED; MySQL InnoDB uses REPEATABLE READ.
• The difference between non-repeatable read (a row changes) and phantom read (the set of rows changes).
• Lost update versus write skew: lost update conflicts on the same row; write skew touches different rows and is invisible to row-level conflict checks.
• Why SERIALIZABLE needs a retry loop, and which SQLSTATE to catch (40001).
• Optimistic locking (version column, retry on conflict) versus pessimistic locking (FOR UPDATE, wait on conflict).
• InnoDB uses next-key (gap) locks to prevent phantoms in locking reads; PostgreSQL uses snapshots and predicate tracking.
• Readers do not block writers under MVCC.

Summary

• Isolation levels trade anomalies against blocking and aborts; PostgreSQL offers three distinct behaviours: READ COMMITTED, REPEATABLE READ (snapshot isolation) and SERIALIZABLE (SSI).
• READ COMMITTED allows non-repeatable reads and phantoms; REPEATABLE READ prevents both in PostgreSQL but still allows write skew.
• Lost updates are fixed with an atomic UPDATE, FOR UPDATE, a version column, or a higher level with retry.
• Write skew needs SERIALIZABLE with retry, or explicit locks on the rows the decision reads.
• Any transaction that can fail with 40001 must be retried as a whole.