A city library’s circulation database sees frequent reader/writer blocking during evening checkouts. Which isolation-related change should the SQL AI developer recommend?
Select an answer to reveal the explanation.
Short Explanation
You can ease evening checkout blocking by enabling RCSI, which lets readers use a row version while a writer updates the circulation row. Think of it as checking a photocopy while staff updates the shelf card.
Full Explanation
For frequent reader/writer blocking during evening checkouts, enabling Read Committed Snapshot Isolation (RCSI) is the appropriate change: readers can use row versions rather than taking shared locks that conflict with writers updating the same circulation rows. This statement-level read consistency can reduce waits when catalog or due-date updates overlap with reads, without requiring every procedure to be rewritten. Forcing SERIALIZABLE on every checkout expands the locking needed to preserve that stronger isolation and can increase reader/writer waits, worsening the problem. Turning off row versioning tempdb usage removes the versioning benefit and leaves readers relying on shared locks, preserving the blocking the library wants to reduce. Granting UNMASK changes whether patrons can see masked sensitive values; it does not change lock compatibility between circulation readers and writers. Exam caveat: RCSI is database-scoped and changes the behavior of READ COMMITTED reads to use row versions; it does not prevent writers from blocking other writers, and long transactions can keep versions in use. Operational check: measure blocking and wait statistics during evening checkout, test the change in a non-production environment for checkout correctness, then monitor tempdb version-store usage and blocked-session reports after deployment to confirm reader/writer waits decline.