You have a Microsoft SQL Azure database.
You have the following stored procedure:
You discover that the stored procedure periodically fails to update Person.Contact.
You need to ensure that Person.Contact is always updated when UpdateContact executes. The solution must
minimize the amount of time required for the stored procedure to execute and the number of locks held.
What should you do?
A.
Add the following line of code to line 12:WITH (UPDLOCK)
B.
Add the following line of code to line 05:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
C.
Add the following line of code to line 08:
WITH (UPDLOCK)
D.
Add the following line of code to line 05:
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
Explanation:
* Overall, you should use UPDLOCK when you read a value that you plan to update later in the same
transaction to prevent the value from changing.
* UPDLOCK
Specifies that update locks are to be taken and held until the transaction completes. UPDLOCK takes update
locks for read operations only at the row-level or page-level. If UPDLOCK is combined with TABLOCK, or a
table-level lock is taken for some other reason, an exclusive (X) lock will be taken instead.
When UPDLOCK is specified, the READCOMMITTED and READCOMMITTEDLOCK isolation level hints are
ignored. For example, if the isolation level of the session is set to SERIALIZABLE and a query specifies
(UPDLOCK, READCOMMITTED), the READCOMMITTED hint is ignored and the transaction is run using the
SERIALIZABLE isolation level.