What is a dirty read, and what does the READ UNCOMMITTED transaction isolation level permit that stronger levels do not?

## The definition A transaction that writes a row does not make that write durable or official until it commits. Between the write and the commit, the new version exists in the engine but is provisional: a rollback — explicit, or caused by an error, a constraint violation, a deadlock victim selection, or a crash — erases it as if it had never happened. A **dirty read** is a read that returns such a provisional version. The reader cannot tell whether the value it just saw will become real. If the writer rolls back, the reader has consumed a value that no serial execution of the two transactions could ever have produced. ## Where it sits in the standard The SQL standard defines four isolation levels by which anomalies each permits: | Level | Dirty read | Non-repeatable read | Phantom | |---|---|---|---| | READ UNCOMMITTED | allowed | allowed | allowed | | READ COMMITTED | prevented | allowed | allowed | | REPEATABLE READ | prevented | prevented | allowed | | SERIALIZABLE | prevented | prevented | prevented | READ UNCOMMITTED is the floor: it forbids nothing on the read side. Beyond the three named anomalies it also permits read skew, lost updates and write skew, since those are permitted even one level up. ## The one thing it does not relax Every isolation level, including this one, forbids the **dirty write** — replacing a row version that an uncommitted transaction has already written. Allowing that would make rollback incoherent: if T1 wrote a row, T2 overwrote it, and T1 then rolled back, the engine would have to choose between restoring T1's pre-image (destroying T2's committed work) and leaving T2's value (making T1's rollback incomplete). So write locks are taken and honoured at READ UNCOMMITTED exactly as at any other level. This makes the level's real scope precise: **it changes what you may see, never what you may write over.** ## The mechanism, in the family where it means something In a lock-based engine, isolation strength is a function of how long shared read locks are held: - **SERIALIZABLE / REPEATABLE READ** — read locks held to end of transaction (with range locks at the top level). - **READ COMMITTED** — read lock taken, then released immediately after the read. - **READ UNCOMMITTED** — **no shared read locks at all.** Taking no read lock is what lets a reader walk over a row an uncommitted writer is holding an exclusive lock on. The upside is that such a reader never waits behind a writer and never contributes lock-manager pressure; the downside is that it can read anything, in any state. ## Concrete failure shapes - **Rolled-back data used as fact.** A monitoring query totals pending orders including one from a transaction that then fails a constraint check and aborts. The number reported was never true. - **Torn multi-row states.** A transfer writes the debit and, a moment later, the credit. A dirty reader between the two writes sees money that has left one account and not arrived at the other — an intermediate state the transaction was specifically constructed to hide. - **Values that violate declared constraints.** Constraints are enforced at write or at commit; a dirty reader can observe a state that will be rejected. Because none of this raises an error, the damage is silent and downstream. ## Why it is largely a lock-era artefact MVCC engines keep multiple committed versions of each row, so a reader can always be handed a version that was committed as of some instant without waiting for anybody. In such an engine, readers already never block writers at READ COMMITTED — the benefit READ UNCOMMITTED was invented to deliver is already free. There is consequently nothing to gain by exposing uncommitted versions, and several such engines accept the level name and simply behave as READ COMMITTED. That is standard-conformant, because the standard specifies which anomalies a level must *prevent*, not which it must *exhibit*: preventing more than required is always allowed. ## Practical stance Treat READ UNCOMMITTED as something to recognise, not to reach for. The only defensible uses are read-only, approximate, and tolerant of nonsense: a rough row-count estimate on a lock-based engine, an operator's diagnostic peek at a table in the middle of a huge batch, a progress indicator. Nothing whose result is stored, compared, billed, or shown as authoritative should be read at this level. If a query is slow because of lock waits at a stronger level, the fix is almost always better indexing, shorter transactions, or an MVCC-based read path — not lowering the isolation level until the waits disappear.