---

🟢 Junior Level

A Non-Repeatable Read (also known as a Fuzzy Read) is a concurrency anomaly where a transaction reads the exact same row twice during its execution but receives different column values, because another concurrent transaction updated (UPDATE) or deleted (DELETE) that row and successfully executed a COMMIT in the interim.

Unlike a “Dirty Read”, the data read during a Non-Repeatable Read is authentic and permanently committed. However, it compromises the internal consistency of multi-step business transactions.

Execution Timeline

Timeline  Transaction A (Discount Calculation)          Transaction B (Price Update)
   |      ------------------------------------          ----------------------------
   |  T1: BEGIN;
   |  T2: SELECT price FROM products WHERE id = 1;      -- Price: $1,000
   |  T3:                                               BEGIN;
   |  T4:                                               UPDATE products SET price = 1500 WHERE id = 1;
   |                                                    COMMIT; -- Formally committed!
   |  T5: SELECT price FROM products WHERE id = 1;      -- Price: $1,500! (VALUE CHANGED)
   v  T6: -- Receipt issued at $1,000, but payment charged at $1,500!

Isolation Level Vulnerability

Isolation Level	Non-Repeatable Read Permitted?
Read Uncommitted	⚠️ Permitted
Read Committed	⚠️ Permitted
Repeatable Read	❌ Prevented
Serializable	❌ Prevented

Analogy: Imagine viewing an online flight booking page where a seat is listed at $200. You add it to your cart and enter your payment details. In the two minutes it takes you to type your credit card number, an airline manager updates the seat price to $300. When you click “Confirm Purchase”, your bank account is debited $300 instead of the expected $200.

---

🟡 Middle Level

Formalization ($A_2$ under ANSI SQL-92)

In transaction theory, the $A_2$ phenomenon is formalized as the following interleaved sequence:

\[r_1[x] \dots w_2[x] \dots c_2 \dots r_1[x] \dots c_1\]

1. Transaction $T_1$ reads row $x$ ($r_1[x]$).
2. Transaction $T_2$ mutates row $x$ ($w_2[x]$) and commits ($c_2$).
3. Transaction $T_1$ re-reads row $x$ ($r_1[x]$) and observes the modified value committed by $T_2$.

Non-Repeatable Read vs. Phantom Read

This distinction is a cornerstone of technical database interviews:

Criterion	Non-Repeatable Read (Fuzzy Read)	Phantom Read
Target Rows	Modifications to an existing single row	Appearance or disappearance of new rows
Triggering SQL	UPDATE or DELETE on a specific primary key	INSERT or DELETE matching a range predicate
Query Pattern	Specific row lookup: WHERE id = 10	Range query: WHERE age > 18 or WHERE department = 'IT'
Core Vulnerability	Corrupts Read-Modify-Write calculations	Corrupts aggregate calculations (COUNT, SUM, AVG)

Production Remediation Strategies

Strategy 1: Elevate Isolation to Repeatable Read

Under Repeatable Read, the database freezes a single visibility snapshot for the entire transaction. Repeated SELECT queries continue to read the initial version from the snapshot:

@Transactional(isolation = Isolation.REPEATABLE_READ)
public void generateInvoice(Long orderId) {
    Order order1 = orderRepo.findById(orderId).orElseThrow();
    // Subsequent reads will observe identical data throughout this method
}

Strategy 2: Pessimistic Row Locking (SELECT FOR UPDATE)

When elevating the isolation level for the whole transaction is undesirable (due to serialization aborts or lock scope), lock the specific row explicitly under Read Committed:

public interface OrderRepository extends JpaRepository<Order, Long> {

    @Lock(LockModeType.PESSIMISTIC_WRITE)
    @Query("SELECT o FROM Order o WHERE o.id = :id")
    Optional<Order> findByIdForUpdate(@Param("id") Long id);
}

This issues a SELECT ... FOR UPDATE, acquiring an exclusive row lock (X-Lock) that blocks other transactions from modifying the price until the transaction completes.

---

🔴 Senior Level

Engine Internals: Snapshot Scope in PostgreSQL

In PostgreSQL under Read Committed, the internal kernel routine GetTransactionSnapshot() executes prior to each individual SQL statement:

Transaction T1 (Read Committed):
[Step 1] SELECT price FROM products WHERE id = 1;
         ├── Allocates Snapshot A (sees commits up to T1.1)
         └── Returns price = 100

[Concurrent Transaction T2 executes UPDATE and COMMIT]

[Step 2] SELECT price FROM products WHERE id = 1;
         ├── Allocates Snapshot B (sees all commits up to T1.2, INCLUDING T2!)
         └── Returns price = 150 (Non-Repeatable Read)

Under Repeatable Read, GetTransactionSnapshot() is invoked only once upon the first read query. The snapshot reference is cached in CurrentSnapshot, ensuring immutable visibility.

Hibernate / JPA First-Level Cache Trap

