Java Interview Questions

Home / All Answers / Transactions

Question 4 · Section 11

What is Read Committed?

The second SELECT returned a different value because Transaction B committed its changes.

English Transactions Source Markdown
Language versions: English Russian Ukrainian

🟢 Junior Level

Read Committed is an isolation level where a transaction only sees data that has been officially confirmed (COMMIT) by other transactions.

Key Properties

  • Dirty Read: Prohibited — you never see uncommitted data because the DBMS only shows row versions whose authoring transaction has already COMMITed.
  • Non-Repeatable Read: Possible — the same query may return different results
  • Phantom Read: Possible — new rows may appear on repeated queries

Simple Example

Transaction A: SELECT balance FROM accounts WHERE id = 1; -- Returned 100
Transaction B: UPDATE accounts SET balance = 200 WHERE id = 1; COMMIT;
Transaction A: SELECT balance FROM accounts WHERE id = 1; -- Returned 200!

The second SELECT returned a different value because Transaction B committed its changes.

Where It Is Used

This is the default level in PostgreSQL, Oracle, and SQL Server. The most common level for regular applications.

🟡 Middle Level

Implementation Mechanism via MVCC

In databases with MVCC (PostgreSQL), at the Read Committed level, a data snapshot is created before each query within the transaction.

Key difference from Repeatable Read:

  • Read Committed: new snapshot before each statement
  • Repeatable Read: one snapshot for the entire transaction

Why It’s the “Gold Standard”

  1. Balance: Sufficient data accuracy for most business processes with minimal overhead
  2. No blocking: In MVCC, readers don’t block writers and vice versa
  3. Minimal errors: On Read Committed, the DBMS never aborts a transaction due to isolation conflicts (unlike Serializable). The only errors are constraint violations or app-level logic.

Anomalies at This Level

Non-repeatable Read

-- Transaction A (Read Committed)
BEGIN;
SELECT price FROM products WHERE id = 1;  -- 100
-- Another transaction: UPDATE products SET price = 150 WHERE id = 1; COMMIT;
SELECT price FROM products WHERE id = 1;  -- 150 (changed!)
COMMIT;

Why? On Read Committed, each SELECT gets its own snapshot. Between the first and second SELECT, another transaction managed to commit changes, and the second snapshot sees them.

Phantom Read

-- Transaction A
BEGIN;
SELECT COUNT(*) FROM users WHERE age > 20;  -- 5 rows
-- Another transaction: INSERT INTO users (age) VALUES (25); COMMIT;
SELECT COUNT(*) FROM users WHERE age > 20;  -- 6 rows (a new one appeared!)
COMMIT;

How to Avoid Anomalies

If data stability is needed within a transaction:

  • SELECT … FOR UPDATE: Locks rows until the end of the transaction
  • Raise the level to Repeatable Read: For the entire transaction

Features in PostgreSQL

At the Read Committed level, each statement sees a database snapshot taken at the start of that statement’s execution, not at the start of the entire transaction.

🔴 Senior Level

MVCC Snapshot Mechanics in PostgreSQL

How Snapshots Work Internally

  • Each snapshot contains:
    • xmin: earliest transaction ID that is still running
    • xmax: first transaction ID that has not yet started
    • xip_list: list of in-progress transaction IDs
  • Visibility rule: tuple is visible if:
    • xmin < snapshot.xmin (committed before our snapshot)
    • xmax is not set OR xmax >= snapshot.xmin (not yet deleted)

Read Committed Snapshot Behavior

  • New snapshot created for each statement, not each transaction
  • This means BETWEEN two SELECTs in the same transaction, another transaction can commit changes
  • The second SELECT will see those changes because it gets a fresh snapshot

Performance Characteristics

Advantages

  • Zero blocking: Readers never block writers, writers never block readers
  • No serialization failures: Unlike Serializable, no transaction aborts due to conflicts
  • Minimal overhead: Snapshot creation is cheap (just captures current txid state)

Trade-offs

  • Non-repeatable reads: Application must handle potentially different results
  • Phantom reads: Range queries can return different row counts
  • Business logic complexity: Multi-step calculations may need explicit locking

SELECT FOR UPDATE — Deep Dive

SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
  • Acquires exclusive row-level lock
  • Other transactions attempting FOR UPDATE on the same row will block
  • Prevents both non-repeatable reads AND lost updates for that row
  • Lock held until transaction ends (COMMIT/ROLLBACK)
  • Can cause deadlocks if not used carefully

When to use:

  • Read-modify-write patterns (balance calculations)
  • When you need Repeatable Read behavior for specific rows only
  • Avoiding lost update without changing isolation level

Production Patterns

Pattern 1: Optimistic approach (default Read Committed)

@Transactional // Uses Read Committed
public void transfer(Long fromId, Long toId, BigDecimal amount) {
    // If conflict occurs, handle at application level
    Account from = repo.findById(fromId);
    Account to = repo.findById(toId);
    // ... logic
}

Pattern 2: Pessimistic approach (FOR UPDATE)

@Query("SELECT a FROM Account a WHERE a.id = :id FOR UPDATE")
Account findByIdForUpdate(@Param("id") Long id);

@Transactional
public void transfer(Long fromId, Long toId, BigDecimal amount) {
    Account from = repo.findByIdForUpdate(fromId); // Locked!
    Account to = repo.findByIdForUpdate(toId);     // Locked!
    // Safe to modify
}

Read Committed vs Read Committed Snapshot (SQL Server)

SQL Server has two flavors:

  • Read Committed (locking): Uses shared locks, readers can block writers
  • Read Committed Snapshot (RCSI): Uses row versioning like PostgreSQL MVCC

PostgreSQL always uses the MVCC approach — no locking-based Read Committed exists.

Monitoring and Tuning

  • Track tup_fetched vs tup_returned ratios (sequential vs index scans)
  • Monitor deadlocks — Read Committed has the lowest deadlock rate
  • Check temp_files and temp_bytes — complex queries on RC may need more temp space
  • Long-running transactions on RC don’t cause bloat (unlike RR/Serializable)

When NOT to Use Read Committed

  • Financial invariants requiring multi-step verification
  • Reports that must be self-consistent
  • When application logic assumes stable reads
  • Distributed transactions requiring strict consistency

🎯 Interview Cheat Sheet

Must know:

  • Read Committed — the default level in PostgreSQL, Oracle, SQL Server
  • Prohibits dirty reads but allows non-repeatable and phantom reads
  • Each SELECT gets a new snapshot — two SELECTs in one transaction may return different data
  • Implemented via MVCC — readers don’t block writers
  • SELECT … FOR UPDATE locks rows to prevent lost updates
  • 90% of applications work fine on Read Committed

Common follow-up questions:

  • How does Read Committed differ from Repeatable Read? — RC: snapshot per statement, RR: one snapshot per transaction
  • How to avoid non-repeatable read on RC? — SELECT FOR UPDATE or raise to RR
  • Why is RC a good default? — Balance of protection and performance, no serialization failures
  • What is EvalPlanQual in PostgreSQL? — PG mechanism for re-evaluating WHERE clause on concurrent UPDATE

Red flags (DO NOT say):

  • “Read Committed = full isolation” — allows non-repeatable and phantom reads
  • “Two SELECTs in a transaction always return the same result” — not true on RC
  • “RC requires locks” — MVCC works without read locks

Related topics:

  • [[2. What transaction isolation levels exist]]
  • [[8. What is Non-Repeatable Read]]
  • [[9. What is Phantom Read]]
  • [[11. What is the default isolation level in PostgreSQL]]