70-465 Question #94: Real Exam Question with Answer & Explanation

Question

You work as a Database Administrator (DBA) for a company named ABC.com. The company uses a Microsoft SQL Server 2012 infrastructure. You have a database named CorpDB. You plan to create a stored procedure to access data in CorpDB. You need to ensure that the stored procedure supports dirty reads. What should you do?

Options

• AYou should configure the stored procedure to use the READ UNCOMMITTED isolation level.
• BYou should configure the stored procedure to use the READ COMMITTED isolation level.
• CYou should configure the stored procedure to use the READ SERIALIZABLE isolation level.
• DYou should configure the stored procedure to use the REPEATABLE READ isolation level.
• EYou should configure the stored procedure to use the SNAPSHOT isolation level.

Explanation

To support dirty reads for a stored procedure in SQL Server 2012, the transaction isolation level must be configured to allow reading uncommitted data. This specific behavior is enabled by the READ UNCOMMITTED isolation level.

Common mistakes.

• B. The READ COMMITTED isolation level prevents dirty reads by ensuring that a transaction can only read data that has been committed by other transactions.
• C. The SERIALIZABLE isolation level provides the highest level of transaction isolation, preventing dirty reads, non-repeatable reads, and phantom reads.
• D. The REPEATABLE READ isolation level prevents dirty reads and non-repeatable reads by ensuring that any data a transaction reads remains locked until the transaction completes.
• E. The SNAPSHOT isolation level provides a transactionally consistent view of the database as it existed at the start of the transaction, thereby preventing dirty reads and other concurrency issues.

Concept tested. SQL Server Transaction Isolation Levels and Dirty Reads

Reference. https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-isolation-levels?view=sql-server-ver16

No community discussion yet for this question.