Difficulty: Easy
Correct Answer: Correct
Explanation:
Introduction / Context:
Transaction isolation level governs how concurrent transactions interact. In SQL Server, the default isolation level for new sessions is READ COMMITTED unless explicitly changed at the session/database level. Understanding this baseline is crucial for diagnosing blocking, deadlocks, and consistency anomalies.
Given Data / Assumptions:
Concept / Approach:
Under READ COMMITTED, statements cannot read data modified by other transactions that have not yet committed. With READ_COMMITTED_SNAPSHOT ON, SQL Server implements this using row versioning (nonblocking reads); otherwise it uses shared locks on read. The default level name remains READ COMMITTED in both cases.
Step-by-Step Solution:
Verification / Alternative check:
Use DBCC USEROPTIONS to display the current session’s isolation level. Review database properties for snapshot-based implementation.
Why Other Options Are Wrong:
Common Pitfalls:
Assuming SNAPSHOT is the default; forgetting to measure blocking differences when switching RCSI on.
Final Answer:
Correct
Discussion & Comments