---

🟢 Junior Level

In PostgreSQL, the default transaction isolation level is Read Committed.

The Core Concept in 30 Seconds

Under Read Committed, a transaction is guaranteed protection against Dirty Reads: every query sees only data that was successfully committed (COMMITTED) before the current SQL statement began executing.

The defining architectural principle of PostgreSQL at this level:

Each individual SQL statement (SELECT, UPDATE, DELETE) within the same open transaction acquires its own fresh data snapshot (Statement-Level Snapshot)!

If a single open transaction runs SELECT balance FROM accounts WHERE id = 1, pauses, and repeats the identical query, the second query will observe newly committed updates made by concurrent transactions during that pause.

Execution Timeline

Transaction A (Read Committed)              Concurrent Transaction B
------------------------------              ------------------------
BEGIN;
SELECT count(*) FROM orders; -- Returns 100
                                            BEGIN;
                                            INSERT INTO orders (...);
                                            COMMIT; -- Formally committed!
SELECT count(*) FROM orders; -- Returns 101 (Sees new row in fresh snapshot!)
COMMIT;

Anomaly Vulnerability Matrix in PostgreSQL (Read Committed)

ANSI SQL Anomaly	Status in PostgreSQL	Why It Happens
Dirty Read	❌ Prevented	PostgreSQL verifies commit status bits in the tuple header; uncommitted tuples are invisible
Non-Repeatable Read	⚠️ Permitted	Re-executing queries generates a new statement snapshot reflecting recent commits
Phantom Read	⚠️ Permitted	Range queries in a new statement snapshot see newly committed inserted or deleted rows
Lost Update	⚠️ Permitted	Occurs during application-level Read-Modify-Write flows without locking or versioning

Practical Spring Boot Example

In Spring, @Transactional defaults to Isolation.DEFAULT, which resolves to Read Committed on PostgreSQL:

@Service
public class AccountService {

    private final AccountRepository accountRepository;

    public AccountService(AccountRepository accountRepository) {
        this.accountRepository = accountRepository;
    }

    @Transactional // Defaults to Read Committed in PostgreSQL
    public void printBalanceTwice(Long accountId) throws InterruptedException {
        // Query 1: Allocates Statement Snapshot #1
        Account firstRead = accountRepository.findById(accountId).orElseThrow();
        System.out.println("First Read: " + firstRead.getBalance());

        // Simulate 2 seconds of external processing
        // Meanwhile, a concurrent transaction executes: UPDATE accounts SET balance = 5000 WHERE id = 1
        Thread.sleep(2000);

        // Query 2: In plain SQL, this allocates Statement Snapshot #2!
        // NOTE: Hibernate caches entities in its L1 Persistence Context (firstRead == secondRead).
        // To bypass L1 and query PostgreSQL's fresh snapshot, issue a native query or refresh:
        Account freshRead = accountRepository.findByIdFreshNative(accountId);
        System.out.println("Second Read: " + freshRead.getBalance());
        // freshRead.getBalance() reflects the concurrent commit (Non-Repeatable Read)!
    }
}

---

🟡 Middle Level

MVCC Architecture in PostgreSQL

PostgreSQL achieves transaction isolation using Multi-Version Concurrency Control (MVCC). Unlike traditional two-phase locking engines:

Readers never block writers, and writers never block readers.

Data modifications (UPDATE, DELETE) do not overwrite existing bytes in-place on disk. Instead, the engine creates new versions of rows (tuples):

• INSERT: Appends a tuple with the current transaction ID in t_xmin and sets t_xmax = 0.
• DELETE: Leaves the tuple in place but writes the deleting transaction ID into t_xmax.
• UPDATE: Atomically executes a DELETE on the old version (setting its t_xmax) plus an INSERT of the new version (new tuple with t_xmin, linked via the t_ctid pointer).

Table Heap Page:
+---------------------------------------------------------------------------------+
| Version 1: [xmin: 100, xmax: 105, balance: 1000] -> (Dead tuple after commit 105)|
| Version 2: [xmin: 105, xmax: 0,   balance: 1500] -> (Live tuple for snapshot >= 105)|
+---------------------------------------------------------------------------------+

Snapshot Lifecycle in Read Committed vs. Repeatable Read

Inside the PostgreSQL engine, a snapshot is represented by SnapshotData:

• xmin: Lowest active Transaction ID (all $XID < xmin$ are guaranteed completed).
• xmax: First unassigned Transaction ID (all $XID \ge xmax$ are invisible).
• xip[]: List of in-progress Transaction IDs between xmin and xmax.

Read Committed:
  Query 1 (SELECT) -----> GetTransactionSnapshot() -> Snapshot #1 allocated
  Query 2 (SELECT) -----> GetTransactionSnapshot() -> Snapshot #2 allocated anew!

Repeatable Read:
  Query 1 (SELECT) -----> GetTransactionSnapshot() -> Snapshot #1 frozen for entire transaction
  Query 2 (SELECT) -----> Reuses Snapshot #1!

