You need to ensure that Person.Contact is always updated when UpdateContact executes

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?

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.



Leave a Reply 3

Your email address will not be published. Required fields are marked *


Lee

Lee

Looks like D – The solution must minimize the amount of time required for the stored procedure to execute and the number of locks held.

Skippo

Skippo

The correct answer is option C.

A shared row (read) lock is acquired for the SELECT statement. Since there was going to be a subsequent update (write) operation, the UPDLOCK query hint prompts the DB engine to assign an higher lock, to prevent deadlocks.