Some multi-version relational engines accept a request for the READ UNCOMMITTED isolation level but behave exactly as if READ COMMITTED had been requested. Why does that happen, and is it standard-conformant?
answer
- Level = read-lock policy in a locking engine
- READ UNCOMMITTED = take no read locks
- MVCC readers already never block
- Dirty reads would cost work to implement
- Standard says 'must prevent', stricter is legal
basics
~20 sMulti-versioning already lets readers proceed without blocking on writers, so exposing uncommitted versions buys nothing and would require extra machinery. It is conformant: the standard says which anomalies a level must prevent, and preventing more than required is always allowed.
solid answer
~60 sThe whole point of READ UNCOMMITTED in a lock-based engine is to skip shared read locks so readers never wait behind writers. In a **multi-version** engine that benefit is already free: every read is served from a version committed as of some instant, so readers never block writers and writers never block readers, even at READ COMMITTED. There is no contention left for READ UNCOMMITTED to remove. Worse, delivering literal dirty reads would mean *adding* work — surfacing in-flight, uncommitted versions and dealing with the fact that they may be rolled back mid-read. That is machinery built solely to expose a hazard, so engines skip it. It is **conformant**. The standard defines each level by the anomalies it must *prevent*, not by ones it must exhibit. Running a transaction more strictly than requested never breaks a guarantee, so silently providing READ COMMITTED for a READ UNCOMMITTED request is legal. The practical lesson: the level name is a *maximum permitted weakness*, not a promise about behaviour. Never write code that depends on seeing uncommitted data — the same statement may or may not show it depending on the engine.
go deeper
Know that some engines treat the request as READ COMMITTED and that this is allowed, not a defect.
Explain that isolation levels were originally read-lock policies and that multi-versioning already removes reader blocking, leaving nothing for this level to gain.
Add the conformance argument — levels specify what must be prevented — and the portability trap of code developed on an upgrading engine and deployed on a literal one.
Discuss isolation-level names as an interface inherited from a locking implementation and now a poor fit for multi-version architectures, and the resulting need to reason about actual engine semantics rather than level labels.
## Where READ UNCOMMITTED came from The SQL isolation levels were named in an era when concurrency control meant **two-phase locking**. In that model an isolation level is essentially a policy on read locks: - SERIALIZABLE — shared read locks plus range locks, all held until commit. - REPEATABLE READ — shared read locks on touched rows, held until commit. - READ COMMITTED — shared read lock taken, released immediately after the read. - READ UNCOMMITTED — **no shared read locks at all.** That last row is the entire feature. Not taking a read lock means a reader never has to wait for a writer holding an exclusive lock, and never adds entries to the lock manager. On a busy lock-based engine, a long analytical scan at a stronger level could block writers for its whole duration, or be blocked by them; dropping read locks made such a scan possible at all. The price was that the scan could read anything, including provisional versions that later vanish. ## Why multi-versioning removes the motive A **multi-version** engine does not overwrite rows in place. An update writes a new version and keeps the old one until nothing can still need it. A read is therefore served by selecting the newest version that was committed as of a chosen instant — no lock is required to be sure the data is committed, because commitment is recorded in the version's metadata. The consequences are immediate: - Readers never block writers. - Writers never block readers. - A reader never queues behind an exclusive lock at all. All of that is already true at READ COMMITTED. So the benefit READ UNCOMMITTED exists to provide — avoiding read-lock contention — has already been delivered by the storage architecture, for free, without giving up the no-dirty-reads guarantee. There is nothing left to buy. ## Why it would cost effort to implement literally It is not merely that dirty reads would be useless; they would take extra work. To serve them, the engine would have to locate versions written by transactions still in flight and hand them out despite their status metadata saying "not committed". It would also have to define behaviour when such a version disappears mid-query because its writer rolled back, and decide what a scan mixing committed and in-flight versions even means for aggregates. All of that is engineering invested purely to expose a hazard nobody should depend on. Engines reasonably decline. ## Why it is standard-conformant The standard specifies isolation levels as a table of anomalies each level must **prevent**. It does not require that a level *exhibit* the anomalies it merely permits. "Permitted" is a ceiling on weakness, not a floor. A transaction executed more strictly than requested therefore satisfies every requirement of the requested level: an application asking for READ UNCOMMITTED has declared it can tolerate dirty reads, and giving it none violates no promise. By the same logic an engine may implement all four levels as SERIALIZABLE and remain conformant — inefficient, but legal. Engines behave differently around this in ways worth knowing. Some accept the syntax and silently upgrade. Some accept it, upgrade, and report the transaction's level as the one you asked for. Some reject or warn. A few lock-based engines implement it literally and genuinely produce dirty reads. Behaviour is engine-specific and should be verified, not assumed. ## What this means when you write code 1. **Setting the level is a permission, not a directive.** `SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED` grants the engine leave to show you dirty data; it does not oblige it to. Any logic that depends on seeing another transaction's in-flight work is unportable and probably wrong even where it happens to work. 2. **Do not use it as a performance knob on a multi-version engine.** There is no contention to relieve, so it will not make a slow query faster. If a read is slow, the causes are the usual ones — missing indexes, excessive rows examined, a plan choosing the wrong access path, or version accumulation from long-lived transactions. 3. **Do not conclude from "my queries never see dirty data" that the level is safe.** Move the same code to a lock-based engine, or a different configuration, and the semantics can change under you. 4. **Read the engine's documentation about its actual mapping** before assuming behaviour, and verify with a two-session experiment if it matters. ## The compact answer to hold READ UNCOMMITTED is a lock-avoidance feature in a lock-based world. Multi-versioning solved lock avoidance for readers without weakening correctness, so on those engines the level has no benefit left to offer, and providing the stronger READ COMMITTED behaviour instead is both sensible and conformant — because the standard constrains what a level must prevent, never what it must permit to happen.
- If an engine silently gives you READ COMMITTED, could an application still break?Only if it was written to depend on seeing uncommitted data, which is already a broken design. The risk is the reverse direction: code developed and tested on an upgrading multi-version engine, then deployed against a lock-based engine that honours the level literally, suddenly starts consuming rolled-back rows with no code change.
- Would implementing all four isolation levels as SERIALIZABLE be standard-conformant?Yes. The standard states which anomalies each level must prevent, so preventing more is always permitted. It would be conformant and needlessly expensive, since every transaction would pay for guarantees most do not need and applications would face serialization failures they never asked to handle.
- On a multi-version engine, what should you investigate when a read query is slow, since lowering the isolation level will not help?The usual access-path and workload causes: missing or unusable indexes, a plan scanning far more rows than it returns, stale statistics leading to a poor plan choice, or accumulated dead row versions that a long-running transaction has prevented from being cleaned up. Lock waits are rarely the cause for pure readers under multi-versioning.
It is like asking a library for permission to read manuscripts before they are proofread, and being handed the finished printed copy anyway — the library never promised to give you drafts, only that it would not stop you having them, and the finished copy satisfies you better in every way.
saying these in an interview costs you the question
- Calling the silent upgrade a bug or a standards violation
- Setting READ UNCOMMITTED as a performance tweak on a multi-version engine
- Assuming the level behaves identically across engines
- Believing the standard requires each level to actually exhibit the anomalies it permits
- Writing application logic that relies on observing another transaction's uncommitted rows