What is Repeatable Read

Unlike Read Committed, which re-evaluates visibility before every single SQL statement, Repeatable Read adheres to a Transaction-Level Snapshot:

🟢 Junior Level

Repeatable Read is a transaction isolation level that guarantees: once a transaction reads a row of data, all subsequent reads of that exact same row will return identical values throughout the duration of the transaction, even if concurrent transactions alter that row and execute a COMMIT.

Key Characteristics:

Guaranteed Dirty Read Prevention: Uncommitted changes made by other concurrent transactions are strictly invisible.
Guaranteed Non-Repeatable Read Prevention: Data values read once remain frozen in time for the reading transaction.
Phantom Reads: Permitted under the ANSI SQL standard, although prevented in MySQL (InnoDB) and PostgreSQL’s standard query paths via engine-specific mechanisms.

Execution Timeline

Timeline  Transaction A (Repeatable Read)       Transaction B (External Update)
   |      -------------------------------       -------------------------------
   |  T1: BEGIN;
   |  T2: SELECT balance FROM accounts          -- Balance: $1,000
   |      WHERE id = 1;
   |  T3:                                       BEGIN;
   |  T4:                                       UPDATE accounts SET balance = 500
   |                                            WHERE id = 1;
   |                                            COMMIT; -- Formally committed!
   |  T5: SELECT balance FROM accounts          -- Balance: $1,000!
   |      WHERE id = 1;                         -- T1 observes original snapshot value!
   v  T6: COMMIT;

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

@Service
public class BillingReportService {

    // Ensures financial report consistency across long-running queries
    @Transactional(isolation = Isolation.REPEATABLE_READ, readOnly = true)
    public MonthlyReport generateReport(Long companyId) {
        // All subsequent queries see the snapshot taken at the start of the first query
        BigDecimal totalDebit = ledgerRepository.sumDebit(companyId);
        BigDecimal totalCredit = ledgerRepository.sumCredit(companyId);
        return new MonthlyReport(totalDebit, totalCredit);
    }
}

Analogy: Repeatable Read is like taking a photographic snapshot of an archival document the moment you enter a library reading room. Even if archivists retrieve and overwrite the physical paper document on the shelf, the photograph on your phone screen remains permanently unaltered.

🟡 Middle Level

The Transaction-Level MVCC Snapshot Model

Unlike Read Committed, which re-evaluates visibility before every single SQL statement, Repeatable Read adheres to a Transaction-Level Snapshot:

The visibility snapshot is captured exactly once when the transaction issues its first read query.
The snapshot structure stores:
- xmin: The oldest transaction ID that was still active when the snapshot was initialized.
- xmax: The first unassigned transaction ID.
- xip_list: The active in-progress transaction IDs running concurrently at snapshot inception.
All subsequent SELECT queries reuse this identical snapshot, filtering out any commits finalized after the snapshot timestamp.

Engine Divergence: MySQL (InnoDB) vs. PostgreSQL

Criterion	MySQL (InnoDB)	PostgreSQL
Default Status	Default Engine Setting	Explicit Opt-In (Default is Read Committed)
Phantom Protection	Next-Key Locks (Record Lock + Gap Lock)	MVCC Snapshot isolation (no range locks)
Concurrent `UPDATE` Conflicts	Blocks on row lock until lock is released	Aborts with serialization error (`40001`)
Deadlock Frequency	Higher (due to gap lock intersections)	Low

Built-in Lost Update Protection in PostgreSQL

In PostgreSQL, Repeatable Read provides native protection against Lost Updates:

-- Transaction T1:
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1; -- 100

-- Transaction T2 (Concurrent):
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
UPDATE accounts SET balance = 80 WHERE id = 1;
COMMIT;

-- Transaction T1 attempts to modify the same row:
UPDATE accounts SET balance = 90 WHERE id = 1;
-- ERROR: could not serialize access due to concurrent update (SQLSTATE 40001)

Because the row was modified by $T_2$ after $T_1$’s snapshot was taken, PostgreSQL prohibits an inconsistent update on an outdated row version and forces an abort of $T_1$.

🔴 Senior Level

The Write Skew Anomaly: Why Repeatable Read $\ne$ Serializable

Repeatable Read protects against write conflicts on the same row, but is powerless against Write Skew, where an invariant spans multiple distinct rows.

Concrete Banking Invariant Example:

Account Rule: Total balance across Account $A$ and Account $B$ must remain non-negative ($A + B \ge 0$).
Initial State: $A = 100$, $B = 100$ ($\text{Total} = 200$).

Transaction T1 (Withdraw $150 from Account A):
  1. Reads A (100) and B (100). Verifies: 100 + 100 >= 150 (Valid).
  2. UPDATE accounts SET balance = balance - 150 WHERE id = 'A'; (Balance A = -50)

Transaction T2 (Concurrently withdraws $150 from Account B):
  1. Reads A (100) and B (100) from its snapshot. Verifies: 100 + 100 >= 150 (Valid).
  2. UPDATE accounts SET balance = balance - 150 WHERE id = 'B'; (Balance B = -50)

Both transactions COMMIT successfully under Repeatable Read!
(T1 modified row A, T2 modified row B — no physical row-level collision occurred!)
FINAL STATE: A = -50, B = -50. Total Balance = -100! (Invariant shattered!)