Under Read Committed, PostgreSQL calls GetTransactionSnapshot() before every single SQL statement, minimizing the retention of old data versions and boosting throughput.

The EvalPlanQual (EPQ) Mechanism for Concurrent Updates

When two transactions attempt to update the same row concurrently under Read Committed:

-- Initial state: id = 1, balance = 1000, is_active = true

-- Transaction T1:
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1 AND is_active = true;
-- T1 acquires an exclusive row lock (RowExclusiveLock / XMAX)

-- Transaction T2 (concurrent):
BEGIN;
UPDATE accounts SET is_active = false WHERE id = 1 AND is_active = true;
-- T2 finds the row locked by T1 and pauses, waiting for T1 to finish.

When Transaction T1 commits:

1. Transaction T2 unblocks.
2. Because T2 runs under Read Committed, it does not fail with a serialization failure.
3. PostgreSQL triggers EvalPlanQual (EPQ): T2 fetches the latest committed version of the row (produced by T1) via t_ctid and re-evaluates the query’s WHERE clause.
4. In this example, is_active = true remains satisfied (T1 only changed balance). T2 successfully applies its update, setting is_active = false.
5. If T1 had set is_active = false, the EPQ re-check would evaluate to false, and T2 would silently skip updating the row (updating 0 rows).

---

🔴 Senior Level

PostgreSQL Kernel Internals: Commit Log (CLOG) and Hint Bits

Checking the transaction manager (ProcArrayLock) on every visibility check (HeapTupleSatisfiesVisibility) would cause disastrous CPU spinlock contention. PostgreSQL optimizes visibility checks through two layers:

1. CLOG (pg_xact): A shared memory ring buffer and on-disk files storing 2 bits per transaction:
   • 00 — TRANSACTION_STATUS_IN_PROGRESS
   • 01 — TRANSACTION_STATUS_COMMITTED
   • 10 — TRANSACTION_STATUS_ABORTED
   • 11 — TRANSACTION_STATUS_SUB_COMMITTED
2. Hint Bits in t_infomask:
   • The first reader that inspects a tuple checks pg_xact, resolves the creator transaction’s status (xmin), and mutates the tuple header in shared buffer memory directly, setting hint bits:
     • HEAP_XMIN_COMMITTED
     • HEAP_XMIN_INVALID (rolled back)
     • HEAP_XMAX_COMMITTED
   • Subsequent queries across all connections inspect these hint bits directly without accessing pg_xact or acquiring lock manager spinlocks.

HeapTuple Header Structure (HeapTupleHeaderData):
+-------------------------------------------------------------------------------+
| t_xmin (32 bit) | t_xmax (32 bit) | t_cid / t_xvac (32 bit) | t_ctid (6 bytes) |
| t_infomask2     | t_infomask (16 bit: Hint Bits!)          | t_hoff (offset)   |
+-------------------------------------------------------------------------------+

The Impact on VACUUM, Bloat, and Transaction Wraparound

Even under Read Committed, long-running transactions or orphaned idle in transaction sessions cause severe cluster degradation:

1. Holding OldestXmin Back: An uncommitted transaction holds back the global OldestXmin horizon.
2. Blocking autovacuum: PostgreSQL’s autovacuum cannot purge dead tuples whose $xmax \ge \text{OldestXmin}$.
3. Table & Index Bloat: Concurrent updates accumulate dead tuples on disk. A 5 GB table can rapidly balloon to 50 GB.
4. Transaction ID Wraparound: If the transaction horizon lags by $2^{31}$ transactions, PostgreSQL enforces a safety emergency shutdown into read-only mode to prevent catastrophic data corruption.

-- Monitoring dangerous long-running transactions holding autovacuum back:
SELECT pid, 
       usename, 
       state, 
       now() - xact_start AS xact_duration, 
       now() - state_change AS state_duration, 
       query 
FROM pg_stat_activity 
WHERE state IN ('active', 'idle in transaction') 
  AND xact_start < now() - INTERVAL '5 minutes'
ORDER BY xact_start ASC;

Highload Defenses Against Lost Update under Read Committed

@Service
public class HighloadWalletService {

    private final WalletRepository walletRepository;

    public HighloadWalletService(WalletRepository walletRepository) {
        this.walletRepository = walletRepository;
    }

    // Strategy 1: Atomic In-Place SQL Update (Zero Application Contention)
    @Transactional
    public void creditBalanceAtomic(Long walletId, BigDecimal amount) {
        int updated = walletRepository.incrementBalanceAtomic(walletId, amount);
        if (updated == 0) {
            throw new IllegalStateException("Wallet not found or deactivated");
        }
    }

    // Strategy 2: Pessimistic Row Locking (SELECT ... FOR UPDATE)
    @Transactional
    public void debitBalancePessimistic(Long walletId, BigDecimal amount) {
        Wallet wallet = walletRepository.findByIdForUpdate(walletId)
                .orElseThrow(() -> new IllegalArgumentException("Wallet not found"));

        if (wallet.getBalance().compareTo(amount) < 0) {
            throw new IllegalStateException("Insufficient funds");
        }
        wallet.setBalance(wallet.getBalance().subtract(amount));
    }
}

