Dirty read

A dirty read is when your transaction reads a value another transaction has written but not yet committed. If the writer rolls back, you acted on data that never existed; if it commits, you may still have caught it half-way, in a state no commit ever produced. Every mainstream OLTP database prevents dirty reads by default, so in practice they come from the READ UNCOMMITTED level, SQL Server’s NOLOCK hint, and application code that leaks uncommitted data into a cache or a message.

Picture it first: reading over someone’s shoulder

Your flatmate is typing to the trip group: “I’ll transfer ₹5,000 for the hotel.” You glance at her phone when she has typed “I’ll transfer ₹5” and announce she is chipping in five rupees. Another time you read the whole sentence, book the hotel, and watch her delete the draft unsent.

Both mistakes have one cause: you read a draft, which can still change or vanish. Only a sent message is a promise.

Map it back: the draft is an uncommitted write, sending is COMMIT, deleting is ROLLBACK, and the glance is a dirty read. A database stops the peeking either by making you wait until the message is sent (locking) or by showing you the last message that was sent (MVCC).

What counts as a dirty read

The SQL standard’s wording: T1 modifies a row, T2 reads it before T1 ends, and if T1 rolls back, T2 read something that “never really existed”. Berenson, Bernstein, Gray and colleagues showed in 1995 that this supports two readings. In their shorthand w1[x] is T1 writing item x, r2[x] is T2 reading it, and c1 and a1 are T1’s commit and abort.

• Strict, anomaly A1: w1[x] … r2[x] … (a1 and c2 in either order). It counts only if the writer aborts and the reader commits.
• Broad, phenomenon P1: w1[x] … r2[x] … (c1 or a1). The read itself is forbidden, however the writer ends.

The paper argues for the broad reading, because the strict one misses a reader seeing the middle of a transfer that later commits (the isolation levels hub walks through its example). This page uses the broad meaning.

Atul Adya’s 1999 thesis splits “no dirty reads” into three conditions on committed transactions:

• G1a, aborted reads: you read a value written by a transaction that aborted.
• G1b, intermediate reads: you read a value that was not the writer’s final write to that item.
• G1c, circular information flow: two transactions each saw the other’s uncommitted writes.

Kleppmann’s Hermitage suite tests each by hand: every READ COMMITTED it covers passes all three, while MySQL’s and SQL Server’s READ UNCOMMITTED fail all three. Seeing your own uncommitted writes is not a dirty read; that is the point of a transaction.

The classic case: acting on a rollback

A UPI-style wallet holds ₹1,000. Session A, a tatkal ticket purchase, debits ₹800 and then waits on the payment gateway before committing. Session B, an autopay job someone once moved to READ UNCOMMITTED because it was “faster”, needs ₹500 for an electricity bill.

The balance was ₹1,000 at every commit point, enough for the bill, yet the customer has a failed bill, a fee and an alarming SMS. The dirty window lasts exactly as long as A holds its transaction open across the slow gateway call.

The subtle case: the writer commits, but you saw the middle

Dirty reads don’t need a rollback. Take a ₹3,000 transfer from savings (₹5,000) to the wallet (₹1,000), written as two UPDATEs in one transaction. Session B, a pre-approved loan check, sums both balances and wants at least ₹5,000.

B sees savings debited but the wallet not yet credited: ₹2,000 + ₹1,000 = ₹3,000, a total that existed at no commit point. A commits normally, so the strict A1 definition sees nothing wrong; this is G1b. The one-row version is common too: code that inserts a row with placeholders (total = 0, status DRAFT) and fills them in later in the same transaction lets a dirty reader catch the placeholder.

Why dirty reads are harmful

Decisions built on data that never existed

A declined payment, a “sold out” banner in a flash sale for stock only tentatively sold, a fraud score built on a reversed transaction: the decision commits even though its input rolled back.

Cascading aborts, done by hand

If T2 reads from T1 and T1 aborts, T2 must abort too: a cascading abort, which drags in anything that read from T2 as well. Theory ranks schedules by safety:

• Recoverable: T2 may read uncommitted data but may not commit before T1 does.
• Cascadeless: transactions read only committed data, so one abort never drags in another.
• Strict: nobody reads or overwrites an item until its writer ends, so undo just restores before-images.

