Isolation Levels

When multiple users update the database at the same time, transactions can interfere with each other. Isolation Levels define how much one transaction is isolated from changes made by another transaction.

Why Do We Need Isolation?

Imagine an e-commerce system where only 1 laptop is available.

sequenceDiagram
    participant A as User A
    participant DB as Database
    participant B as User B

    A->>DB: Read stock = 1
    B->>DB: Read stock = 1
    A->>DB: Buy laptop
    B->>DB: Buy laptop
    DB-->>A: Order successful
    DB-->>B: Order successful

    Note over DB: Overselling!

The database must control what one transaction can see while another transaction is running.

Three Common Concurrency Problems

1. Dirty Read

A transaction reads data that another transaction has changed but not committed.

sequenceDiagram
    participant T1 as Transaction 1
    participant DB as Database
    participant T2 as Transaction 2

    T1->>DB: Update Balance = 500
    T2->>DB: Read Balance = 500
    T1->>DB: Rollback
    Note over T2: T2 read data that never committed

Example: T2 thinks the balance is ₹500, but after T1 rolls back, the actual balance may be ₹1,000.

2. Non-Repeatable Read

The same transaction reads the same row twice and gets different values because another transaction committed a change between the reads.

sequenceDiagram
    participant T1 as Transaction 1
    participant DB as Database
    participant T2 as Transaction 2

    T1->>DB: Read Balance = 1000
    T2->>DB: Update Balance = 1500
    T2->>DB: Commit
    T1->>DB: Read Balance again
    DB-->>T1: Balance = 1500

    Note over T1: Same query, different result

3. Phantom Read

A transaction executes the same query twice but gets a different set of rows because another transaction inserted or deleted matching rows.

sequenceDiagram
    participant T1 as Transaction 1
    participant DB as Database
    participant T2 as Transaction 2

    T1->>DB: SELECT orders WHERE Amount > 1000
    DB-->>T1: 5 orders
    T2->>DB: Insert new order Amount = 2000
    T2->>DB: Commit
    T1->>DB: Run same query again
    DB-->>T1: 6 orders

    Note over T1: New row appeared

Isolation Levels

Common isolation levels are:

• Read Uncommitted
• Read Committed
• Repeatable Read
• Serializable
• Snapshot / MVCC-based isolation in databases that support it

Isolation Level Comparison

1. Read Uncommitted

The transaction can read changes made by other transactions even before they commit.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

BEGIN TRANSACTION;

SELECT Balance
FROM Accounts
WHERE AccountId = 101;

COMMIT;

Advantage: Very high concurrency and fewer blocking issues.

Problem: Dirty reads can occur.

It is generally unsuitable for financial operations where correctness is critical.

2. Read Committed

The transaction can only read committed data.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

BEGIN TRANSACTION;

SELECT Balance
FROM Accounts
WHERE AccountId = 101;

COMMIT;

This prevents dirty reads, but another transaction can modify the row before the first transaction reads it again.

3. Repeatable Read

Once a transaction reads a row, another transaction cannot modify that row in a way that causes the original transaction to get a different value during the transaction.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

BEGIN TRANSACTION;

SELECT Balance
FROM Accounts
WHERE AccountId = 101;

-- Other transaction cannot change this row
-- in a conflicting way while this transaction holds
-- the required locks.

SELECT Balance
FROM Accounts
WHERE AccountId = 101;

COMMIT;

This prevents dirty reads and non-repeatable reads, but phantom rows can still occur.

4. Serializable

Serializable provides the strongest traditional isolation level. Concurrent transactions behave as though they were executed one after another.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN TRANSACTION;

SELECT *
FROM Inventory
WHERE ProductId = 100
AND AvailableQuantity > 0;

-- Perform business operation

COMMIT;

This provides strong consistency but can significantly reduce concurrency because transactions may block each other.

Complete Example — Preventing Inventory Overselling

Suppose an e-commerce application has:

ProductId = 100
AvailableQuantity = 1

