You plan to deploy two stored procedures named SP1 and SP2 that read data from the database.
Your company identifies the following requirements for each stored procedure:
SP1 must allow dirty reads.
SP2 must place range locks on the data to ensure read consistency.
You need to identify which isolation level you must set for each stored procedure. The solution must minimize
the number of locks.
Which isolation level should you identify?
To answer, drag the appropriate isolation level to the correct stored procedure in the answer area. (Answer
choices may be used once, more than once, or not at all.)
Select and Place:

Answer:

Explanation:
Note:
* READ UNCOMMITTED
Specifies that statements can read rows that have been modified by other transactions but not yet committed.
Transactions running at the READ UNCOMMITTED level do not issue shared locks to prevent other
transactions from modifying data read by the current transaction. READ UNCOMMITTED transactions are also
not blocked by exclusive locks that would prevent the current transaction from reading rows that have been
modified but not committed by other transactions. When this option is set, it is possible to read uncommitted
modifications, which are called dirty reads. Values in the data can be changed and rows can appear or
disappear in the data set before the end of the transaction. This option has the same effect as setting NOLOCK
on all tables in all SELECT statements in a transaction. This is the least restrictive of the isolation levels.
* SERIALIZABLE
Specifies the following:
Statements cannot read data that has been modified but not yet committed by other transactions. No other
transactions can modify data that has been read by the current transaction until the current transactioncompletes.
Other transactions cannot insert new rows with key values that would fall in the range of keys read by any
statements in the current transaction until the current transaction completes. Range locks are placed in the
range of key values that match the search conditions of each statement executed in a transaction. This blocks
other transactions from updating or inserting any rows that would qualify for any of the statements executed by
the current transaction. This means that if any of the statements in a transaction are executed a second time,
they will read the same set of rows. The range locks are held until the transaction completes. This is the most
restrictive of the isolation levels because it locks entire ranges of keys and holds the locks until the transaction
completes. Because concurrency is lower, use this option only when necessary.
SET TRANSACTION ISOLATION LEVEL (Transact-SQL)