Full Stack • Java • System Design • Cloud • AI Engineering

Database Isolation Levels in System Design

Learn Database Isolation Levels with practical examples. Understand Read Uncommitted, Read Committed, Repeatable Read, Serializable, Dirty Reads, Non-Repeatable Reads, Phantom Reads, MVCC, row locking, Spring Boot transaction isolation, and real-world banking examples.

Introduction

Imagine a banking application with millions of customers.

Two customers access the same account simultaneously.

  • Customer A transfers $2,000
  • Customer B checks the account balance

If Customer B reads the balance while Customer A's transaction is only partially complete, incorrect information may be displayed.

This is known as a concurrency problem.

Database Isolation Levels determine how transactions interact with one another and what data they are allowed to see while running concurrently.

Without proper isolation:

  • Dirty Reads occur
  • Non-Repeatable Reads occur
  • Phantom Reads occur
  • Lost Updates occur
  • Business data becomes inconsistent

Learning Objectives

After completing this article, you'll understand:

  • What is Transaction Isolation?
  • Why Isolation Levels Matter
  • Concurrency Problems
  • Read Uncommitted
  • Read Committed
  • Repeatable Read
  • Serializable
  • MVCC
  • Row Locking
  • Spring Boot Isolation
  • Banking Examples
  • Best Practices

What is Transaction Isolation?

Isolation determines how much one transaction can see another transaction's changes before they are committed.

Think of multiple users working on the same spreadsheet.

Isolation ensures they don't accidentally overwrite or read incomplete changes.

Why Isolation Levels?

Without Isolation

flowchart TD
    U1[User 1]
    U2[User 2]
    DB[(Database)]

    U1 --> DB
    U2 --> DB

Both users may read or update the same data simultaneously.

With Isolation

flowchart TD
    U1[Transaction A]
    DB[(Database)]
    U2[Transaction B]

    U1 --> DB
    DB --> U2

The database controls visibility and locking to maintain consistency.

Real-Time Banking Example

Current Balance

Account Balance = $10,000

Customer A

Withdraw $2,000

Customer B

Check Balance

Question

Should Customer B see:

  • $10,000?
  • $8,000?
  • Or wait?

The answer depends on the configured isolation level.

Transaction Timeline

sequenceDiagram
    participant T1 as Transaction A
    participant DB
    participant T2 as Transaction B

    T1->>DB: Begin Transaction
    T1->>DB: Update Balance
    T2->>DB: Read Balance
    T1->>DB: Commit

Isolation level determines what Transaction B sees.

Concurrency Problems

The four most common problems are:

Problem Description
Dirty Read Reading uncommitted data
Non-Repeatable Read Same row changes during transaction
Phantom Read New rows appear during transaction
Lost Update Concurrent updates overwrite each other

Isolation Levels Overview

flowchart LR
    A[Read Uncommitted]
    B[Read Committed]
    C[Repeatable Read]
    D[Serializable]

    A --> B
    B --> C
    C --> D

Higher isolation

  • Better consistency
  • Lower concurrency

Isolation Comparison

Isolation Dirty Read Non-Repeatable Read Phantom Read
Read Uncommitted ❌ Possible ❌ Possible ❌ Possible
Read Committed ✅ Prevented ❌ Possible ❌ Possible
Repeatable Read ✅ Prevented ✅ Prevented ❌ Possible*
Serializable ✅ Prevented ✅ Prevented ✅ Prevented

*Behavior may vary slightly by database implementation. For example, PostgreSQL uses MVCC and prevents many anomalies differently than MySQL.

Read Uncommitted

Lowest isolation level.

Transactions can read data that has not yet been committed.

Dirty Read Example

sequenceDiagram
    participant T1
    participant DB
    participant T2

    T1->>DB: Update Balance = 8000
    Note over DB: Not committed

    T2->>DB: Read Balance
    DB-->>T2: 8000

    T1->>DB: Rollback

Transaction B read invalid data.

Read Committed

Default isolation level in PostgreSQL, Oracle, and SQL Server.

Transactions can read only committed data.

sequenceDiagram
    participant T1
    participant DB
    participant T2

    T1->>DB: Update Balance
    T1->>DB: Commit

    T2->>DB: Read Balance
    DB-->>T2: Updated Balance

Dirty Reads are eliminated.

Non-Repeatable Read

Suppose a transaction reads the same row twice.

Between those reads,

another transaction updates the row.

sequenceDiagram
    participant T1
    participant DB
    participant T2

    T1->>DB: Read Salary = 5000

    T2->>DB: Update Salary = 6000
    T2->>DB: Commit

    T1->>DB: Read Salary
    DB-->>T1: 6000

The same query returned different results.

Repeatable Read

The database guarantees that rows already read cannot change during the transaction.

flowchart TD
    A[Transaction Begins]
    B[Read Product Price]
    C[Another Transaction Updates Price]
    D[Read Product Price Again]

    A --> B
    B --> C
    C --> D

The transaction continues to see the original version of the row.

Phantom Read

A query returns additional rows because another transaction inserted new records.

First Query

SELECT *
FROM orders
WHERE status='NEW';

Result

100 Rows

Another transaction inserts a new order.

Second Query

101 Rows

A new "phantom" row appeared.

Phantom Read Diagram

