---

🟢 Junior Level

Read Committed is the most widely adopted transaction isolation level in enterprise relational databases. It serves as the default setting for PostgreSQL, Oracle, and Microsoft SQL Server.

Under this level, a transaction is guaranteed to read only data that has been officially committed (COMMIT) by other concurrent transactions prior to the moment of reading. Any in-flight, uncommitted modifications made by other transactions remain strictly invisible.

Core Rules:

1. Guaranteed Dirty Read Protection: A transaction never observes intermediate mutations that could subsequently be aborted via ROLLBACK.
2. Permits Non-Repeatable Reads: If a transaction executes the exact same SELECT twice, rows may return altered values if a concurrent transaction modified and committed those rows in the interim.
3. Permits Phantom Reads: If a transaction repeats a range query (WHERE age > 18), new rows inserted and committed by concurrent transactions will appear in the second result set.

Practical Execution Timeline

Timeline  Transaction A (Account Query)         Transaction B (Fee Deduction)
   |      -----------------------------         -----------------------------
   |  T1: BEGIN;
   |  T2: SELECT balance FROM accounts          -- Balance: $1,000
   |      WHERE id = 1;
   |  T3:                                       BEGIN;
   |  T4:                                       UPDATE accounts SET balance = 900
   |                                            WHERE id = 1;
   |                                            COMMIT; -- Formally committed!
   |  T5: SELECT balance FROM accounts          -- Balance: $900!
   |      WHERE id = 1;                         -- Value changed within Transaction A!
   v  T6: COMMIT;

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

@Service
public class UserService {

    // Default isolation level in PostgreSQL, Oracle, and MS SQL Server
    @Transactional(isolation = Isolation.READ_COMMITTED)
    public UserProfile getUserProfile(Long userId) {
        return userRepository.findById(userId)
            .orElseThrow(() -> new EntityNotFoundException("User not found"));
    }
}

Analogy: Read Committed is like browsing an online news publication. You only see officially published articles (author drafts are hidden). However, if you refresh the homepage after five minutes, headlines and story paragraphs may have been updated or newly added by editors.

---

🟡 Middle Level

Architectural Mechanism: Statement-Level MVCC Snapshots

In engines powered by Multi-Version Concurrency Control (MVCC), such as PostgreSQL and modern SQL engines:

• A new visibility snapshot is created at the start of EVERY individual SQL statement (SELECT, UPDATE, DELETE).
• When a query executes within a long transaction, the engine captures a snapshot of the database state at that precise microsecond.
• A subsequent query executed 10 seconds later generates a brand-new snapshot, immediately incorporating all commits that completed during the 10-second interval.

Transaction T1 (Read Committed):
[Begin T1]
   │
   ├─► SELECT 1 ──► [Snapshot 1 generated: sees database state at T1.1]
   │
   │   (Concurrent Transaction T2 executes COMMIT)
   │
   └─► SELECT 2 ──► [Snapshot 2 RE-GENERATED: sees newly committed changes from T2!]

Read Committed vs. Repeatable Read

Criterion	Read Committed	Repeatable Read
Snapshot Generation	Fresh snapshot before every statement (Statement-level)	Single snapshot created at transaction start (Transaction-level)
Re-reading Same Row	May observe modified values	Strictly returns identical values
Serialization Conflicts	Minimal / None (statements wait on row locks)	Frequent (aborts on concurrent write conflicts)
Database Default	PostgreSQL, Oracle, SQL Server	MySQL (InnoDB)

The Lost Update Trap in Application Code

Read Committed does not protect against Lost Updates during manual application-level Read-Modify-Write cycles:

// ❌ HAZARDOUS READ-MODIFY-WRITE UNDER READ COMMITTED:
@Transactional
public void addBonus(Long accountId, BigDecimal bonus) {
    Account acc = accountRepository.findById(accountId); // Reads balance = $100
    // If a concurrent thread debits $50 and commits right here...
    acc.setBalance(acc.getBalance().add(bonus)); // 100 + 10 = $110
    accountRepository.save(acc); // OVERWRITES and completely obliterates the $50 debit!
}

Remediation Strategies:

1. Atomic In-Place SQL: UPDATE accounts SET balance = balance + :bonus WHERE id = :id.
2. Pessimistic Locking: SELECT ... FOR UPDATE (via Spring Data JPA: @Lock(LockModeType.PESSIMISTIC_WRITE)).
3. Optimistic Locking: Entity version checking via @Version.

---

🔴 Senior Level

PostgreSQL EvalPlanQual (EPQ) Under Concurrent Writes

What happens when two concurrent transactions at Read Committed attempt to update the same row simultaneously?

-- Transaction 1:
UPDATE accounts SET balance = balance - 100 WHERE id = 1; -- Acquires row X-Lock

-- Transaction 2 (Concurrent):
UPDATE accounts SET balance = balance - 50 WHERE id = 1 AND balance >= 100;

1. Transaction $T_2$ identifies the target row via its statement snapshot, attempts to acquire an exclusive row lock (X-Lock), and blocks, waiting for $T_1$ to finish.
2. Transaction $T_1$ executes COMMIT.
3. Transaction $T_2$ wakes up. Because $T_1$’s new row version was not part of $T_2$’s original snapshot, PostgreSQL invokes its internal EvalPlanQual (EPQ) protocol:
   • $T_2$ fetches the latest committed tuple version produced by $T_1$.
   • $T_2$ re-evaluates the query’s WHERE clause (balance >= 100) against this new version.
   • If the condition remains satisfied, $T_2$ executes its update against the new tuple.
   • If the condition is no longer met (e.g., balance dropped below 100), $T_2$ silently ignores the row, returning 0 rows updated without throwing an error.

