Notestransactions🌱 seedling

Dirty Read

Also: Uncommitted Read · Dirty Write · Aborted Read · G1a

A dirty read is seeing another transaction's uncommitted write, which may still be rolled back. Its sibling, the dirty write, is overwriting another transaction's uncommitted write.

Why it exists

Without it, a transaction can act on data that never officially existed: a balance from a transfer that aborted, or half of a multi-row update that is still in progress.

How it works

  • Dirty read: T1 writes x, T2 reads x before T1 commits, then T1 aborts. T2 has seen a value from nowhere. Even if T1 commits, T2 may have seen only some of its writes.
  • Dirty write: T1 and T2 both update rows x and y and interleave; without locks, x ends with T1's value and y with T2's, a state neither intended.
  • Prevention: writers hold row locks until commit (stops dirty writes at every level); readers are served the last committed version rather than blocking (stops dirty reads at Read Committed and above).

Trade-offs

Allowing dirty reads Preventing them
no need to keep a committed copy beside the in-progress one a second version per row being written
readers never wait (with locking reads) readers wait for writers

In practice

  • Read Uncommitted is the only level that allows dirty reads; Postgres doesn't implement it and silently gives Read Committed.
  • SQL Server's NOLOCK hint reads uncommitted data and is a common source of "impossible" report numbers.

Pitfalls

  • Assuming dirty reads are harmless for "approximate" reports: rolled-back and half-finished data can make totals wrong, not just stale.

Recall

These cards are also in your review deck, scheduled with spaced repetition.