---

4 Tricky Questions

1. What happens if you execute SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED in PostgreSQL?

Answer: The query executes successfully without syntax errors, but the isolation level is silently and automatically promoted to Read Committed. In PostgreSQL’s MVCC storage architecture, reading uncommitted (dirty) data is physically impossible:

• The visibility checking routines (HeapTupleSatisfiesVisibility) evaluate whether a tuple’s creating transaction has committed via CLOG status bits. If a transaction is IN_PROGRESS, the tuple is invisible to all other sessions.
• Enabling Dirty Reads would require disabling transaction visibility validation, breaking consistency across heap pages, WAL, index trees, and TOAST storage.
• PostgreSQL accepts the READ UNCOMMITTED syntax purely for ANSI SQL compliance, mapping it directly to Read Committed.

---

2. How does the EvalPlanQual (EPQ) re-check work, and can an UPDATE silently skip rows?

Answer: Yes, an UPDATE can silently skip rows. When Transaction T2 attempts to update a row locked by Transaction T1:

1. T2 pauses on the row lock held by T1.
2. Once T1 commits, T2 wakes up and follows the tuple pointer t_ctid to inspect the updated version written by T1.
3. PostgreSQL runs EvalPlanQual: it feeds the newly committed tuple into T2’s original WHERE clause.
4. If the predicate remains true, T2 applies its update to the new version.
5. If T1’s update modified columns such that T2’s WHERE predicate is no longer satisfied (e.g., T1 changed status = 'PENDING' to status = 'COMPLETED'), EPQ evaluates to false. T2 skips the row without raising an error, and the updated row count returned is 0.

---

3. Why can SELECT ... FOR UPDATE in PostgreSQL under Read Committed return a row that violates the original WHERE condition in complex JOINs?

Answer: This is an established behavioral edge case in EvalPlanQual during multi-table joins. Consider:

SELECT * FROM parent p JOIN child c ON p.id = c.parent_id 
WHERE c.status = 'ACTIVE' FOR UPDATE OF p;

1. The query executor builds an execution plan and finds a matching row pair where c.status = 'ACTIVE'.
2. When attempting to lock the parent row, the engine discovers a concurrent transaction just updated parent and committed.
3. EPQ fetches the new parent tuple and re-evaluates the join.
4. In older PostgreSQL versions (pre-PostgreSQL 12), EPQ re-evaluated the join condition using the original snapshot’s child tuple rather than re-reading a fresh child tuple. If a concurrent transaction had updated child to INACTIVE, the query could still lock and return the parent row. PostgreSQL 12+ substantially overhauled EPQ join handling, but best practice dictates locking both joined tables explicitly: FOR UPDATE OF p, c.

---

4. How does Read Committed in PostgreSQL differ from Read Committed in Oracle and Microsoft SQL Server?

Answer:

• PostgreSQL vs. Oracle: Both engines use non-blocking MVCC readers. However, Oracle stores historical row versions in a dedicated Undo Tablespace and modifies heap tables in-place. PostgreSQL writes new tuple versions directly into the main table heap pages, requiring the background VACUUM daemon to reclaim dead tuples. If Oracle runs out of undo space under long transactions, it throws ORA-01555: snapshot too old, whereas PostgreSQL experiences table and index bloat.
• PostgreSQL vs. MS SQL Server: By default, standard MS SQL Server uses a locking concurrency model for Read Committed: SELECT queries acquire short-term Shared Locks (S-locks) that block concurrent writers and wait for active writers to finish. To achieve PostgreSQL’s lock-free read behavior in MS SQL Server, administrators must explicitly enable Read Committed Snapshot Isolation (RCSI), which utilizes the tempdb system database for row versioning.

---

🎯 Interview Cheat Sheet

• Default Isolation: PostgreSQL defaults to Read Committed.
• Core Mechanism: Implemented via MVCC; readers never block writers, and writers never block readers.
• Statement Snapshot: Each individual SQL statement gets a fresh snapshot (GetTransactionSnapshot()).
• Tuple Structure: t_xmin (creating transaction), t_xmax (deleting/updating transaction), and t_infomask (Hint Bits).
• EvalPlanQual (EPQ): Mechanism that re-evaluates WHERE conditions on the newest committed tuple version when unblocking after a concurrent update.
• READ UNCOMMITTED Behavior: Accepted syntactically, but automatically elevated to Read Committed.
• Production Bloat Hazard: Long-running transactions or idle in transaction sessions hold back OldestXmin, preventing autovacuum from reclaiming dead tuples and leading to table bloat.

Related Topics

• What is Read Committed
• What is Repeatable Read
• What is the Default Isolation Level in MySQL
• What is Lost Update
• What is Serializable