Isolation Levels for PostgreSQL

This post describes the isolation levels in PostgreSQL and their implications.

Isolation Levels Overview

PostgreSQL supports four standard isolation levels defined by the SQL standard:

Read Uncommitted (Same as Read Committed in PostgreSQL)
Read Committed
Repeatable Read
Serializable

Each level provides different guarantees about the visibility of data changes made by concurrent transactions.

Read Uncommitted

The lowest isolation level.
Transactions can see uncommitted changes made by other transactions (dirty reads).
Not supported in PostgreSQL; it behaves like Read Committed. (It is defined in settings but has no effect.)

Read Committed

Default isolation level in PostgreSQL.
A transaction sees only data committed before the query began.
Prevents dirty reads but allows non-repeatable reads and phantom reads.
Suitable for most applications where high concurrency is required.

Time	f-tx	s-tx	Value
1	BEGIN		1
2		BEGIN	1
3		READ (1)	1
4	UPDATE … +1 (locks)		1
5		UPDATE … +1 → WAITS	1
6	COMMIT (writes 2)		2
7		→ unblocked	2
8		→ reloads (2)	2
9		→ applies +1 (3)	2
10		→ COMMIT (writes 3)	3

Repeatable Read

A transaction sees a consistent snapshot of the database as of the start of the transaction.
Prevents dirty reads and non-repeatable reads but allows phantom reads.
Suitable for scenarios where consistent reads are required within a transaction.
May lead to serialization anomalies in highly concurrent environments.

Time	f-tx	s-tx	Value
1	BEGIN		1
2		BEGIN (snapshot: sees 1)	1
3		READ (1)	1
4	UPDATE … +1 (locks)		1
5		UPDATE … +1 → WAITS	1
6	COMMIT (writes 2)		2
7		→ ERROR: could not serialize	2
8		→ ROLLBACK	2

Serializable

The highest isolation level.
Transactions are executed in a way that they appear to be serialized.
Prevents dirty reads, non-repeatable reads, and phantom reads.
May lead to serialization failures, requiring transactions to be retried.
Suitable for applications requiring strict consistency.

It’s almost same with Repeatable Read in PostgreSQL, but with additional checks to ensure serializability. Such cases are

Write Skew Anomaly

id	name	on_call
1	Alice	true
2	Bob	true

In Repeatable Read:

Time	tx1 (Alice requests off-duty)	tx2 (Bob requests off-duty)
1	BEGIN
2		BEGIN
3	SELECT COUNT(*) FROM doctors
	WHERE on_call = true; – sees 2 ✓
4		SELECT COUNT(*) FROM doctors
		WHERE on_call = true; – sees 2 ✓
5	UPDATE doctors SET on_call = false
	WHERE id = 1; – Alice goes off
6		UPDATE doctors SET on_call = false
		WHERE id = 2; – Bob goes off
7	COMMIT ✓
8		COMMIT ✓
9	Result: 0 doctors on call! 💥

Repeatable Read CANNOT detect this! Each transaction updates a different row (no conflict) Each sees 2 doctors in their snapshot But the result is 0 doctors (business rule violated)

In Serializable:

Time	tx1 (Alice)	tx2 (Bob)
1	BEGIN
2		BEGIN
3	SELECT COUNT(*) … – 2
4		SELECT COUNT(*) … – 2
5	UPDATE … id = 1
6		UPDATE … id = 2
7	COMMIT ✓
8		COMMIT 💥
		ERROR: could not serialize
		access due to read/write
		dependencies among transactions

Read-Only Transaction Anomaly