Forbidding dirty reads makes schedules cascadeless for free. Engines at READ UNCOMMITTED don’t track who read from whom, so they enforce none of this. Run the autopay timeline on MySQL 8.4: B’s COMMIT succeeds at once, and after A rolls back, B’s “autopay failed” row remains. The cascade still happens, by hand: a support ticket, a refunded fee, an apology SMS.

Checks that see half-done work

A sign-up flow sees another user’s uncommitted insert of @asha and rejects a handle that is free a second later. Oracle’s docs say dirty reads “compromise data integrity, violate foreign keys, and ignore unique constraints”. Declared constraints are still enforced; the exposure is in checks your code makes with reads.

Dirty writes: the anomaly every engine blocks

A dirty write, Berenson’s P0 (w1[x] … w2[x] … (c1 or a1)), overwrites another transaction’s uncommitted value. It breaks invariants: with the rule x = y, T1 setting both to 1 and T2 both to 2, the interleaving w1[x] w2[x] w2[y] c2 w1[y] c1 leaves x = 2 and y = 1. And it breaks rollback: in w1[x] w2[x] a1, restoring T1’s before-image of x erases T2’s write, while not restoring it leaves T2 unable to undo itself later. An undo log (write-ahead logging) only works if nobody writes on top of your uncommitted change.

So every mainstream engine holds an exclusive lock on each row it writes until the transaction ends, at every level; SQL Server’s docs say the level “doesn’t affect the locks acquired to protect data modifications”. In the paper’s table READ UNCOMMITTED is “Degree 1”: no read locks, but long write locks. The demo below shows a second UPDATE waiting on both PostgreSQL and MySQL, even at READ UNCOMMITTED.

Which databases allow dirty reads

Two traps. PostgreSQL never warns you: SHOW transaction_isolation prints read uncommitted while you get READ COMMITTED, so a script that “proves” dirty reads on MySQL proves nothing on PostgreSQL. And on SQL Server, WITH (NOLOCK) is READ UNCOMMITTED for one table; the level is the same as NOLOCK on every table.

How engines prevent dirty reads

The two strategies differ in what a reader does when it meets a row with an uncommitted change.

Locking. The writer holds an exclusive (X) lock until it ends. A READ COMMITTED reader needs a shared (S) lock, which conflicts with X, so it waits. It holds S only briefly (SQL Server releases row locks before the next row), but it queues behind every open writer. This is SQL Server’s default on a boxed install (READ_COMMITTED_SNAPSHOT OFF).

MVCC. The writer adds a new version and leaves the last committed one in place. The reader’s visibility rule hides versions from transactions that hadn’t committed when its snapshot was taken, so it returns the newest committed version without waiting. PostgreSQL keeps old versions in the table, InnoDB and Oracle rebuild them from undo, SQL Server with RCSI keeps a version store; see MVCC.

Locking makes readers wait, so one long batch UPDATE can stall every report touching its rows. MVCC pays in storage and clean-up: VACUUM in PostgreSQL (vacuum and bloat), purge in InnoDB, and in SQL Server up to 14 extra bytes per row, so 100-byte rows can grow by up to 14%.

Why READ UNCOMMITTED buys nothing on an MVCC engine

On an MVCC engine a READ COMMITTED reader never waits for a writer (Oracle: “a writer never blocks a reader”), so RU has no blocking to remove. All it could skip is finding the older committed version: PostgreSQL doesn’t bother, and InnoDB’s saving is small at best, paid for in correctness.

Why it does buy something on a locking engine: the NOLOCK habit

On SQL Server with locking READ COMMITTED, a report scanning a busy Orders table queues behind every open writer. WITH (NOLOCK) makes the waiting vanish, which is how it spread as the cure for blocking. Microsoft’s documentation lists the costs:

• Never-committed values shown to users.
• Rows read twice or skipped when another transaction moves a row’s index key during your scan (locking READ COMMITTED can do this too; row versioning avoids it).
• Untouched rows missed when a concurrent page split hits an allocation-order scan, which can’t happen at READ COMMITTED.
• Error 601, to be retried like a deadlock, and still blocked by DDL, since every query takes a schema-stability lock.

The real fix is row versioning: turn on READ_COMMITTED_SNAPSHOT (RCSI) so READ COMMITTED reads committed versions without shared locks, or use SNAPSHOT for reports needing one consistent view; Azure SQL Database has RCSI on by default. You pay for the version store, and statements that relied on readers blocking can ask for shared locks again with READCOMMITTEDLOCK.

