Database Isolation Levels

Overview

Database isolation defines how concurrent transactions see each other's changes. Higher isolation = fewer anomalies, more locks/overhead. Most production systems use Read Committed (PostgreSQL default) or Repeatable Read (MySQL InnoDB default).

The four anomalies isolation prevents:

Architecture

ISOLATION LEVELS vs ANOMALIES PREVENTED
══════════════════════════════════════════════════════════

                    Dirty  Non-repeat  Phantom  Serialization
                    Read   Read        Read     Anomaly
                    ─────  ──────────  ───────  ─────────────
Read Uncommitted  │  ✗      ✗           ✗         ✗
Read Committed    │  ✓      ✗           ✗         ✗
Repeatable Read   │  ✓      ✓           ✗(*)      ✗
Serializable      │  ✓      ✓           ✓         ✓

(*) MySQL InnoDB prevents phantoms in RR via gap locks

HOW MVCC WORKS (Repeatable Read):
══════════════════════════════════════════════════════════

  accounts table (with hidden MVCC columns):
  ┌──────────┬─────────┬────────────────┬───────────────┐
  │ id       │ balance │ transaction_id │ roll_pointer  │
  ├──────────┼─────────┼────────────────┼───────────────┤
  │ acct_001 │ 100     │ 200            │ NULL          │ ← committed
  └──────────┴─────────┴────────────────┴───────────────┘

  Transaction A starts (tx_id = 201):
  Transaction B starts (tx_id = 202):

  Transaction A: UPDATE accounts SET balance = 200 WHERE id = 'acct_001'
  ┌──────────┬─────────┬────────────────┬───────────────┐
  │ acct_001 │ 200     │ 201 (uncommit) │ ──────────────┤─▶ old row (balance=100)
  └──────────┴─────────┴────────────────┴───────────────┘

  Transaction B: SELECT balance → reads balance=100
  (tx_id 201 is not committed yet → follows roll_pointer → reads tx_id=200 row)

  Transaction A: COMMIT
  Transaction B: SELECT balance → STILL reads balance=100
  (ReadView was created when B started → tx_id 201 was active then → invisible)

  Transaction B: COMMIT
  New transaction: SELECT balance → reads balance=200 (A is committed)

LOCK TYPES:
══════════════════════════════════════════════════════════
  Optimistic (MVCC):  readers don't block writers
                      writers don't block readers
                      Used in: Read Committed, Repeatable Read

  Pessimistic (locks): Serializable uses shared/exclusive locks
                       or predicate locks to prevent all anomalies

Technical Implementation

Setting Isolation Level in SQL

-- Session level (applies to all subsequent transactions)
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Single transaction
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
  SELECT balance FROM accounts WHERE id = 'acct_001';
  UPDATE accounts SET balance = balance - 100 WHERE id = 'acct_001';
COMMIT;

-- PostgreSQL default: READ COMMITTED
-- MySQL InnoDB default: REPEATABLE READ

Spring / JDBC Isolation Levels

// Spring @Transactional — set isolation per method
@Transactional(isolation = Isolation.READ_COMMITTED)
public void transfer(String from, String to, BigDecimal amount) {
    Account source = accountRepo.findById(from).orElseThrow();
    Account target = accountRepo.findById(to).orElseThrow();

    if (source.getBalance().compareTo(amount) < 0) {
        throw new InsufficientFundsException();
    }

    source.setBalance(source.getBalance().subtract(amount));
    target.setBalance(target.getBalance().add(amount));
}

// Options:
// Isolation.DEFAULT           — uses DB default
// Isolation.READ_UNCOMMITTED  — dirty reads possible
// Isolation.READ_COMMITTED    — prevents dirty reads
// Isolation.REPEATABLE_READ   — prevents non-repeatable reads
// Isolation.SERIALIZABLE      — full isolation, lowest throughput

Demonstrating an Anomaly (Non-Repeatable Read)

-- Session 1 (READ COMMITTED):
BEGIN;
SELECT balance FROM accounts WHERE id = 1;  -- returns 100

-- Session 2 (concurrent):
BEGIN;
UPDATE accounts SET balance = 200 WHERE id = 1;
COMMIT;

-- Session 1 continues:
SELECT balance FROM accounts WHERE id = 1;  -- returns 200! (non-repeatable)
-- Under REPEATABLE READ → would still return 100
COMMIT;

Optimistic Locking with Version Column

@Entity
public class Account {
    @Id
    private String id;
    private BigDecimal balance;

    @Version  // JPA optimistic locking — auto-incremented on update
    private Long version;
}

// If two transactions both read version=5 and try to update:
// First commit: version 5→6, succeeds
// Second commit: WHERE version=5 matches no rows → OptimisticLockException
// Caller must retry
@Transactional
public void debit(String accountId, BigDecimal amount) {
    try {
        Account account = repo.findById(accountId).orElseThrow();
        account.setBalance(account.getBalance().subtract(amount));
        repo.save(account);  // throws OptimisticLockException on version conflict
    } catch (OptimisticLockException e) {
        // Retry logic
        throw new RetryableException("Concurrent modification, please retry");
    }
}

Interview Preparation

Q: What isolation level does PostgreSQL use by default, and what anomaly can still occur?

PostgreSQL defaults to Read Committed. Dirty reads are prevented (you only see committed data), but non-repeatable reads can occur — two SELECT statements within the same transaction may return different values if another transaction commits a change between them.

Q: Explain MVCC.

Multi-Version Concurrency Control (MVCC) maintains multiple versions of each row. When a transaction updates a row, the old version is kept (in undo log or as a previous version). Readers get the version that was current when their transaction (or the statement, for Read Committed) started — so readers and writers don't block each other. PostgreSQL and MySQL InnoDB both use MVCC.

Q: When would you use SERIALIZABLE isolation?

When correctness is critical and concurrent anomalies are unacceptable: financial transfers, inventory reservation, anything where a phantom read or write skew could cause incorrect results. The tradeoff: significantly lower throughput and higher deadlock risk. Most production systems use Read Committed + application-level locking (SELECT FOR UPDATE, optimistic locking) for specific critical sections.

Q: What is write skew?

Write skew is a serialization anomaly that can occur even at REPEATABLE READ. Example: two concurrent transactions each read "are there at least 2 doctors on call?" (both see yes), then each marks one doctor as off-call. Result: 0 doctors on call — violates the constraint. Prevention: SERIALIZABLE isolation or explicit SELECT FOR UPDATE to lock the rows.

Learning Resources

• PostgreSQL Transaction Isolation — official docs
• MySQL InnoDB Isolation Levels
• Designing Data-Intensive Applications — Martin Kleppmann, Chapter 7
• MVCC in PostgreSQL