---

🟢 Junior Level

Transaction isolation levels determine the degree to which in-flight modifications made by one transaction are isolated from and protected against concurrent transactions executing simultaneously in the database.

The ANSI/ISO SQL-92 standard defines four standard isolation levels, ordered by increasing data consistency guarantees and decreasing concurrent throughput:

1. Read Uncommitted: The weakest level. A transaction can read uncommitted, in-flight modifications made by other transactions before they issue a COMMIT.
2. Read Committed: A transaction can only read data that was successfully committed prior to the query execution. Prevents “dirty” reads.
3. Repeatable Read: Guarantees that any row read by a transaction will return identical values if queried again within the same transaction. Prevents “non-repeatable” reads.
4. Serializable: The strictest level. Transactions execute with the guarantee of complete isolation, producing results equivalent to executing transactions strictly serially one after another.

Standard ANSI SQL Anomaly Matrix

Isolation Level	Dirty Read	Non-Repeatable Read	Phantom Read
Read Uncommitted	⚠️ Allowed	⚠️ Allowed	⚠️ Allowed
Read Committed	❌ Prevented	⚠️ Allowed	⚠️ Allowed
Repeatable Read	❌ Prevented	❌ Prevented	⚠️ Allowed (Prevented in MySQL InnoDB*)
Serializable	❌ Prevented	❌ Prevented	❌ Prevented

*Note: MySQL InnoDB prevents Phantom Reads under Repeatable Read using Next-Key Locking (Record + Gap Locks).

import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Isolation;
import org.springframework.transaction.annotation.Transactional;

@Service
public class OrderService {

    // Explicitly defining transaction isolation in Spring
    @Transactional(isolation = Isolation.REPEATABLE_READ)
    public void generateMonthlyReport(Long accountId) {
        // Repeated SELECT queries for accountId are guaranteed to return identical rows
    }
}

Analogy: Isolation levels are like partitioned seating in a library: Read Uncommitted is an open shared table where anyone can read notes over your shoulder; Read Committed provides privacy partitions; Repeatable Read places you in a private study carrel; and Serializable is a locked, guarded private room where only one researcher is permitted at a time.

---

🟡 Middle Level

Concurrent Access Anomalies Explained

1. Dirty Read: Transaction $T_1$ updates a row without committing. Transaction $T_2$ reads this modified row. Then $T_1$ issues ROLLBACK. Transaction $T_2$ is left with dirty, hallucinated data that never officially existed in the database.
2. Non-Repeatable Read (Fuzzy Read): Transaction $T_1$ reads a row where balance = 100. Transaction $T_2$ updates balance = 150 and executes COMMIT. Transaction $T_1$ re-reads the same row and observes 150.
3. Phantom Read: Transaction $T_1$ executes a range query: SELECT * FROM users WHERE age > 18 (yielding 5 records). Transaction $T_2$ inserts a new row with age = 20 and issues COMMIT. Transaction $T_1$ repeats the range query and discovers 6 records (a “phantom” row appeared).

Default Isolation Levels in Industry Databases

• PostgreSQL, Oracle, Microsoft SQL Server: Default to Read Committed. This maximizes throughput (TPS) and eliminates the memory overhead of maintaining deep MVCC version histories.
• MySQL (InnoDB): Historically defaults to Repeatable Read (originally chosen in early MySQL versions to guarantee correctness for Statement-Based Binary Logging replication).

Architectural Behavior in PostgreSQL

In PostgreSQL, Multi-Version Concurrency Control (MVCC) creates point-in-time snapshots natively:

• Read Committed: A fresh visibility snapshot is generated at the start of each individual SQL statement inside the transaction.
• Repeatable Read: A single visibility snapshot is generated once at the start of the first query and remains immutable until the transaction completes.

---

🔴 Senior Level

The Write Skew Anomaly and Snapshot Isolation

The ANSI SQL-92 specification is famously criticized for omitting multi-version systems and missing a critical anomaly: Write Skew.

Modern relational engines (such as PostgreSQL and Oracle) implement Snapshot Isolation under the label of Repeatable Read. Snapshot Isolation eliminates Dirty Reads, Non-Repeatable Reads, and read phantoms, yet permits Write Skew:

Concrete Write Skew Scenario:

• Business Rule: A hospital must maintain at least one on-call doctor (count(on_call) >= 1). Currently, two doctors are on call: Doctor A and Doctor B.
• Transaction 1 (Doctor A): Queries SELECT count(*) WHERE on_call = true (returns 2). Marks Doctor A off-duty: UPDATE doctors SET on_call = false WHERE name = 'A'.
• Transaction 2 (Doctor B): Simultaneously queries SELECT count(*) (returns 2, as $T_1$ is uncommitted). Marks Doctor B off-duty: UPDATE doctors SET on_call = false WHERE name = 'B'.
• Both transactions commit successfully!
• Outcome: Exactly 0 doctors remain on call. The system invariant is broken!

Because neither transaction updated the exact same row, Snapshot Isolation detects no write-write collision. Only Serializable isolation (or explicit SELECT FOR UPDATE locking) prevents Write Skew.

PostgreSQL Serializable Snapshot Isolation (SSI)

Instead of using traditional heavyweight two-phase locking (2PL) that blocks concurrent readers, PostgreSQL (since version 9.1) uses SSI (Serializable Snapshot Isolation):