Two customers click Buy at almost exactly the same time.

sequenceDiagram
    participant A as Customer A
    participant B as Customer B
    participant DB as Database

    A->>DB: BEGIN TRANSACTION
    B->>DB: BEGIN TRANSACTION

    A->>DB: Check stock = 1
    B->>DB: Check stock = 1

    A->>DB: Reduce stock to 0
    A->>DB: COMMIT

    B->>DB: Try to reduce stock
    DB-->>B: Conflict / wait / failure

    Note over DB: Only one order succeeds

The exact implementation can use an appropriate isolation level, locking strategy, or an atomic conditional update.

Better SQL Pattern

UPDATE Inventory
SET AvailableQuantity = AvailableQuantity - 1
WHERE ProductId = 100
  AND AvailableQuantity > 0;

Then check the affected row count:

if (rowsAffected == 1)
{
    // Stock successfully reserved
}
else
{
    // Stock unavailable
}

This is often preferable to simply relying on a very high isolation level for every operation.

Snapshot Isolation

Some databases support MVCC / snapshot-based isolation, where readers can work with a consistent version of data instead of always blocking on writers.

sequenceDiagram
    participant Reader as Reader
    participant DB as Database
    participant Writer as Writer

    Reader->>DB: Read Version 1
    Writer->>DB: Update row
    Writer->>DB: Commit
    Reader->>DB: Continue reading
    DB-->>Reader: Consistent snapshot

    Note over Reader: Reader can see a consistent version

Snapshot-based approaches can improve read concurrency, but the exact semantics depend on the database and configuration.

SQL Server Example

In SQL Server, Read Committed is the default isolation level, but its behavior can differ depending on whether Read Committed Snapshot Isolation (RCSI) is enabled.

SELECT name, is_read_committed_snapshot_on
FROM sys.databases
WHERE name = DB_NAME();

This is an important interview point: do not assume every database implements an isolation level in exactly the same way.

How to Choose an Isolation Level?

Isolation vs Performance

flowchart LR
    A[Lower Isolation] --> B[Higher Concurrency]
    B --> C[Less Blocking]
    A --> D[More Concurrency Anomalies]

    E[Higher Isolation] --> F[Stronger Consistency]
    F --> G[More Blocking / Contention]
    E --> H[Lower Concurrency]

The goal is not to always use the strongest isolation level. The goal is to choose the level that satisfies the business consistency requirement without creating unnecessary contention.

Interview Scenario

Interviewer: Two users are purchasing the last product at exactly the same time. How would you prevent overselling?

Answer:

1. Use an atomic conditional update or appropriate transaction.
2. Check the affected row count.
3. Use locking/isolation where required.
4. Commit the reservation and order together when they belong to the same database transaction.
5. For distributed microservices, consider patterns such as reservation, idempotency, and Saga instead of relying on a single database transaction.

Common Interview Questions

• What is an isolation level?
• What is a dirty read?
• What is a non-repeatable read?
• What is a phantom read?
• Read Committed vs Repeatable Read?
• Why is Serializable expensive?
• What is Snapshot Isolation?
• How would you prevent inventory overselling?
• Does every database implement isolation levels identically?
• How does isolation affect performance?

Key Takeaways

• Read Uncommitted → highest concurrency, dirty reads possible.
• Read Committed → prevents dirty reads.
• Repeatable Read → prevents dirty and non-repeatable reads.
• Serializable → strongest traditional isolation.
• Snapshot / MVCC → can improve read concurrency using consistent versions.
• Higher isolation generally means more consistency but potentially more contention.
• For real systems, combine transactions/isolation with atomic SQL operations and appropriate locking rather than blindly choosing the strongest level.

Conclusion

Isolation levels control how concurrent transactions interact. For system design interviews, remember the three major problems—dirty read, non-repeatable read, and phantom read—and understand how each isolation level handles them.

The key system-design trade-off is simple: stronger isolation gives stronger consistency, but can reduce concurrency.