Hibernate / JPA First-Level Cache Trap on Read Committed

A standard senior interview question: “If the underlying database operates at Read Committed, does Hibernate guarantee repeatable reads when calling findById() twice in the same transaction?”

@Transactional // Database isolation: Read Committed
public void demoCache(Long id) {
    Account a1 = accountRepo.findById(id).get(); // Balance = 100
    // At this instant, an external microservice updates balance = 200 and commits in DB!
    Account a2 = accountRepo.findById(id).get(); // WHAT IS RETURNED?

    System.out.println(a1 == a2); // true!
    System.out.println(a2.getBalance()); // 100 (NOT 200!)
}

Explanation: Hibernate’s First-Level Cache (Persistence Context) indexes managed entity instances by their primary keys within the active Session / EntityManager. When findById(id) is called a second time, Hibernate does not send an SQL query to the database. It immediately returns the existing object reference from JVM memory. Consequently, at the entity level, Hibernate’s Persistence Context effectively emulates Repeatable Read for entity lookups by ID, even when the database runs on Read Committed. To force fresh state from the database, the developer must explicitly call entityManager.refresh(a1) or execute a native SQL query.

---

4 Tricky Questions

1. Why do PostgreSQL and Oracle default to Read Committed, while MySQL InnoDB defaults to Repeatable Read?

Answer:

• PostgreSQL & Oracle: Designed from inception on multi-version concurrency (MVCC). In MVCC, statement-level snapshots are lightweight, readers never block writers, and transactions rarely experience serialization aborts. It represents the optimal balance of throughput and data safety for enterprise transactional systems.
• MySQL (InnoDB): In early MySQL versions, replication relied exclusively on Statement-Based Replication (SBR), which replicated raw SQL statements across nodes. Under Read Committed, differences in transaction commit order between the master and replicas caused data desynchronization. MySQL adopted Repeatable Read with gap locks as its default to guarantee replication determinism.

---

2. Does Read Committed protect against Lost Updates when using atomic updates like UPDATE table SET counter = counter + 1?

Answer: Yes. When an update is executed as a single atomic SQL statement (UPDATE table SET counter = counter + 1 WHERE id = :id), the engine acquires an exclusive write lock (X-Lock) on the row during statement execution. Concurrent transactions are serialized at the lock level. When the next transaction acquires the lock, the engine’s current-read mechanism (such as PostgreSQL’s EvalPlanQual or InnoDB’s locking read) fetches the latest committed value and increments it. No updates are lost. Lost Updates only occur when code uses the application-level “Read-Modify-Write” pattern without locking.

---

3. How does Read Committed in PostgreSQL differ from default Read Committed in Microsoft SQL Server?

Answer:

• PostgreSQL: Uses MVCC. Readers read point-in-time tuple snapshots without taking shared locks, meaning readers never block writers and writers never block readers.
• MS SQL Server (Default): Historically uses Two-Phase Locking (2PL). Readers acquire Shared Locks (S-Locks). If a writer is currently modifying a row (holding an Exclusive X-Lock), concurrent readers block and wait until the writer commits. (Note: SQL Server can enable READ_COMMITTED_SNAPSHOT (RCSI) to use TempDB-based MVCC, mimicking PostgreSQL’s non-blocking behavior).

---

4. What happens when executing SELECT ... FOR UPDATE inside a Read Committed transaction?

Answer: SELECT ... FOR UPDATE performs a Locking Current Read:

1. It bypasses historical MVCC snapshot visibility and reads the most recent committed tuple version directly.
2. It acquires an exclusive row-level lock (X-Lock) that persists until the transaction executes COMMIT or ROLLBACK.
3. Any other transaction attempting to modify or acquire a lock on those rows is placed in a wait queue. This prevents Non-Repeatable Reads and Lost Updates for the locked rows.

---

🎯 Interview Cheat Sheet

• Core Rule: Transactions read only committed data; dirty reads are strictly prevented.
• Snapshot Timing: A new MVCC snapshot is evaluated before every individual SQL statement (Statement-level).
• Anomalies:
  • Dirty Read — Prevented.
  • Non-Repeatable Read — Permitted.
  • Phantom Read — Permitted.
  • Lost Update — Permitted on application Read-Modify-Write; Prevented on atomic SQL updates.
• PostgreSQL EvalPlanQual: Re-evaluates WHERE conditions on the newly committed row version when waiting on concurrent UPDATE locks.
• Hibernate L1 Cache Traps: findById() returns cached JVM references, emulating Repeatable Read within the same session. Use entityManager.refresh() to fetch updated DB state.
• Pessimistic Locking: Use SELECT ... FOR UPDATE (@Lock(LockModeType.PESSIMISTIC_WRITE)) to eliminate read instability for specific rows.

Related Topics

• What Transaction Isolation Levels Exist
• What is Non-Repeatable Read
• What is Phantom Read
• What is the Default Isolation Level in PostgreSQL