sequenceDiagram
    participant T1
    participant DB
    participant T2

    T1->>DB: SELECT Orders

    T2->>DB: INSERT Order
    T2->>DB: Commit

    T1->>DB: SELECT Orders Again

Serializable

Highest isolation level.

Transactions execute as if they were processed sequentially.

flowchart LR
    T1[Transaction 1]
    T2[Transaction 2]
    T3[Transaction 3]

    T1 --> T2
    T2 --> T3

Advantages

  • Maximum consistency

Disadvantages

  • More locking
  • Lower throughput
  • Increased waiting time

MVCC (Multi-Version Concurrency Control)

Many modern databases use MVCC instead of locking readers.

Each update creates a new version of a row.

flowchart TD
    V1[Version 1]
    V2[Version 2]
    V3[Version 3]

    V1 --> V2
    V2 --> V3

Readers continue using the old version while writers create a new version.

Used by

  • PostgreSQL
  • Oracle
  • MySQL InnoDB

Row Locking

When updating data,

the database locks only the affected rows.

flowchart TD
    T1[Transaction A]
    R1[Row Lock]
    DB[(Customer Table)]

    T1 --> R1
    R1 --> DB

Other rows remain available.

Table Locking

Sometimes an entire table is locked.

flowchart TD
    LOCK[Table Lock]

    DB[(Orders Table)]

    LOCK --> DB

Generally avoided because it reduces concurrency.

Spring Boot Example

@Transactional(
    isolation = Isolation.REPEATABLE_READ
)
public void transferMoney() {

}

Available isolation levels

  • READ_UNCOMMITTED
  • READ_COMMITTED
  • REPEATABLE_READ
  • SERIALIZABLE
  • DEFAULT

Spring Transaction Flow

flowchart TD
    CLIENT[Client]

    API[REST Controller]

    SERVICE[Service Layer]

    TX[Transaction Manager]

    DB[(PostgreSQL)]

    CLIENT --> API
    API --> SERVICE
    SERVICE --> TX
    TX --> DB

Banking Example

Money Transfer

flowchart TD
    DEBIT[Debit Account]

    CREDIT[Credit Account]

    LEDGER[Ledger Entry]

    COMMIT[Commit]

    DEBIT --> CREDIT
    CREDIT --> LEDGER
    LEDGER --> COMMIT

Strong consistency is mandatory.

Amazon Example

Amazon commonly uses

  • Read Committed
  • Optimistic Locking
  • Distributed transactions (Saga Pattern)

for order processing and inventory updates.

Netflix Example

Netflix prioritizes availability and scalability.

Many services use eventual consistency, while transactional services apply stronger isolation where required.

Isolation Level Selection

Use Case Recommended Isolation
Banking Serializable / Repeatable Read
Payment Gateway Serializable
Inventory Repeatable Read + Optimistic Locking
E-commerce Catalog Read Committed
Analytics Read Committed
Reporting Read Committed

Monitoring

Monitor

  • Deadlocks
  • Lock Wait Time
  • Transaction Duration
  • Rollback Count
  • Active Transactions
  • Blocking Sessions
  • CPU Usage
  • Slow Queries

Tools

  • PostgreSQL pg_stat_activity
  • MySQL Performance Schema
  • Oracle AWR
  • Datadog
  • Grafana
  • Prometheus

Common Mistakes

❌ Always using Serializable

❌ Long-running transactions

❌ Reading uncommitted data

❌ Holding locks during API calls

❌ Ignoring deadlocks

❌ Using the same isolation level for every workload

Best Practices

  • Use the lowest isolation level that satisfies business requirements.
  • Keep transactions short.
  • Avoid user interaction during transactions.
  • Prefer optimistic locking for read-heavy systems.
  • Monitor deadlocks and lock contention.
  • Index frequently updated columns appropriately.
  • Test concurrency scenarios under production-like load.

Common Interview Questions

What is a Transaction Isolation Level?

A transaction isolation level defines how and when the changes made by one transaction become visible to other concurrent transactions.

What is a Dirty Read?

A Dirty Read occurs when one transaction reads data modified by another transaction before it has been committed.

What is a Phantom Read?

A Phantom Read occurs when the same query returns a different number of rows because another transaction inserted or deleted matching records.

Why isn't Serializable always used?

Although Serializable provides the strongest consistency, it reduces concurrency, increases locking, and lowers throughput. Most applications use lower isolation levels to balance performance and consistency.

Which isolation level does PostgreSQL use by default?

PostgreSQL uses Read Committed as the default isolation level and implements concurrency using Multi-Version Concurrency Control (MVCC).

Summary

Transaction isolation levels are fundamental to building reliable multi-user database applications. They control how concurrent transactions interact, balancing data consistency with system performance.

In this article, we covered:

  • Transaction isolation fundamentals
  • Dirty Reads
  • Non-Repeatable Reads
  • Phantom Reads
  • Read Uncommitted
  • Read Committed
  • Repeatable Read
  • Serializable
  • MVCC
  • Row locking
  • Spring Boot integration
  • Banking, Amazon, and Netflix examples
  • Best practices

Choosing the correct isolation level is a critical architectural decision. For financial systems, stronger isolation is often necessary, while high-scale consumer applications typically use Read Committed combined with optimistic locking and application-level consistency mechanisms to achieve excellent performance without sacrificing correctness.

Loading likes...

Comments

Share a question, correction, or practical insight about this article.

Loading approved comments...