PostgreSQL Isolation Levels Explained: What Transactions Prevent and What They Still Allow

Most developers learn that transactions make database operations safe. What often gets missed is that a transaction can still read inconsistent data, make decisions based on outdated information, or even produce incorrect business results depending on the isolation level being used.

Isolation levels exist because locking everything would destroy concurrency. PostgreSQL instead tries to balance correctness and performance, allowing multiple transactions to work simultaneously while preventing specific categories of anomalies.

Why Isolation Levels Exist

Imagine two users interacting with the same bank account.

Transaction A checks the balance and sees $1000.

At the same time, Transaction B withdraws $500 and commits.

If Transaction A performs another read during the same transaction, should it still see $1000 or should it now see $500?

The answer depends entirely on the transaction’s isolation level.

Without isolation guarantees, concurrent transactions can observe data changing underneath them, leading to subtle bugs that are extremely difficult to reproduce in production.

The Main Concurrency Problems

Database literature usually describes four common anomalies:

• Dirty Reads
• Non-Repeatable Reads
• Phantom Reads
• Serialization Anomalies

Let’s understand each one before looking at PostgreSQL’s isolation levels.

Dirty Reads

A dirty read happens when one transaction reads data written by another transaction that hasn’t committed yet.

Consider:

-- Transaction A
BEGIN;

UPDATE accounts
SET balance = 500
WHERE id = 1;

Before Transaction A commits:

-- Transaction B
SELECT balance
FROM accounts
WHERE id = 1;

Transaction B sees:

500

Now imagine Transaction A rolls back:

ROLLBACK;

The actual balance returns to:

1000

Transaction B just read data that never truly existed.

Dirty reads allow transactions to observe uncommitted changes.

The good news is PostgreSQL never allows dirty reads, even at its lowest isolation level.

Non-Repeatable Reads

A non-repeatable read occurs when the same row is read twice within a transaction and returns different results.

Initial value:

balance = 1000

Transaction A:

BEGIN;

SELECT balance
FROM accounts
WHERE id = 1;

Result:

1000

Meanwhile Transaction B:

UPDATE accounts
SET balance = 500
WHERE id = 1;

COMMIT;

Transaction A reads again:

SELECT balance
FROM accounts
WHERE id = 1;

Result:

500

The same query inside the same transaction returned different values.

This can break application logic that assumes previously read data remains stable.

Phantom Reads

A phantom read occurs when the rows matching a condition change during a transaction.

Suppose Transaction A executes:

SELECT *
FROM orders
WHERE amount > 1000;

It finds:

5 rows

Meanwhile Transaction B inserts:

INSERT INTO orders(amount)
VALUES (1500);

COMMIT;

Transaction A runs the same query again:

SELECT *
FROM orders
WHERE amount > 1000;

Now it sees:

6 rows

No existing row changed.

Instead, an entirely new row appeared.

That newly visible row is called a phantom.

Serialization Anomalies

This is the most subtle class of concurrency bugs.

Imagine two doctors updating a hospital scheduling system.

Current state:

Doctor A on call
Doctor B on call

Rule:

At least one doctor must remain on call.

Transaction A:

Reads Doctor B
Sees B is on call
Turns off Doctor A

Transaction B:

Reads Doctor A
Sees A is on call
Turns off Doctor B

Both transactions commit.

Final result:

Doctor A off call
Doctor B off call

The business rule has been violated even though each transaction individually looked correct.

This type of issue is known as a serialization anomaly or write skew.

PostgreSQL Isolation Levels

PostgreSQL supports three practical isolation levels:

• Read Committed
• Repeatable Read
• Serializable

Although SQL standards define four levels, PostgreSQL treats Read Uncommitted as Read Committed.

Isolation Level Hierarchy

Read Committed
       ↓
Repeatable Read
       ↓
Serializable

Moving downward increases consistency guarantees but also introduces additional overhead.

Read Committed

This is PostgreSQL’s default isolation level.

Each query sees a snapshot of committed data as of the moment the query starts.

How It Works

Transaction A:

BEGIN;

SELECT balance
FROM accounts
WHERE id = 1;

Result:

1000

Transaction B:

UPDATE accounts
SET balance = 500
WHERE id = 1;

COMMIT;

Transaction A executes again:

SELECT balance
FROM accounts
WHERE id = 1;

Result:

500

Every statement gets a fresh snapshot.

What It Prevents

• Dirty Reads

What It Allows

• Non-Repeatable Reads
• Phantom Reads
• Write Skew

For many applications this level is perfectly acceptable and provides the best balance between performance and correctness.

Repeatable Read

Repeatable Read takes a snapshot when the transaction begins.

Every query inside that transaction uses the same snapshot.

Visualizing the Snapshot

No matter how many commits happen elsewhere, the transaction continues seeing the original view.

Example

Transaction A:

BEGIN ISOLATION LEVEL REPEATABLE READ;

SELECT balance
FROM accounts
WHERE id = 1;

Result:

1000

Transaction B:

UPDATE accounts
SET balance = 500
WHERE id = 1;

COMMIT;

Transaction A reads again:

SELECT balance
FROM accounts
WHERE id = 1;

Result:

1000

The value remains stable throughout the transaction.

What It Prevents

• Dirty Reads
• Non-Repeatable Reads
• Phantom Reads

What It Still Allows

• Serialization Anomalies
• Write Skew

Many developers assume Repeatable Read guarantees full correctness.

It does not.

The classic doctor scheduling example can still fail.

Understanding Write Skew

This is the anomaly that surprises most engineers.

Consider:

SELECT *
FROM doctors
WHERE on_call = true;

Both transactions see:

2 doctors on call

Transaction A disables Doctor A.

Transaction B disables Doctor B.

Each transaction only modifies its own row.

No row-level conflict exists.

As a result, both commits succeed.

Final state:

0 doctors on call

The database remains internally consistent, but the business rule is broken.

This is why Serializable isolation exists.

Serializable

Serializable is PostgreSQL’s strongest isolation level.

It guarantees that concurrent transactions behave as if they executed one after another.

What PostgreSQL Actually Does

PostgreSQL does not lock the entire database.

Instead, it uses a technique called:

Serializable Snapshot Isolation (SSI)

The engine tracks transaction dependencies and looks for dangerous execution patterns.

When PostgreSQL detects a potential serialization anomaly, it aborts one of the transactions.

Example Error

ERROR: could not serialize access
due to read/write dependencies
among transactions

The application is expected to retry the transaction.

What It Prevents

• Dirty Reads
• Non-Repeatable Reads
• Phantom Reads
• Write Skew
• Serialization Anomalies

Tradeoffs

Benefits:

• Strongest consistency guarantees
• Eliminates subtle concurrency bugs
• Simplifies business rule enforcement

Costs:

• Higher memory usage
• Additional dependency tracking
• Occasional transaction retries

For critical financial or inventory systems, these tradeoffs are often worth it.

How MVCC Enables Isolation Levels

In the previous article, I explained how PostgreSQL uses MVCC to maintain multiple row versions.

Each row stores metadata such as:

• xmin
• xmax

These transaction identifiers allow PostgreSQL to determine which row version should be visible for a given snapshot.

When a transaction starts, PostgreSQL records a snapshot of active transaction IDs.

Every query then evaluates row visibility against that snapshot.

This mechanism is what makes non-blocking reads possible while still providing isolation guarantees.

Choosing the Right Isolation Level

There is no universally correct choice.

Read Committed

Best for:

• Most web applications
• CRUD systems
• Reporting workloads

Benefits:

• Fastest execution
• Lowest overhead
• High concurrency

Repeatable Read

Best for:

• Long-running business workflows
• Financial calculations
• Multi-step reporting transactions

Benefits:

• Stable snapshots
• Consistent reads

Serializable

Best for:

• Banking systems
• Inventory management
• Reservation systems
• Critical business invariants

Benefits:

• Strongest correctness guarantees

Cost:

• Retry logic becomes mandatory

A Practical Rule I Follow

Most applications start safely with Read Committed.

If I need a transaction to observe a stable view of data throughout its lifetime, I move to Repeatable Read.

When a business rule absolutely cannot be violated, even under heavy concurrency I use Serializable and build retry handling into the application layer.

The important thing is understanding that transactions alone do not guarantee correctness. The isolation level determines which concurrency problems PostgreSQL prevents, which ones it tolerates, and which ones your application still needs to handle.