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
NOLOCKhint 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.
Connections
- Part of
- Isolation Level
- Related
- Read Committed
Recall
These cards are also in your review deck, scheduled with spaced repetition.