Dirty Read Explained: How Uncommitted Data Corrupts Transactions

A balance check that reads an unfinished transfer

It is 14:03 UTC on a payment platform. The transfer service opens a transaction, debits acc-9981 from USD 300 to USD 60, and has not committed yet. A few milliseconds later the balance service asks whether that same account can fund another withdrawal. Under READ UNCOMMITTED, the balance service sees USD 60, declines a valid request, and writes a false “insufficient funds” audit record. The transfer then rolls back because a downstream bank API timed out. The account still holds USD 300. The declined request was correct. The audit log is not.

That is a dirty read: Transaction B observed a write that Transaction A never made permanent. The damage is rarely the row itself. It is the application decision, cache entry, or event that escapes before the writer aborts.

This scenario follows one uncommitted debit through storage-engine visibility rules, isolation levels, multi-service fan-out, and the configuration choices that either stop the anomaly or invite it.

sequenceDiagram
    title Dirty read during an uncommitted debit
    participant T1 as Transfer service (T1)
    participant DB as Database engine
    participant T2 as Balance service (T2)
    T1->>DB: BEGIN; UPDATE accounts SET balance = 60
    Note over DB: New value is in the write buffer / new row version
    T2->>DB: SELECT balance WHERE id = 9981
    DB-->>T2: 60 (dirty, if READ UNCOMMITTED)
    Note over T2: Declines withdrawal and writes audit
    T1->>DB: ROLLBACK
    Note over DB: Balance restored to 300
    Note over T2: Decision already committed outside the row

Stage 1 — Name the uncertainty window

Committed data is durable and visible to later transactions. Uncommitted data is provisional: it may exist in a buffer, undo chain, or new MVCC version, and the writer can still erase it with ROLLBACK. A dirty read is a read of that provisional middle state.

Two engine families expose the window differently:

• Lock-based engines prevent dirty reads when readers take shared locks that conflict with the writer’s exclusive lock. READ UNCOMMITTED skips that shared lock and lets the reader touch the buffered page.
• MVCC engines tag each new row version with the writing transaction id. A reader’s snapshot includes only committed creators, so an in-flight version is invisible and the previous committed version is returned instead.

`` [] --> Uncommitted: Writer mutates the row Uncommitted --> Committed: COMMIT Uncommitted --> RolledBack: ROLLBACK Committed --> []: Permanent for later readers RolledBack --> [*]: Prior committed value restored

The mental model is not “the database stored a wrong number.” It is “another transaction acted on a future that was cancelled.”

## Stage 2 — Watch the race at the buffer

The dirty-read timeline has four beats: T1 writes without committing, T2 reads the provisional value, T1 rolls back, and T2 keeps the decision it already made. From the engine’s point of view T2’s later `COMMIT` can still be valid. The bug lives in application logic that treated an uncommitted observation as durable truth.

```sql
-- T1 (transfer)
BEGIN;
UPDATE accounts SET balance = 60 WHERE id = 9981;
-- still open...

-- T2 (balance check) at READ UNCOMMITTED
BEGIN;
SELECT balance FROM accounts WHERE id = 9981; -- may return 60
-- declines a second withdrawal based on that value
COMMIT;

-- T1
ROLLBACK; -- balance returns to 300; T2's decision remains

On a locking engine at READ UNCOMMITTED, T2 never waits for T1’s exclusive lock. On PostgreSQL, even an attempted READ UNCOMMITTED session is upgraded to READ COMMITTED, so T2 would still see the last committed balance of 300. Engine architecture decides whether the anomaly is possible at all.

Stage 3 — Follow the leak across services

Inside one database connection the harm is already real. Across microservices it becomes a data-contract violation that outruns the rollback.

A warehouse reservation writes stock to zero and stays uncommitted. An inventory service reads that zero under a weak isolation setting and tells the order service the SKU is unavailable. Shipping and notification pipelines act on that answer. When the warehouse rolls back, stock is 1 again, but the customer already received a cancellation and the audit trail contains a phantom stock-out.

`` A[Warehouse service] -->|BEGIN reserve SKU-4421| B[Uncommitted write buffer] B -->|Weak isolation read| C[Inventory service] C -->|Reports zero available| D[Order service] D -->|Queues cancellation| E[Shipping / notify] A -->|ROLLBACK| F[Stock restored to 1] F -.->|Too late| E

Caches make the window longer. A Redis or Memcached entry populated from a dirty read can serve the phantom value long after the source transaction disappeared. Treat “committed read → then cache” as part of the contract for financial and inventory paths.

## Stage 4 — Choose the isolation floor that closes dirty reads

SQL isolation levels are defined by the anomalies they allow. Dirty reads are permitted only at `READ UNCOMMITTED`. `READ COMMITTED` is the first level that forbids them, and it is the default on PostgreSQL, Oracle, and SQL Server.

| Isolation level | Dirty read | Non-repeatable read | Phantom read | Write skew |
| --- | :---: | :---: | :---: | :---: |
| Read Uncommitted | Possible | Possible | Possible | Possible |
| Read Committed | Prevented | Possible | Possible | Possible |
| Repeatable Read | Prevented | Prevented | Possible* | Possible* |
| Serializable | Prevented | Prevented | Prevented | Prevented |

\*InnoDB Repeatable Read also blocks many phantoms with gap locks; the SQL standard still allows phantoms at that level.

Serializable removes more anomaly classes, but under contention it increases aborts and retries. For most OLTP work, Read Committed is the correct floor for dirty-read protection. Raise isolation when the workload also needs stable multi-statement snapshots or serializable conflict detection.

## Stage 5 — Respect what each engine can actually do

The SQL standard allows dirty reads at `READ UNCOMMITTED`. Implementations diverge:

| Database | Default isolation | Can emit dirty reads? |
| --- | --- | :---: |
| PostgreSQL | Read Committed | No — MVCC excludes uncommitted versions; `READ UNCOMMITTED` is upgraded |
| MySQL InnoDB | Repeatable Read | Yes — only at `READ UNCOMMITTED` |
| Oracle | Read Committed | No — that is the minimum level |
| SQL Server | Read Committed | Yes — via `READ UNCOMMITTED` or `NOLOCK` |

PostgreSQL and Oracle are architecturally closed to dirty reads. MySQL and SQL Server are not, and SQL Server’s `WITH (NOLOCK)` is the most common accidental opt-in: developers add it for “faster reads” and silently accept dirty, non-repeatable, and phantom reads together.

```ini
# postgresql.conf — safe default already prevents dirty reads
default_transaction_isolation = 'read committed'