To eliminate Write Skew, systems must migrate to SERIALIZABLE isolation or employ explicit pessimistic locking (SELECT ... FOR UPDATE).

Table Bloat & VACUUM Stalls in PostgreSQL

When a Repeatable Read transaction remains open for an extended duration (e.g., an un-tuned analytics query running for 45 minutes):

The transaction’s xmin holds the database xmin horizon back in the past.
PostgreSQL’s background autovacuum cannot clean up or reclaim any dead row versions (dead tuples) deleted by other short-lived transactions if those tuples were modified after the long transaction began.
Impact: High-volume tables experience severe Table Bloat and index bloat, degrading disk capacity and polluting CPU RAM cache lines (shared_buffers).

4 Tricky Questions

1. Can a Phantom Read occur in MySQL InnoDB under `Repeatable Read`?

Answer: Yes, in a subtle edge-case scenario! In MySQL InnoDB, pure SELECT statements use consistent MVCC snapshots and will never observe phantoms. However, if a transaction executes a write operation (UPDATE) matching a row newly inserted by a concurrent transaction, a phantom read is exposed:

-- Transaction T1 (Repeatable Read):
SELECT * FROM users WHERE id = 10; -- Empty (row does not exist in snapshot)

-- Transaction T2:
INSERT INTO users (id, name) VALUES (10, 'Phantom');
COMMIT;

-- Transaction T1:
SELECT * FROM users WHERE id = 10; -- Still empty (protected by snapshot)
-- But now T1 updates that row:
UPDATE users SET name = 'Caught' WHERE id = 10; -- 1 row affected!
-- Subsequent SELECT:
SELECT * FROM users WHERE id = 10; -- ROW SUDDENLY APPEARS! (Phantom Read)

Why this happens: An UPDATE operates as a Locking Current Read. It discovers the physical row, updates it, and writes $T_1$’s transaction ID into the row header. Under MVCC rules, a transaction always sees versions created or modified by itself. The subsequent SELECT renders the newly claimed row.

2. Why does a concurrent `UPDATE` conflict trigger an exception in PostgreSQL Repeatable Read, whereas on `Read Committed` it succeeds?

Answer:

On Read Committed: PostgreSQL uses EvalPlanQual (EPQ). When $T_1$ commits, $T_2$ wakes up, reads the latest committed row version, re-evaluates the WHERE condition, and applies the update.
On Repeatable Read: Snapshots are strictly immutable. Updating a row committed by a concurrent transaction after $T_1$’s snapshot was frozen would violate repeatable read guarantees (modifying state that does not exist in or conflicts with the transaction’s frozen view). PostgreSQL forbids EPQ under Repeatable Read and immediately aborts the transaction with ERROR: could not serialize access due to concurrent update (SQLSTATE 40001).

3. Why do Deadlocks occur much more frequently in MySQL on `Repeatable Read` than on `Read Committed`?

Answer: Because of Next-Key Locks (Record Lock + Gap Lock). On Repeatable Read, range queries and queries using non-unique indexes lock not only the physical records, but also the empty gap intervals between index entries to prevent phantom insertions. When two concurrent transactions insert data into adjacent or overlapping gaps, their gap locks conflict. When both transactions wait for each other to release intervening range locks, a classic relational deadlock occurs: Deadlock found when trying to get lock; try restarting transaction. Under Read Committed, gap locking is disabled (except for foreign-key checks and duplicate-key checks), virtually eliminating gap lock deadlocks.

4. How should a Spring application be configured when using `@Transactional(isolation = Isolation.REPEATABLE_READ)`?

Answer: Because database engines (PostgreSQL, CockroachDB) abort conflicting writes under Repeatable Read with serialization exceptions (CannotSerializeTransactionException), the Spring service method must be idempotent and wrapped with an automated retry mechanism:

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 SafeOrderService {

    @Retryable(
        retryFor = { CannotSerializeTransactionException.class },
        maxAttempts = 3,
        backoff = @Backoff(delay = 50, multiplier = 2.0)
    )
    @Transactional(isolation = Isolation.REPEATABLE_READ)
    public void processOrder(Long orderId, BigDecimal amount) {
        Order order = orderRepository.findById(orderId).orElseThrow();
        order.setAmount(order.getAmount().add(amount));
        orderRepository.save(order);
    }
}

Without @Retryable, concurrent updates under Repeatable Read result in unhandled 500 server errors in customer-facing HTTP requests.

🎯 Interview Cheat Sheet

Core Guarantee: Data read once returns the exact same values on all subsequent reads within the transaction.
Snapshot Behavior: A single visibility snapshot is captured at the start of the transaction (Transaction-level).
Anomalies:
- Dirty Read — Prevented.
- Non-Repeatable Read — Prevented.
- Phantom Read — Prevented in practice in MySQL (Next-Key Locks) and PostgreSQL; edge-case phantoms possible in MySQL via update-then-read.
- Write Skew — Permitted (requires Serializable).
PostgreSQL Write Conflicts: Aborts with ERROR: could not serialize access (40001); requires Spring @Retryable.
MySQL Gap Locks: Repeatable Read enables Gap Locks, which drastically increases deadlock frequency compared to Read Committed.
Table Bloat Hazard: Long-running transactions freeze the xmin horizon, preventing VACUUM from purging dead tuples.