Why can a multi-version relational engine never expose a dirty read to a reader, even when the session explicitly requests the READ UNCOMMITTED isolation level?
answer
- Update-in-place + skip read lock = dirty read possible
- MVCC: new version per write, visibility from commit state
- Uncommitted version invisible except to its author
- READ UNCOMMITTED accepted-as-READ-COMMITTED or rejected
- MVCC pays in retained versions and cleanup, not in read locks
basics
~20 sMulti-version engines serve reads from committed row versions chosen by a visibility rule keyed on committed transaction state. An uncommitted version is simply not visible to anyone but its writer, so there is no mechanism to return one — READ UNCOMMITTED is accepted and behaves as READ COMMITTED.
solid answer
~50 sIn a **multi-version (MVCC)** engine a write does not overwrite a row; it creates a new version stamped with the writing transaction's identifier. Every read applies a **visibility rule**: show the newest version whose creating transaction has committed relative to this reader's snapshot, and whose deleting transaction has not. An uncommitted version fails that test by construction. There is no code path that returns it to another transaction, because visibility is derived from commit state, not from a lock the reader chose to skip. The reader also never blocks — it walks the version chain to an older committed version instead of waiting. So requesting READ UNCOMMITTED buys nothing: engines either accept it and silently behave as READ COMMITTED, or reject it. Dirty reads are an artifact of **lock-based** implementations, where READ UNCOMMITTED means 'take no shared read lock', letting you read a page a writer has modified in place before commit.
code
sql · 4 linesSET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN;
SELECT balance FROM account WHERE id = 1; -- still only committed versions
COMMIT;go deeper
Say that multi-version engines only ever show committed row versions, so there is no way for a reader to see uncommitted data whatever level is requested.
Explain the visibility rule over version chains and contrast it with lock-based update-in-place storage where skipping the shared read lock is what produces a dirty read.
Use it diagnostically: on an MVCC engine a 'dirty read' report is a misdiagnosis, and you would look at statement-level snapshots, replica lag or caching instead — while noting the version-retention cost MVCC pays.
Discuss the storage-model choice as an architectural tradeoff — no read locks and structural dirty-read impossibility, paid for with version retention, cleanup pressure and sensitivity to long transactions — and what it implies for portability.
## Two different storage philosophies Whether dirty reads are even *possible* is a property of how the engine stores concurrent modifications, not of the SQL standard. **Lock-based, update-in-place.** A writer modifies the row (or the page) directly and records the old image in an undo/rollback structure so it can be restored on abort. During that window, the live data page physically contains the uncommitted value. Readers are kept away by taking a **shared read lock**, which conflicts with the writer's exclusive lock and makes the reader wait. READ UNCOMMITTED is defined as *not taking that shared lock*: the reader skips the queue and reads whatever the page currently holds — which may be uncommitted. That is the mechanism that produces a dirty read. **Multi-version (MVCC).** A writer never destroys the old value. Updating a row creates a **new version** of it, tagged with the creating transaction's id, while the previous version remains, tagged with the deleting transaction's id. The table therefore holds several versions of the same logical row, linked in a chain. ## The visibility rule A reader in an MVCC engine does not ask 'what does this page contain?' It asks, for each candidate version: *was the transaction that created this version committed as of my snapshot, and is the transaction that removed it either absent or not committed as of my snapshot?* The engine consults committed-transaction state (a snapshot of which transaction ids had committed at a given instant) to answer. An uncommitted version fails the first half of the test for every transaction except its own author. There is deliberately no branch in the visibility logic that says 'unless the caller asked for READ UNCOMMITTED, in which case return the in-flight version'. Dirty reads are not disabled by a check — they are unrepresentable. Two important corollaries: - **Readers never block writers, and writers never block readers.** Instead of waiting for an exclusive lock to clear, the reader follows the version chain to the newest committed version. This is the headline performance property of MVCC, and it happens to be the same mechanism that eliminates dirty reads. - **A transaction always sees its own uncommitted writes.** The visibility rule special-cases the reader's own transaction id. That is required by every isolation level and is not an anomaly. ## What READ UNCOMMITTED does on such an engine Behaviour varies but the outcome does not: - Some engines accept the level and treat it as READ COMMITTED, documenting the substitution. - Some reject the request outright. - None expose uncommitted versions through normal SQL. So `SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED` on an MVCC engine is at best a no-op and at worst a misleading line in the codebase, because a reader will assume it does what the name says. This is another instance of the general rule that the standard's levels are **ceilings on permitted phenomena**: being stricter than the label is conforming behaviour. ## Why the difference matters practically **Diagnosing across engines.** If someone reports 'we get dirty reads', the first question is which engine and which storage model. On an MVCC engine the report is almost certainly a misdiagnosis — the actual problem is usually staleness from a per-statement snapshot, replica lag, or an application-level cache. On a lock-based engine, look for an explicit uncommitted-read hint or level. **Porting code.** A query that used READ UNCOMMITTED on a lock-based engine to avoid blocking behind writers needs no such hint after moving to MVCC, because it will not block anyway. Conversely, code written against MVCC that assumes readers never wait can suddenly block when ported to a lock-based engine at a strict level. **The cost MVCC pays instead.** Eliminating dirty reads by keeping versions is not free. Old versions must be retained while any snapshot might still need them, and reclaimed afterwards by a background cleanup process. A long-running transaction pins that horizon, causing version accumulation, larger tables and slower scans. So the tradeoff is real: MVCC trades storage and cleanup work for the absence of read locks — and gets dirty-read impossibility as a structural side effect. ## The one-line answer Dirty reads require a mechanism that can hand a reader an uncommitted value. Lock-based, update-in-place storage has such a mechanism (skip the read lock, read the modified page). Multi-version storage does not: visibility is computed from commit state, so uncommitted versions are invisible to everyone but their author, whatever level you ask for.
- If a multi-version engine never blocks readers, what does it give up in exchange?Storage and maintenance. Superseded row versions must be kept as long as any active snapshot could still need them, then reclaimed by background cleanup. A long-running transaction holds that horizon back, so dead versions accumulate, tables and indexes grow, and scans slow down because they must skip invisible versions. Monitoring the oldest active transaction is the standard mitigation.
- A team reports dirty reads on a multi-version engine. What are they most likely actually seeing?Almost certainly not dirty reads. The usual causes are per-statement snapshots at READ COMMITTED, where two statements in one transaction see different committed states, replication lag when reads are routed to a replica, or a stale application-level cache. All produce surprising values, but all of those values were committed at some point.
Lock-based storage is a whiteboard being edited in place — peek without waiting and you see half-erased text. MVCC pins a new sheet for each edit and only files the sheet once it is signed off, so a browser can only ever pick up signed sheets.
saying these in an interview costs you the question
- Claiming READ UNCOMMITTED makes a multi-version engine return uncommitted rows
- Assuming dirty reads are a universal possibility rather than an artifact of lock-based, update-in-place storage
- Saying MVCC readers block until the writer commits
- Believing MVCC removes read locks at no cost, ignoring version retention and cleanup
- Calling a value seen from a stale snapshot or a lagging replica a dirty read