# my.cnf — keep InnoDB at least at READ-COMMITTED for OLTP
[mysqld]
transaction_isolation = READ-COMMITTED

-- SQL Server: prefer RCSI over NOLOCK for non-blocking committed reads
ALTER DATABASE YourDatabase SET READ_COMMITTED_SNAPSHOT ON;

Stage 6 — Use weak reads only where approximation is the product

Dirty reads are sometimes an accepted trade-off when the consumer already labels the number as approximate: live “users online” counters, coarse monitoring scans, or batch reports that must not contend with OLTP writers. In those cases document the isolation choice on the endpoint or query, and never feed the result into payments, inventory commitments, compliance ledgers, or shared caches that other services treat as authoritative.

Defenses, cheapest first:

1. Audit for explicit READ UNCOMMITTED, NOLOCK, and ORM isolation overrides; remove unjustified ones.
2. Pin Read Committed (or higher) at the pool / data-source layer on MySQL and SQL Server.
3. Use Repeatable Read / Serializable snapshots for long reporting queries on MVCC engines instead of weakening isolation.
4. Add optimistic version checks when you cannot raise isolation but must detect lost updates.
5. Prefer append-only ledgers for balances that must reconstruct only from committed events.

End-to-end outcome

After this walkthrough you should be able to:

• Explain why “dirty” means provisional, not merely incorrect.
• Trace a dirty read from an uncommitted buffer through an application decision and optional cache fan-out.
• Name Read Committed as the isolation floor that closes the anomaly.
• Distinguish engines that cannot emit dirty reads from those that can at READ UNCOMMITTED / NOLOCK.
• Choose intentional approximate reads without putting them on financial or inventory paths.

Alternatives considered

Exclusive locking for every read. Waiting on the writer’s exclusive lock prevents dirty reads without MVCC, but read throughput collapses under write contention.

Always Serializable. Removes dirty reads and most other anomalies, at the cost of higher abort/retry rates that many OLTP APIs are not prepared to handle.

RCSI or native MVCC at Read Committed. Preferred alternative to NOLOCK: non-blocking readers that still see only committed versions.

Failure and degraded modes

Phantom refunds and double credits. A payment path refunds against an in-flight debit that later rolls back, leaving both the restored balance and the refund.

Lost sales and poisoned audits. Checkout reads a provisional zero stock, turns away a buyer, and records a stock-out that never existed after rollback.

Cache poisoning. A short dirty observation is stored with a multi-minute TTL and keeps serving the phantom after the database has already healed.

• A dirty read is acting on a provisional write that may still roll back — the danger is the decision, cache, or event that escapes, not only the row value.
• Read Committed is the isolation floor that forbids dirty reads; it is the default on PostgreSQL, Oracle, and SQL Server.
• PostgreSQL and Oracle cannot emit dirty reads by design; MySQL and SQL Server can at READ UNCOMMITTED, and SQL Server often does via NOLOCK.
• Prefer RCSI or native MVCC committed reads over NOLOCK when you need non-blocking readers without provisional data.
• Use READ UNCOMMITTED only for explicitly approximate analytics — never for payments, inventory commitments, compliance logs, or authoritative caches.