An order-entry database also serves heavy reporting queries. Under the current isolation level, long reports take shared locks that block order updates. Reports must still see only committed data. Which isolation approach best resolves the conflict?
Select an answer to reveal the explanation.
Short Explanation
Row versioning is like handing the report a photocopy of the ledger while the clerks keep writing in the original. Readers get a consistent, committed picture and writers never wait on them. Don't take the READ UNCOMMITTED bait: it stops the blocking but hands you dirty data, which the requirement rules out.
Full Explanation
Snapshot isolation (and similar multiversion concurrency control approaches) keeps prior committed versions of modified rows. A reader sees the data as it was committed at the start of its transaction or statement, retrieved from the version store, so it does not need shared locks on the current rows. Writers therefore are not blocked by long-running reports, and reports are not blocked by writers, while every value the report sees was committed. READ UNCOMMITTED also avoids taking shared locks, but it achieves that by reading in-flight changes, producing dirty reads that violate the stated requirement. REPEATABLE READ holds shared locks for the duration of the transaction, which lengthens exactly the blocking the team wants to remove. SERIALIZABLE adds range protection on top of that and further reduces concurrency. Exam caveat: versioning is not free; it consumes storage for row versions and can raise update-conflict errors when two transactions modify the same row under snapshot rules, so writers may need retry logic. Operational check: after enabling row versioning in a test environment, monitor version-store growth, blocking chains and report run times under a realistic mixed workload before changing production.