On a lock-based database engine, when is reading at the READ UNCOMMITTED isolation level a defensible choice, and what failure modes beyond seeing rolled-back data does it expose?

## Why the question is engine-family specific The level only *does* anything where isolation is implemented with read locks. In a lock-based engine, a `SELECT` at a stronger level takes shared locks on the rows or pages it reads. Two costs follow: the reader can wait behind an exclusive lock held by a long write transaction, and the reader's own shared locks can block writers for the duration of a large scan. READ UNCOMMITTED removes both by taking no shared read locks at all. In a multi-version engine neither cost exists — reads are already served from committed versions without waiting — so the level buys nothing there and the discussion below simply does not apply. ## The narrow zone where it is defensible Every legitimate use shares three properties. The query is: 1. **Read-only.** Its result never becomes an input to a write, directly or indirectly. 2. **Approximate by design.** The consumer's contract already says "about this many", and a wrong answer produces no wrong decision. 3. **Disposable.** The number is not persisted, reconciled against another number, billed, or shown as authoritative. Examples that pass all three: a rough row-count or size estimate for capacity planning; an operator inspecting a table in the middle of a hours-long batch load to see whether it is progressing; a progress or heartbeat indicator on an ETL job; a quick diagnostic peek at what a stuck job has written so far. Examples that fail: any figure shown to a customer; anything compared against another query's result; anything feeding a decision, a threshold, an alert people act on, or a downstream write. "It's only a dashboard" fails as soon as someone makes a decision from the dashboard, which is what dashboards are for. ## The failure mode people forget Most candidates stop at "you might read data that gets rolled back" and conclude the error is bounded by how much uncommitted work is in flight. It is not. A scan without read locks holds **no stable position** with respect to concurrent structural change. Consider a scan walking an index in key order. A concurrent transaction updates a row so that its indexed key changes: - If the row moves from a location the scan has **not yet reached** to one it has **already passed**, the scan never sees it — a **missed row**. - If the row moves from a location the scan has **already passed** to one it has **not yet reached**, the scan sees it a second time — a **double-counted row**. The same class of problem arises around page splits and row relocations during maintenance operations. Nothing about this is proportional to the amount of uncommitted data; it is proportional to concurrent write and reorganisation activity. A count taken this way can be off in either direction by an amount you cannot bound in advance, and no error is raised to tell you. On top of that: - **Torn multi-row states.** A transaction is precisely the mechanism that hides intermediate states. A dirty reader sees them: a debit without its matching credit, an order header without its lines, a parent row whose children have not been written yet. Aggregates computed across such a moment are internally inconsistent even before any rollback. - **States that violate declared constraints.** Constraints are enforced at write or commit; the reader can observe a state that will be rejected. - **Total silence.** There is no flag on the result set saying "this contains provisional data", no counter to alarm on, and no way for a downstream consumer to tell a dirty result from a clean one. The blast radius is set by who reuses the number, not by who wrote the query. ## The better alternatives, in order Before reaching for the level, exhaust the options that do not give up correctness: 1. **A read replica or a dedicated reporting endpoint.** Moves the scan off the contended instance entirely and keeps normal isolation. This solves the real problem — analytics competing with OLTP — rather than papering over it. 2. **An engine-maintained approximation.** Row-count and size estimates from the engine's own catalog or statistics are usually the number the person actually wanted, cost nothing, and carry no dirty-data risk. 3. **A multi-version read path**, where the engine offers one, giving non-blocking reads of committed data. 4. **Fixing the query.** Reads that must scan huge portions of a table because of a missing index hold locks proportional to what they touch; narrowing the access path shrinks the contention that motivated the whole idea. 5. **Shortening the write transactions** that the reader is waiting behind, which usually helps far more things than this one query. ## If you use it anyway Put a comment on the statement stating that the result is approximate and why. Keep it out of any code path that writes. Do not let the value be stored where a later reader will assume it is exact. And revisit it if the platform ever moves to a multi-version engine, where the setting becomes noise that misleads the next reader about the query's intent. ## The stance to take in an interview Name the narrow zone honestly, then show you know the unbounded-error failure mode rather than only the rollback one, then say what you would do instead. "I know when it is defensible, and I would still reach for a replica or the engine's own estimate first" is the answer that reads as production judgement.