DRAG DROP
You plan to deploy SQL Server 2012.
You are designing two stored procedures named USP_1 and USP_2 that have the following requirements:
Prevent data read by USP_1 from being modified by other active processes.
Prevent USP_2 from performing dirty reads.
You need to recommend the isolation level for each stored procedure. The solution must maximize
concurrency.
Which isolation levels should you recommend?
To answer, drag the appropriate isolation level to the correct stored procedure in the answer area.
Select and Place:
Answer: 
Explanation:
Note:
* REPEATABLE READ
This isolation level includes the guarantees given by SNAPSHOT isolation level. In addition, REPEATABLE
READ guarantees that for any row that is read by the transaction, at the time the transaction commits the row
has not been changed by any other transaction. Every read operation in the transaction is repeatable up to the
end of the transaction.
* Committed Read is SQL Server’s default isolation level. It ensures that an operation will never read data
another application has changed but not yet committed.
Transaction Isolation Levels
https://docs.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql
READ COMMITTED
Specifies that statements cannot read data that has been modified but not committed by other transactions. This prevents dirty reads. Data can be changed by other transactions between individual statements within the current transaction, resulting in nonrepeatable reads or phantom data. This option is the SQL Server default.
REPEATABLE READ
Specifies that statements cannot read data that has been modified but not yet committed by other transactions and that no other transactions can modify data that has been read by the current transaction until the current transaction completes. 1
Shared locks are placed on all data read by each statement in the transaction and are held until the transaction completes. This prevents other transactions from modifying any rows that have been read by the current transaction. Other transactions can insert new rows that match the search conditions of statements issued by the current transaction. If the current transaction then retries the statement it will retrieve the new rows, which results in phantom reads. Because shared locks are held to the end of a transaction instead of being released at the end of each statement, concurrency is lower than the default READ COMMITTED isolation level. Use this option only when necessary. +