1. Transactions execute concurrently without read locks, reading from consistent snapshots.
2. Lightweight, non-blocking in-memory locks called SIREAD locks track which tuples and index ranges were inspected.
3. The engine monitors for hazardous serialization dependencies—specifically cycles of rw-antidependencies (situations where one transaction reads data that another concurrent transaction later writes).
4. If a dependency cycle is detected, the database engine aborts one of the conflicting transactions with: ERROR: could not serialize access due to read/write dependencies among transactions (SQLSTATE 40001).

Production Pattern: Automated Retries for Serializable Transactions

Under Serializable, transaction aborts are a normal, expected consequence of concurrency. Services must implement robust retry logic:

import org.springframework.dao.CannotSerializeTransactionException;
import org.springframework.retry.annotation.Backoff;
import org.springframework.retry.annotation.Retryable;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Isolation;
import org.springframework.transaction.annotation.Transactional;

@Service
public class HospitalDutyService {

    @Retryable(
        retryFor = { CannotSerializeTransactionException.class },
        maxAttempts = 5,
        backoff = @Backoff(delay = 100, multiplier = 2.0)
    )
    @Transactional(isolation = Isolation.SERIALIZABLE)
    public void leaveOnCallDuty(String doctorName) {
        long activeDoctors = doctorRepository.countByOnCallTrue();
        if (activeDoctors <= 1) {
            throw new IllegalStateException("Cannot release the last on-call physician!");
        }
        doctorRepository.updateOnCall(doctorName, false);
    }
}

---

4 Tricky Questions

1. Why does executing SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; in PostgreSQL not produce dirty reads?

Answer: In PostgreSQL, the Read Uncommitted level is physically absent from the core engine. For ANSI SQL compliance, PostgreSQL parses the READ UNCOMMITTED command without throwing a syntax error, but silently maps it to Read Committed. PostgreSQL’s MVCC visibility logic inspects tuple headers (xmin/xmax) against the status table pg_xact. A row written by an uncommitted transaction is unconditionally invisible to all other transactions at zero extra cost. PostgreSQL is architecturally incapable of returning dirty, uncommitted rows.

---

2. Why does MySQL InnoDB’s Repeatable Read eliminate Phantom Reads, whereas PostgreSQL’s does not?

Answer: Because of differing isolation mechanisms:

• PostgreSQL: Uses pure Snapshot Isolation for Repeatable Read. A snapshot conceals rows inserted by concurrent transactions during read queries. However, subsequent write mutations (UPDATE ... WHERE) apply to all existing rows matching the predicate, exposing write-phantom anomalies.
• MySQL (InnoDB): Combines MVCC snapshots with Next-Key Locks (a combination of a Record Lock on the index entry and a Gap Lock on the empty interval between index entries). Reading an indexed range with locking constructs physically blocks other transactions from inserting new rows into the gap, preventing phantoms.

---

3. How does snapshot generation differ between Read Committed and Repeatable Read in PostgreSQL?

Answer:

• Read Committed: Generates a statement-level snapshot at the beginning of each individual SQL statement. If a transaction executes a query, pauses, and executes the query again, the second query reads an updated snapshot reflecting all transactions committed during the pause.
• Repeatable Read: Generates a transaction-level snapshot once at the start of the first query within the transaction. That snapshot remains frozen until the transaction completes, guaranteeing that repeated queries observe an identical view of the database.

---

4. What is the hidden danger of long-running transactions at Repeatable Read or Serializable levels?

Answer: Long-running transactions hold historical snapshots open, obstructing garbage collection and cleanup:

1. PostgreSQL (Table & Index Bloat): The background VACUUM process cannot remove dead tuple versions as long as there is an active transaction whose xmin is older than the transaction that deleted the tuple. Long transactions stall vacuuming, leading to massive table bloat and performance degradation.
2. MySQL InnoDB (Undo Log Overflow): InnoDB must retain pre-images in the Undo Tablespace. Long transactions prevent the Purge Thread from freeing undo log pages, causing system tablespace files to swell uncontrollably.

---

🎯 Interview Cheat Sheet

• 4 Standard Isolation Levels:
  • Read Uncommitted: Allows Dirty, Non-Repeatable, and Phantom Reads. Mapped to Read Committed in PostgreSQL.
  • Read Committed: Prevents Dirty Reads. Generates a fresh snapshot per statement. Default in PostgreSQL and Oracle.
  • Repeatable Read: Prevents Dirty and Non-Repeatable Reads. Generates a single snapshot per transaction. Default in MySQL.
  • Serializable: Complete isolation. Prevents all anomalies including Phantoms and Write Skew.
• Write Skew Anomaly: Occurs under Snapshot Isolation when transactions concurrently read overlapping state, update disjoint rows, and violate a global invariant. Solved only by Serializable or SELECT FOR UPDATE.
• PostgreSQL SSI: Lock-free serializability using SIREAD locks and conflict graph detection (SQLSTATE 40001).
• Resiliency Pattern: Every service method running at Isolation.SERIALIZABLE must be wrapped with @Retryable to handle serialization conflicts.

Related Topics

• Explain Each Letter of ACID
• What is Read Uncommitted
• What is Read Committed
• What is Repeatable Read
• What is Serializable
• What is the Default Isolation Level in PostgreSQL
• What is the Default Isolation Level in MySQL