Dirty read
A dirty read is reading another transaction's uncommitted data. The danger is not that the data is fresh. It's that it may never become real: if the writer rolls back, you have read, and possibly acted on, a value that never existed.
Session A
Session B
BEGIN
BEGIN
UPDATE accounts SET balance = 999
SELECT balance→ 999 ← uncommitted data
ROLLBACK
acts on a balance that never existed
Formally this is Adya's G1a (aborted read). Its sibling G1b (intermediate read) is subtler: reading a draft the writer later overwrites before committing; both live in the anomaly catalog with the rest of the G1 family.
Who prevents it
| Level | SQL standard | PostgreSQL | MySQL (InnoDB) |
|---|---|---|---|
| READ UNCOMMITTED | permitted | impossible: the level silently behaves as READ COMMITTED | happens (proof) |
| READ COMMITTED and up | prevented | impossible | prevented (proof) |
In PostgreSQL dirty reads are impossible at every level. READ UNCOMMITTED is accepted as syntax and silently upgraded. MySQL takes you at your word: its READ UNCOMMITTED hands out data that was never committed, which is why there is no good reason to run at that level.
Related anomalies
- Non-repeatable read, the committed-data cousin: nothing you read is dirty, yet repeated reads still disagree.
- Intermediate read (G1b), the dirty read's nastier variant: a draft value that no committed history ever contained.
See it happen
- MySQL: READ UNCOMMITTED means it, a real dirty read, in a verified transcript
- PostgreSQL: no dirty reads, even if you ask