What is Read Uncommitted

At this level, a transaction can read changes written by other concurrent transactions before those changes have been formally committed with a COMMIT statement.

🟢 Junior Level

Read Uncommitted is the lowest and least restrictive transaction isolation level defined by the ANSI/ISO SQL standard.

At this level, a transaction can read changes written by other concurrent transactions before those changes have been formally committed with a COMMIT statement.

The Fundamental Danger: Dirty Read

If Transaction $T_2$ reads uncommitted modifications made by Transaction $T_1$, and Transaction $T_1$ subsequently cancels its work with ROLLBACK, the data read by $T_2$ becomes fictitious (“dirty” data) because it never officially existed in the database.

Timeline  Transaction A (Debit)             Transaction B (Read Uncommitted)
   |      ---------------------             --------------------------------
   |  T1: BEGIN;
   |  T2: UPDATE accounts
   |      SET balance = 500 WHERE id = 1;
   |  T3:                                   SELECT balance FROM accounts WHERE id = 1;
   |                                        -- Result: 500 (DIRTY READ!)
   |  T4: ROLLBACK; -- Reverts mutation!
   |  T5:                                   Transaction B assumes balance is 500,
   v                                        even though balance remains 100!

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

@Service
public class AnalyticsService {

    // Spring declarative configuration for Read Uncommitted:
    @Transactional(isolation = Isolation.READ_UNCOMMITTED, readOnly = true)
    public long getApproximateActiveUsersCount() {
        // Acceptable strictly for background telemetry and monitoring,
        // where a small percentage inaccuracy is acceptable.
        return userRepository.countActiveUsers();
    }
}

Analogy: Imagine a colleague drafting a rough proposal on a whiteboard. While they step out for lunch, you glance at an unverified preliminary budget figure on the board and present it to an executive. When your colleague returns, they erase the whiteboard number as a calculation error. You have made business decisions based on a discarded draft.

🟡 Middle Level

Database Engine Behaviors

Database Engine	Behavior Under `READ UNCOMMITTED`
PostgreSQL & Oracle	Silent Elevation: Does not physically exist; automatically elevated to `Read Committed`.
MySQL (InnoDB)	Supported. Reads in-flight pages directly from the `Buffer Pool` without unwinding Undo Log chains.
Microsoft SQL Server	Supported. Bypasses shared read locks; activated globally or via table hint `WITH (NOLOCK)`.

PostgreSQL & Oracle Architecture: In PostgreSQL, Multi-Version Concurrency Control (MVCC) checks tuple headers (xmin/xmax) against the in-memory transaction status map (pg_xact). Uncommitted tuples are invisible by default with zero locking overhead. Implementing dirty reads would require deliberate, counter-productive engine complexity; hence, PostgreSQL accepts the SQL command but silently executes it as Read Committed.
Microsoft SQL Server WITH (NOLOCK): Read Uncommitted is functionally identical to the WITH (NOLOCK) query hint:
```
SELECT * FROM Orders WITH (NOLOCK);
```
The engine reads data pages without acquiring Shared Locks (S-locks), completely ignoring Exclusive Locks (X-locks) held by concurrent write transactions.

Permitted Anomalies Matrix

Anomaly	Permitted on Read Uncommitted?
Dirty Read	⚠️ Yes
Non-Repeatable Read	⚠️ Yes
Phantom Read	⚠️ Yes
Lost Update	⚠️ Yes
Dirty Write ($G_0$)	❌ NO (Strictly Forbidden)

🔴 Senior Level

Why Dirty Write ($G_0$) Is Strictly Forbidden Across All Engines

While ANSI SQL permits all read anomalies under Read Uncommitted, it strictly prohibits Dirty Writes ($G_0$):

\[\text{Dirty Write: } T_1 \text{ modifies } X \implies T_2 \text{ modifies } X \implies T_1 \text{ or } T_2 \text{ aborts}\]

If an engine permitted $T_2$ to overwrite an uncommitted row modified by $T_1$, then should $T_1$ issue a ROLLBACK, the engine’s Undo Log would not know which prior image to restore without corrupting $T_2$’s work. Consequently, all relational database engines (including MySQL and SQL Server) unconditionally acquire Exclusive Locks (X-Locks) on all mutating operations (UPDATE, DELETE, INSERT) until transaction termination. Concurrent, unsynchronized writes to the same row are physically impossible even under Read Uncommitted.

Storage Engine Physical Anomalies: SQL Server `NOLOCK` and Page Splits