A two-session demo: MySQL vs PostgreSQL

Run these in two terminals, in order. The results are from MySQL 8.4 (InnoDB) and PostgreSQL 18 at default settings.

-- Setup (both databases)
CREATE TABLE wallets (user_id int PRIMARY KEY, balance int NOT NULL);
INSERT INTO wallets VALUES (42, 1000);

-- Session A: the writer (its own level doesn't matter)
START TRANSACTION;                               -- PostgreSQL: BEGIN;
UPDATE wallets SET balance = balance - 800 WHERE user_id = 42;   -- t1
-- ... waiting on the payment gateway ...
ROLLBACK;                                        -- t3

-- Session B: the reader
-- MySQL: with no SESSION keyword, this applies to the next transaction only
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
-- PostgreSQL instead: BEGIN ISOLATION LEVEL READ UNCOMMITTED;
SELECT balance FROM wallets WHERE user_id = 42;  -- t2: A's debit in flight
SELECT balance FROM wallets WHERE user_id = 42;  -- t4: after A's ROLLBACK
COMMIT;

Neither engine allows a dirty write, and PostgreSQL never says it upgraded your request.

Dirty, stale, non-repeatable, skewed: telling them apart

One question separates the bugs that produce “I saw a wrong number”: was the value ever part of a committed state?

Read skew can produce exactly Fig. 2’s wrong total: under READ COMMITTED, reading the wallet (₹1,000) and then savings just after the transfer commits (₹2,000) also gives ₹3,000. Every value was committed; the bug is when it read them, so only a single snapshot fixes it. Replicas, meanwhile, give stale reads, not dirty ones: a PostgreSQL standby shows a transaction’s changes only once its commit record has been replayed.

Dirty reads across caches and services

Isolation levels describe one database. Your service leaks uncommitted data around them, at any level, whenever it acts on a value before COMMIT. It writes PAID into Redis and then rolls back, so readers see PAID until the key expires. It publishes OrderPlaced to Kafka and then the payment is declined, so the delivery service assigns a rider to an order that never existed: a dirty read across the network. Calls, SMSs and webhooks fail the same way.

Publishing after COMMIT fixes the dirty read but loses the event if the process dies in between. Hence the transactional outbox: write the event as an outbox row in the same transaction; a rollback removes both, and after a commit a relay (a poller or change data capture) publishes it at least once, so consumers must be idempotent. For caches, commit first, then delete the key. After-commit hooks such as Django’s transaction.on_commit suit side effects where an occasional miss is tolerable, like invalidation backed by a TTL.

When READ UNCOMMITTED is defensible

Rarely: when the reader decides nothing, writes nothing, and runs on a locking engine where shared locks really hurt.

• Watching a long load. One transaction is inserting 50 million rows into a SQL Server staging table; COUNT(*) with NOLOCK shows its progress. The uncommitted rows are exactly what you want.
• Rough operational counters, like rows waiting in a queue table, where blocking a production writer costs more than an approximate number.

Even then the number can be off in either direction. It is not defensible for money, stock or seats, for anything customers see as fact, for a result that feeds a write, or on PostgreSQL, InnoDB or Oracle, where readers don’t wait for writers anyway. Nor is it the fix for blocking: on SQL Server use RCSI or SNAPSHOT; everywhere, shorten writers (no network calls inside transactions) and move heavy reports to a replica or a warehouse.

Going deeper: edge cases

PostgreSQL’s deliberate exception: sequences

PostgreSQL’s docs warn that sequence changes, and so serial and identity counters, are visible to everyone at once and never rolled back. You see a side effect of uncommitted work: a number taken by a transaction that aborts is gone for good. Gaps in order IDs are normal, not missing orders; for gapless invoice numbers, update a counter row inside the transaction and accept that invoices queue on it.

Dirty reads on purpose

Adya notes that dirty reads can be deliberate, as in mobile systems whose commits stay tentative until the device reconnects, with compensating actions for the cascade. A saga makes the same trade between services: each step is visible before the saga finishes, so the usual guard is a semantic lock, such as a PENDING status readers know not to trust.

Cheat sheet

How to say it in the interview

“A dirty read is reading another transaction’s uncommitted write. Either the writer rolls back, so I acted on a value that never existed, or it’s mid-way, so I see a debit without its credit. Only READ UNCOMMITTED allows it: PostgreSQL runs that as read committed and Oracle doesn’t offer it, while MySQL and SQL Server really return dirty data, usually via NOLOCK. Engines prevent it with write locks held to commit plus short read locks or MVCC snapshots. My design runs at read committed or higher, and I watch the subtler leak, caches and events written before COMMIT, which I fix with commit-then-invalidate and a transactional outbox.”

Can a dirty read happen on a read replica? Not from replication itself: a replica exposes a transaction only once its commit has been replayed (PostgreSQL documents this for hot standby), so lag gives stale reads, not dirty ones. The exception is self-inflicted: a MySQL replica session at READ UNCOMMITTED can see half of a transaction the replica is still applying.

Why doesn’t PostgreSQL support READ UNCOMMITTED? It accepts the syntax but runs READ COMMITTED, which the standard allows because levels are minimums. Under MVCC readers never wait for writers, so dirty reads would add risk and buy nothing.

Is NOLOCK safe for reports? Not for any report that must be right: besides never-committed values, it can read rows twice or skip them, and fail with error 601. Use RCSI or SNAPSHOT, or a replica or warehouse.

Can a dirty read happen with no database bug at all, say through a cache or an event? Yes. A cache entry, event or service call made before COMMIT shows every receiver data that may roll back, at any isolation level. Use an outbox row in the same transaction, and invalidate caches only after commit.

Where to go next

• Transaction isolation levels: every anomaly, and what each database does at each level.
• MVCC: the version chains and visibility rules that hide uncommitted rows.
• Read committed vs repeatable read: the next step up, and why read skew survives READ COMMITTED.
• Transactional outbox: publishing events only after the data commits.
• Cache-aside, read-through and write-through: keeping caches from serving rolled-back values.
• Design a digital wallet: the debit, credit and autopay flows from this page at scale.

References and further reading

1. A Critique of ANSI SQL Isolation Levels (Berenson, Bernstein, Gray, Melton, O’Neil and O’Neil, SIGMOD 1995; MSR-TR-95-51)microsoft.comBroad P1 versus strict A1 dirty reads, P0 dirty writes and why rollback needs long write locks, and the locking table where READ UNCOMMITTED takes no read locks.
2. Weak Consistency: A Generalized Theory and Optimistic Implementations for Distributed Transactions (Adya, MIT PhD thesis, 1999)pmg.csail.mit.eduSplits “no dirty reads” into G1a aborted reads, G1b intermediate reads and G1c circular information flow; cascaded aborts and recoverability.
3. PostgreSQL documentation: Transaction Isolationpostgresql.orgDirty reads are “allowed, but not in PG”: READ UNCOMMITTED behaves like READ COMMITTED; sequence changes are visible at once and never rolled back.
4. PostgreSQL documentation: Hot Standbypostgresql.orgA standby is eventually consistent, and a transaction’s changes become visible only once its commit record has been replayed.
5. MySQL 8.4 Reference Manual: Transaction Isolation Levelsdev.mysql.comInnoDB’s READ UNCOMMITTED: nonlocking SELECTs whose reads are “not consistent”, “also called a dirty read”; otherwise it works like READ COMMITTED.
6. SQL Server documentation: SET TRANSACTION ISOLATION LEVEL (Transact-SQL)learn.microsoft.comREAD UNCOMMITTED equals NOLOCK on every table; write locks are held to commit at every level; RCSI and SNAPSHOT as the alternatives; Synapse defaults to READ UNCOMMITTED.
7. SQL Server documentation: Table Hints (Transact-SQL)learn.microsoft.comNOLOCK and READUNCOMMITTED: never-committed data, records seen twice or not at all, Sch-S locks still taken, error 601 to retry.
8. SQL Server documentation: Transaction locking and row versioning guidelearn.microsoft.comMissing and double reads during index scans, rows missed in allocation-order scans after page splits, and the version store’s 14 bytes per row.
9. Oracle Database Concepts: Data Concurrency and Consistencydocs.oracle.comOracle never permits dirty reads, offers only READ COMMITTED, SERIALIZABLE and read-only, and serves readers from undo so they never block writers.
10. Hermitage: testing transaction isolation levels (Kleppmann)github.comHand-run G1a, G1b and G1c tests: every READ COMMITTED passes, while MySQL’s and SQL Server’s READ UNCOMMITTED fail all three.