---

🟢 Junior Level

A Dirty Read is a classical database concurrency anomaly where a transaction reads intermediate, uncommitted modifications made by another concurrent transaction that has not yet executed a COMMIT.

If the transaction that performed the write subsequently fails, encounters an exception, or rolls back (ROLLBACK), the data read by the first transaction becomes false (“dirty” data) because it never officially existed in the database.

Practical Execution Timeline

Timeline  Transaction A (Account Top-up)        Transaction B (Credit Approval)
   |      ------------------------------        -------------------------------
   |  T1: BEGIN;
   |  T2: UPDATE accounts SET balance = 500_000
   |      WHERE id = 42;
   |  T3:                                       BEGIN;
   |  T4:                                       SELECT balance FROM accounts WHERE id = 42;
   |                                            -- Reads $500,000! (DIRTY DATA)
   |  T5:                                       -- Approves loan based on $500,000 balance!
   |  T6: ROLLBACK; -- Bank error!
   |      Balance restored to $10,000!
   v  T7:                                       COMMIT; -- Loan granted to an insolvent user!

Permitted vs. Prevented Isolation Levels

Isolation Level	Dirty Read Permitted?
Read Uncommitted	⚠️ Permitted (The only level allowing it)
Read Committed	❌ Prevented
Repeatable Read	❌ Prevented
Serializable	❌ Prevented

Analogy: Imagine copying a complex formula from a classmate’s rough draft paper while they step away for lunch. While away, the professor spots a major calculation error in the formula, and your classmate erases it. If you submit your final exam using that erased formula, your work is built on phantom, invalid data.

---

🟡 Middle Level

Formal Definition: ANSI SQL vs. Berenson et al.

In the classic ANSI SQL-92 standard, Dirty Read was formally categorized as anomaly $A_1$:

\(w_1[x] \dots r_2[x] \dots (a_1 \text{ and } c_2 \text{ in any order})\) Transaction $T_1$ modifies $x$, Transaction $T_2$ reads $x$, then $T_1$ aborts ($a_1$) while $T_2$ commits ($c_2$).

In their landmark 1995 paper (“A Critique of ANSI SQL Isolation Levels”, Jim Gray, Hal Berenson et al.), the authors proved that the ANSI definition was incomplete and formulated the broader, stricter phenomenon $P_1$:

\(w_1[x] \dots r_2[x] \dots (c_1 \text{ or } a_1)\) Transaction $T_2$ must not read value $x$ until Transaction $T_1$ has definitively completed via commit or abort.

How Relational Database Engines Prevent Dirty Reads

                   DEFENSE AGAINST DIRTY READS
                                │
        ┌───────────────────────┴───────────────────────┐
        ▼                                               ▼
PESSIMISTIC TWO-PHASE LOCKING (2PL)             MULTI-VERSION CONCURRENCY (MVCC)
- Writer acquires Exclusive Lock (X-Lock)       - Writer appends a new tuple version
- Reader requests Shared Lock (S-Lock)          - New version tagged with in-progress xmin
- S-Lock conflicts with X-Lock: reader blocks!  - Readers instantly read old version from Snapshot
- Example: Microsoft SQL Server (Default)       - Example: PostgreSQL, Oracle, MySQL InnoDB

Spring / Java Service Example

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

@Service
public class AccountOperationService {

    private final AccountRepository accountRepository;

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

    // Read Committed guarantees total immunity from dirty reads:
    @Transactional(isolation = Isolation.READ_COMMITTED)
    public BigDecimal checkVerifiedBalance(Long accountId) {
        // Query will never observe uncommitted in-flight modifications from other threads
        return accountRepository.findBalanceById(accountId);
    }
}

---

🔴 Senior Level

PostgreSQL Hardware-Level Visibility Checks (HeapTupleSatisfiesMVCC)

In PostgreSQL, dirty read prevention is fundamentally embedded into the tuple header structure inside each 8 KB data page (Heap Page):

Every tuple header stores two 32-bit transaction identifiers:

• t_xmin: The ID of the transaction that created this row version.
• t_xmax: The ID of the transaction that updated or deleted this row version.

When executing any query, the kernel function HeapTupleSatisfiesMVCC checks t_xmin against the in-memory transaction status bitmask (pg_xact):

// Simplified excerpt from PostgreSQL source code (tqual.c):
if (!TransactionIdDidCommit(tuple->t_xmin)) {
    if (TransactionIdIsCurrentTransactionId(tuple->t_xmin)) {
        // We see our own in-flight uncommitted writes
        return true;
    } else {
        // Another transaction's uncommitted write (IN_PROGRESS) -> ROW IS INVISIBLE!
        return false;
    }
}

