Developing SQL Databases — Question 13

You have a reporting application that uses a table named Table1. You deploy a new batch update process to perform updates to Table1.
The environment is configured with the following properties:
✑ The database is configured with the default isolation setting.
✑ The application and process use the default transaction handling.
You observe the application cannot access any rows that are in use by the process.
You have the following requirements:
✑ Ensure the application is not blocked by the process.
✑ Ensure the application has a consistent view of the data
✑ Ensure the application does not read dirty data.
You need to resolve the issue and meet the requirements with the least amount of administrative effort.
What should you do?

Answer options

• A. Enable the database for the ALLOW_SNAPSHOT_ISOLATION isolation level. Modify the application for the SERIALIZABLE isolation level.
• B. Enable the database for the READ_COMITTED_SNAPSHOT isolation level.
• C. Enable the application for the WITH (NOLOCK) hint.
• D. Enable the database for the ALLOW_SNAPSHOT_ISOLATION isolation level. Modify the application and the update process for the SNAPSHOT isolation level.

Correct answer: B

Explanation

The correct answer is B because enabling the READ_COMMITTED_SNAPSHOT isolation level allows the application to read committed data without being blocked by the update process, ensuring a consistent view while avoiding dirty reads. Options A and D involve unnecessary complexity with multiple isolation levels, while C would allow dirty reads, which does not meet the requirements.