Exam 70-764 topic 1 question 284 discussion

DRAG DROP -
You have an SQL Server 2014 server. You plan to create four stored procedures that will use transactions. The stored procedures will be configured as shown in the following table.

You need to recommend an isolation level for each stored procedure. The solution must support the concurrency strategy of each stored procedure and must minimize locks.
What should you recommend?
To answer, drag the appropriate isolation levels to the correct stored procedures. Each isolation level may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
Select and Place:

Suggested Answer:
✑ SP1 SNAPSHOT The transaction can only recognize data modifications that were committed before the start of the transaction. Data modifications made by other transactions after the start of the current transaction are not visible to statements executing in the current transaction. The effect is as if the statements in a transaction get a snapshot of the committed data as it existed at the start of the transaction.
✑ SP2: 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.
✑ SP3: READ COMMITTED Specifies that statements cannot read data that has been modified but not committed by other transactions. This prevents dirty reads.
✑ SP4: SERIALIZABLE specification include: / No other transactions can modify data that has been read by the current transaction until the current transaction completes.
References: SET TRANSACTION ISOLATION LEVEL (Transact-SQL)
https://msdn.microsoft.com/en-us/library/ms173763.aspx