Querying Microsoft SQL Server 2012/2014 — Question 15
You use Microsoft SQL Server to write code for a transaction that contains several statements.
There is high contention between readers and writers on several tables used by your transaction.
You need to minimize the use of the tempdb space. You also need to prevent reading queries from blocking writing queries.
Which isolation level should you use?
Answer options
- A. SERIALIZABLE
- B. SNAPSHOT
- C. READ COMMITTED SNAPSHOT
- D. REPEATABLE READ
Correct answer: C
Explanation
The correct answer is READ COMMITTED SNAPSHOT, as it allows readers to access the last committed version of the data without blocking writers, thus minimizing contention and tempdb usage. SERIALIZABLE and REPEATABLE READ can cause significant blocking issues between readers and writers, while SNAPSHOT may not be as effective in preventing blocking as READ COMMITTED SNAPSHOT.