In page-based locking storage engines like Microsoft SQL Server, querying under Read Uncommitted / NOLOCK exposes queries to physical allocation order storage anomalies:

Skipped Rows: While a reader scans a B-Tree leaf page sequentially along physical disk allocation order, a concurrent writer inserts a record, triggering an index B-Tree Page Split. Half the records are relocated to a newly allocated page positioned behind the scanner’s current page offset. The query completely skips existing, committed rows.
Duplicate Rows: If a page split pushes rows forward onto a page that the scanner has not yet reached, the query visits those rows a second time, returning duplicate records in the result set.
Error 601 (Index Scan Abort): If an index rebuild or table truncation occurs concurrently, the un-locked scan encounters deallocated memory pages, crashing the query with a transient memory access error.

4 Tricky Questions

1. Why does executing `SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;` in PostgreSQL not allow reading dirty data?

Answer: PostgreSQL’s isolation architecture is built entirely on MVCC (Multi-Version Concurrency Control). Every row tuple in PostgreSQL stores header attributes xmin (ID of the creating transaction) and xmax (ID of the deleting transaction). When any query executes, PostgreSQL evaluates the visibility of each tuple by consulting the in-memory transaction status log (pg_xact). If the xmin transaction has a status of IN_PROGRESS or ABORTED, the tuple is recognized as invisible at the engine level. To permit dirty reads, PostgreSQL engineers would have had to add special code to bypass visibility verification for an anti-pattern. PostgreSQL accepts the syntax for ANSI compliance, but executes queries as Read Committed.

2. Does `READ UNCOMMITTED` yield noticeable performance gains in MySQL InnoDB compared to `READ COMMITTED`?

Answer: Practically no. In MySQL InnoDB, Read Committed does not acquire shared locks; it performs non-blocking reads against memory pages in the Buffer Pool. The only distinction is that if a row is actively being modified by another transaction, Read Committed navigates the Undo Log chain (creating a Read View) to locate the latest committed image, whereas Read Uncommitted reads the raw bytes on the dirty page without traversing undo segments. On modern hardware, this difference amounts to sub-microsecond CPU cycles, yielding less than a 1–2% throughput difference while introducing immense risks of data inconsistency.

3. Does JPA / Hibernate allow `Read Uncommitted`, and what is the risk to the L1 Persistence Context?

Answer: Yes, @Transactional(isolation = Isolation.READ_UNCOMMITTED) is translated by the Spring transaction manager to JDBC Connection.TRANSACTION_READ_UNCOMMITTED. However, doing so creates severe corruption risks for the Hibernate First-Level Cache (Persistence Context). If Hibernate loads an entity based on uncommitted, dirty database state, that dirty object is cached in the active EntityManager. If the modifying transaction rolls back in the database, the EntityManager still holds the invalid entity. Subsequent updates or automated dirty-checking flushes during that session will write the corrupted data back into the database.

4. What are the rare legitimate production use cases for `READ UNCOMMITTED` / `NOLOCK`?

Answer: There are only three legitimate scenarios:

Approximate Telemetry & High-Level Dashboards: Aggregating broad metrics (e.g., “approximate count of active WebSocket sessions” or “rough queue depth estimation”) where a $\pm 5\%$ margin of error is acceptable and avoiding shared read locks on legacy locking databases (like SQL Server) is desirable.
Emergency DBA Incident Diagnostics: When production database tables are heavily locked and queued, an administrator can execute SELECT ... WITH (NOLOCK) to inspect session states and running processes without getting stuck behind locked tables.
Immutable Append-Only Audit Logs: Querying audit tables where records are only inserted and never updated or deleted (INSERT-only).

🎯 Interview Cheat Sheet

Core Definition: Weakest isolation level; permits reading uncommitted, in-flight changes (Dirty Read).
Engine Support:
- PostgreSQL & Oracle: Does not exist; automatically elevated to Read Committed.
- MySQL InnoDB: Supported via buffer pool direct reads.
- SQL Server: Supported via WITH (NOLOCK).
Anomalies Allowed: Dirty Read, Non-Repeatable Read, Phantom Read, Lost Update.
Dirty Write ($G_0$): Strictly forbidden across all databases; exclusive locks (X-locks) on writes are always enforced.
SQL Server Hazard: NOLOCK causes physical page split anomalies, returning skipped or duplicate rows.
Hibernate Pitfall: Contaminates the First-Level Cache (EntityManager), leading to permanent database corruption upon session flush.
Production Stance: Never use in transactional business domains or financial processing.