What is a dirty read in a relational database, and what can go wrong for the transaction that performed one if the writing transaction later rolls back?
answer
- Read of an uncommitted row version
- Writer rolls back → value never existed
- Reader's derived writes stay committed — one-way contamination
- Can also see half of a multi-row change
- Only at READ UNCOMMITTED
basics
~20 sA dirty read is reading a row version written by a transaction that has not committed. If that writer rolls back, the value you read never existed in any committed state, so any decision, calculation or write you based on it is derived from data the database itself disowned.
solid answer
~50 sA **dirty read** happens when transaction A reads data that transaction B has written but not yet committed. Two bad outcomes follow. If B **rolls back**, the value A saw never existed in any committed state of the database — A acted on a fact the database subsequently denied. If A then *wrote* something derived from it (an invoice total, an approval flag, a message published downstream), that garbage is now committed and durable, and no rollback of B can undo it. Even if B commits, A's read was still unsound in general: B may have been mid-way through a multi-row update, so A could see half of a transfer — the debit without the matching credit — and compute a total that violates an invariant nobody ever intended to exist. Dirty reads are permitted only at READ UNCOMMITTED; every stronger level forbids them, and multi-version engines never produce them because readers only ever see committed row versions.
code
text · 7 linesT1: BEGIN
T1: UPDATE account SET balance = 0 WHERE id = 1; -- uncommitted
T2: BEGIN (READ UNCOMMITTED)
T2: SELECT balance FROM account WHERE id = 1; -- reads 0 <-- dirty
T2: INSERT INTO alerts(kind) VALUES ('ZERO_BALANCE');
T2: COMMIT -- alert is now durable
T1: ROLLBACK -- balance is 100 and always wasgo deeper
Define it as reading uncommitted data, and give the rollback consequence: the value never existed in any committed state.
Add the partial-state hazard even when the writer commits, and name READ UNCOMMITTED as the only level that permits it.
Emphasize that the reader's derived writes stay committed after the writer aborts, making the corruption one-directional and unrepairable, and treat any READ UNCOMMITTED usage as a review finding.
Frame it as an architectural boundary: uncommitted data must never cross into durable state or into downstream systems, and set a policy that explicit dirty-read hints require justification tied to a non-authoritative consumer.
## The definition A transaction is a unit of work that is not visible to others until it commits. A **dirty read** is the violation of that rule: transaction A reads a row version produced by transaction B while B is still in flight. The data is 'dirty' because it is uncommitted — provisional, and legally revocable by B at any moment. Concretely: 1. B starts and updates `account.balance` from 100 to 0 as part of a withdrawal. 2. A reads `account.balance` and sees 0. 3. B hits an error and rolls back. The row is 100 again, and always was, as far as committed history is concerned. 4. A is now holding the number 0, which corresponds to no committed state the database ever had. ## Why it is harmful **Rollback makes the value fictional.** The most cited hazard: A acted on data that was never real. If A only displayed it, you have shown a user something wrong. If A branched on it — declined a purchase, skipped a top-up, triggered an alert — you have taken an irreversible action on fiction. **Contamination is permanent and one-directional.** A rollback of B undoes B's changes. It cannot undo anything A wrote. So if A read 0 and inserted an 'insufficient funds' record, or published an event, or wrote a derived total, that data is committed and durable while its input has evaporated. This is the reason dirty reads are considered a correctness bug rather than a staleness annoyance: the corruption outlives its cause. **Partial states violate invariants.** Even when B eventually commits, at the moment A reads, B may have applied only some of its writes. A transfer that debits one account and credits another is atomic to everyone else — but a dirty reader can observe the interval between the two writes and see money that has left one account and not yet arrived at the other. Any aggregate A computes in that window is wrong even though every individual value it read will eventually be committed. **It breaks recoverability in theory.** A schedule where one transaction reads another's uncommitted data is not *recoverable* unless the reader is forced to wait to commit until the writer commits. If the reader commits first and the writer then aborts, the database has committed work derived from an aborted transaction — a state no correct recovery procedure can repair. Systems that allow dirty reads therefore do not attempt this repair at all; they push the risk onto the application. ## When can it happen Only at the **READ UNCOMMITTED** isolation level. The SQL standard's table permits dirty reads there and forbids them at READ COMMITTED, REPEATABLE READ and SERIALIZABLE. So in the overwhelming majority of systems — where READ COMMITTED is the default — dirty reads simply do not occur. It is also worth knowing that the level is a *ceiling*: an engine may forbid dirty reads even when you ask for READ UNCOMMITTED, and multi-version engines do exactly that, because their readers are structurally incapable of seeing an uncommitted version. ## What a dirty read is not - It is **not** stale data. Reading a value that was committed but has since changed is normal and, at most, a non-repeatable read. - It is **not** a lost update. Losing a concurrent write is a different anomaly with a different cause. - It is **not** the same as reading your own uncommitted writes. Every isolation level lets a transaction see its own changes; that is required, not an anomaly. - It is **not** caused by missing indexes, replication lag, or caching — those produce staleness, not uncommitted visibility. ## How you avoid it Do nothing unusual: run at READ COMMITTED or above, which is the default nearly everywhere. The only way to encounter a dirty read is to explicitly request READ UNCOMMITTED on a lock-based engine, or to use an engine-specific hint that has the same effect. If you find such a hint in a codebase, treat it as a finding: ask what the query feeds, and whether anything downstream writes or decides based on the result. A dirty read is defensible for a rough, human-eyeballed progress count; it is indefensible anywhere the number is stored, compared against a threshold, or shown as authoritative.
- If the writing transaction ends up committing anyway, was the dirty read harmless?Not necessarily. At the instant of the read the writer may have applied only part of its changes, so the reader can observe a state that violates an invariant the writer restores before commit — for example a debit without its matching credit. The reader's computation is wrong even though every value it saw was eventually committed.
- Is reading a value that was committed a moment ago but has since been changed a dirty read?No. That is ordinary staleness, and if it happens twice within one transaction it is a non-repeatable read. A dirty read specifically requires that the version you observed had not been committed at the time you observed it, which is what makes rollback able to erase it.
- Which isolation levels forbid dirty reads?READ COMMITTED, REPEATABLE READ and SERIALIZABLE all forbid them; only READ UNCOMMITTED permits them. Because the standard's levels are ceilings on permitted phenomena, an engine may also refuse to produce dirty reads at READ UNCOMMITTED, which is what multi-version implementations do.
It is like quoting a colleague's unsaved draft in your published article: they can hit undo, but your article is already printed with their retracted sentence in it.
saying these in an interview costs you the question
- Calling any stale or out-of-date read a dirty read
- Believing that rolling back the writer also cleans up whatever the reader wrote
- Thinking a dirty read is harmless as long as the writer eventually commits
- Confusing it with reading your own uncommitted changes, which every level allows
- Assuming dirty reads can occur at READ COMMITTED under heavy load