A crucial architectural nuance occurs when interacting with Spring Data JPA:

@Transactional // Defaults to Read Committed
public void processOrder(Long id) {
    Product p1 = productRepo.findById(id).get(); // Price: 100 (SELECT sent to DB; cached in L1)

    // A concurrent transaction updates price to 200 and commits in DB!

    Product p2 = productRepo.findById(id).get(); // WHAT HAPPENS HERE?
    // Hibernate DOES NOT query the database! It returns the existing reference from L1 cache!
    // p1 == p2 is true; p2.getPrice() remains 100!

    // HOWEVER, if a custom JPQL or native query is executed:
    BigDecimal currentPrice = productRepo.findPriceNative(id); // Sent directly to DB!
    // currentPrice == 200!
}

Architectural Takeaway: Hibernate’s First-Level Cache (Persistence Context) automatically masks Non-Repeatable Reads for entity lookups by primary key by caching entity instances in JVM heap memory. However, if application code interleaves findById() with native queries or JPQL aggregations, an internal data desynchronization state occurs within the same method.

---

4 Tricky Questions

1. If a transaction reads a row, and a concurrent transaction completely deletes that row (DELETE) and commits, is that a Non-Repeatable Read or a Phantom Read?

Answer: It is a Non-Repeatable Read. Even though the row physically vanished, the mutation targeted an identifiable existing row specified by its primary key. A subsequent SELECT WHERE id = 1 returns nothing (null), representing an altered state of a previously known single record. A Phantom Read is strictly defined as an operation where the set of rows returned by a predicate range query (such as WHERE category = 'Books') changes because new rows were inserted or removed from the matching range.

---

2. Does @Transactional(readOnly = true) prevent Non-Repeatable Reads in PostgreSQL by default?

Answer: No. In Spring, @Transactional(readOnly = true) translates to the database statement SET TRANSACTION READ ONLY, but preserves the database’s default isolation level (Read Committed in PostgreSQL). Under Read Committed, marking a transaction READ ONLY simply forbids mutating operations (INSERT, UPDATE, DELETE). It does not change the snapshot allocation mechanics: each query continues to generate a fresh statement-level snapshot. Two successive reads of the same record can still yield different values. To ensure repeatable reads, you must explicitly set isolation = Isolation.REPEATABLE_READ.

---

3. Why do enterprise banking systems prefer SELECT FOR UPDATE over elevating transactions to Repeatable Read?

Answer: For two primary reasons:

1. Avoiding Serialization Aborts: In PostgreSQL, if two concurrent Repeatable Read transactions attempt to update the same row, the second transaction is forcibly aborted with ERROR: could not serialize access (40001). The application must handle this by rolling back and retrying. With SELECT FOR UPDATE under Read Committed, the second transaction simply pauses on the row lock and resumes smoothly once the first commits.
2. Minimal Lock Footprint: Locks are scoped strictly to individual target account rows rather than expanding snapshot and version tracking across broad tables.

---

4. What happens to physical row versions in PostgreSQL when a Non-Repeatable Read occurs?

Answer: In PostgreSQL, an UPDATE is physically implemented as an append-only operation:

1. During the initial read, $T_1$ inspects tuple version $v_1$ (where xmin is committed and xmax is unset).
2. Transaction $T_2$ executes an UPDATE: it sets xmax = T_2 on tuple $v_1$, appends a new tuple version $v_2$ with xmin = T_2, and commits.
3. When $T_1$ executes its second read under Read Committed, it generates a new snapshot where $T_2$ is committed.
4. $T_1$ evaluates tuple $v_1$: because $v_1$’s xmax ($T_2$) is now committed, $v_1$ is considered dead. Tuple $v_2$ is evaluated: its xmin ($T_2$) is committed, and its xmax is unset. $T_1$ reads $v_2$, observing the modified values.

---

🎯 Interview Cheat Sheet

• Core Definition: A repeated SELECT of the exact same row returns different values because a concurrent transaction executed an UPDATE/DELETE and committed.
• Isolation Scope: Occurs in Read Uncommitted and Read Committed; prevented in Repeatable Read and Serializable.
• Non-Repeatable vs. Phantom:
  • Non-Repeatable Read: Changes in column values of an existing row (WHERE id = ?).
  • Phantom Read: Changes in the set of rows returned by a range predicate (WHERE age > ?).
• Engine Mechanics: Caused by per-statement MVCC snapshots (GetTransactionSnapshot() per query in PostgreSQL Read Committed).
• Hibernate L1 Cache Trap: findById() returns cached JVM entity references, masking Non-Repeatable Reads unless native or JPQL queries bypass the persistence context.
• Production Defenses: Use @Transactional(isolation = Isolation.REPEATABLE_READ) or pessimistic row locking via SELECT ... FOR UPDATE.

Related Topics

• What is Read Committed
• What is Repeatable Read
• What is Phantom Read
• What is Lost Update