Developing Microsoft SQL Server Databases — Question 25

You need to modify usp_GetOrdersAndItems to ensure that an order is NOT retrieved by usp_GetOrdersAndItems while the order is being updated.
What should you add to usp_getOrdersAndItems?

Answer options

• A. Add SET TRANSACTION ISOLATION LEVEL SERIALIZABLE to line 44
• B. Add WITH (UPDLOCK) to the end of line 42
• C. Add SET TRANSACTION ISOLATION LEVEL SNAPSHOT to line 44
• D. Add WITH (READPAST) to the end of line 42

Correct answer: A

Explanation

The correct answer is A because setting the transaction isolation level to SERIALIZABLE ensures that no other transactions can access the order while it is being updated, thus preventing any retrieval during that time. The other options do not provide the same level of locking or isolation required to achieve this effect.