Key Architectural Insight: In PostgreSQL, preventing Dirty Reads requires zero row locking, zero latch contention, and zero thread waiting. An uncommitted row is recognized as invisible via a few CPU bitmask comparison instructions. Because dirty read prevention has zero runtime overhead, PostgreSQL does not physically support Read Uncommitted.

Cascading Failure Chain (Silent Corruption)

Dirty Reads are considered the most catastrophic anomaly in distributed systems because:

1. Zero Database Warnings (Silent Corruption): The database engine raises no exception, warning, or deadlock notice. The application processes the dirty data assuming it is completely valid.
2. Cascading Domino Effect: \(\text{Dirty Read } \to \text{ Erroneous Business Decision } \to \text{ Mutation Committed in DB } \to \text{ Outbound External API Call}\) Even if the database rolls back the initial transaction, the second transaction may have already executed an irrevocable side effect (such as dispatching funds through a payment gateway or signaling a physical warehouse crane).

---

4 Tricky Questions

1. If a transaction modifies a row and then reads that same row before committing, is that considered a Dirty Read?

Answer: No. Reading your own uncommitted mutations (Own-XID Visibility / Read-Your-Own-Writes) is standard, mandatory behavior across all SQL databases. A transaction must always observe the consequences of its own prior statements. The definition of a Dirty Read applies exclusively to reading in-flight, uncommitted modifications made by other concurrent transactions.

---

2. What is the fundamental difference between Dirty Read ($A_1$) and Dirty Write ($G_0$)?

Answer:

• Dirty Read ($A_1$): Transaction $T_2$ reads an uncommitted row modified by $T_1$, which then rolls back. Permitted only under Read Uncommitted.
• Dirty Write ($G_0$): Transaction $T_2$ overwrites (UPDATE or DELETE) an uncommitted row modified by $T_1$. Dirty Writes are strictly forbidden across ALL isolation levels in ALL relational database engines (including Read Uncommitted). If Dirty Writes were permitted, the engine could not execute a ROLLBACK (it would be impossible to determine which prior image to restore), causing physical database corruption. All engines enforce immediate exclusive write locks (X-Locks) on data modifications until transaction commit or rollback.

---

3. Why can querying with WITH (NOLOCK) in Microsoft SQL Server return duplicate rows for a single entity?

Answer: The query hint WITH (NOLOCK) activates un-locked scanning (equivalent to Read Uncommitted). In page-based B-Tree storage engines, concurrent inserts trigger Page Splits: when an 8 KB leaf page fills up, the engine allocates a new page and moves half the rows to it. When a NOLOCK query executes an Allocation Order Scan (scanning pages in physical disk allocation sequence), it may read a row on its initial page, and later, as the scan progresses through newly allocated pages, encounter the exact same row relocated by a concurrent transaction’s page split.

---

4. Can a Dirty Read occur in a microservices architecture during synchronous REST API calls?

Answer: Yes, and it is a widespread architectural pitfall! Consider Service A opening a Spring @Transactional database transaction, setting an order status to PAID, and immediately calling Service B via synchronous HTTP/REST from inside the transactional method:

• Service B queries Service A’s database (or a shared read replica).
• Service B processes and ships the goods.
• Then, upon returning to Service A, an unexpected runtime exception occurs, triggering a database ROLLBACK! Service B acted upon temporary, uncommitted state from Service A. Remediation: Never make synchronous external RPC/HTTP calls inside database transaction boundaries; publish domain events asynchronously only after database transaction commit using the Transactional Outbox Pattern.

---

🎯 Interview Cheat Sheet

• Core Definition: Reading uncommitted modifications from a concurrent transaction that subsequently rolls back.
• Isolation Scope: Permitted only in Read Uncommitted; strictly prevented in Read Committed, Repeatable Read, and Serializable.
• Absent in MVCC: PostgreSQL and Oracle do not have dirty reads; uncommitted tuple headers (xmin) are invisible at the CPU bitmask level.
• Dirty Read vs. Non-Repeatable Read:
  • Dirty Read: Reads uncommitted data that subsequently disappears.
  • Non-Repeatable Read: Reads committed data that is updated between queries.
• Dirty Write ($G_0$): Strictly prohibited in all databases at all isolation levels (exclusive write locks are mandatory).
• Microservices Trap: Never invoke external HTTP endpoints inside an active @Transactional boundary; use the Transactional Outbox pattern.

Related Topics

• What is Read Uncommitted
• What is Read Committed
• What is Non-Repeatable Read
• What Transaction Isolation Levels Exist