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?

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.

Explanation:

Snapshot isolation must be enabled by setting the ALLOW_SNAPSHOT_ISOLATION ON database option before it is used in transactions. This activates the mechanism for storing row versions in the temporary database (tempdb).

READ COMMITTED is the default isolation level for SQL Server. It prevents dirty reads by specifying that statements cannot read data values that have been modified but not yet committed by other transactions. Other transactions can still modify, insert, or delete data between executions of individual statements within the current transaction, resulting in non-repeatable reads, or -phantom- data.

Incorrect Answers:

A: SERIALIZABLE is the most restrictive isolation level, because it locks entire ranges of keys and holds the locks until the transaction is complete. It encompasses REPEATABLE READ and adds the restriction that other transactions cannot insert new rows into ranges that have been read by the transaction until the transaction is complete.

References: https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/snapshot-isolation-in-sql-server