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.
01 CREATE PROCEDURE UpdateContact
02 @ContactID int,
03 @LastName nvarchar(50)
04 AS
05
06 SELECT LastName AS OriginalName
07 FROM Person.Contact
08 WITH (UPDLOCK)
09 WHERE ContactID = @ContactID;
10 UPDATE Person.Contact
11 SET LastName = @LastName
12
13 WHERE ContactID = @ContactID;
What about D? “The solution must minimize the amount of time required for the stored procedure to execute
and the number of locks held.” Snapshot isolation level uses optimistic locking. A session will be able to update a row without blocking anyone else.
Well, the question is quite strange.
First of all, I don’t see any transaction in the SP. I mean, I don’t see
BEGIN TRAN as the first line and COMMIT TRAN as the last line. So SELECT and UPDATE are not in the same transaction. Bearing this in mind, when I read “You discover that the stored procedure periodically fails to update Person.Contact” I want to ask abot the reason for that failures. Especially, taking into account that READ COMMITTED SNAPSHOT is the default transaction isolation level for Microsoft SQL Azure, I don’t see any reason for failure of that SP.
Second, the answer C which suggest using UPDLOCK and marked as correct answer seems to me useless because update lock will be taken and released immediately after SELECT statement complete. This will happen because, as I said, there is no begin transaction in the SP and question does not say that the SP is called in the context of any transaction.
Third, let’s imagine that transaction is started in the SP, then the SP might fail with deadlock ONLY if REPEATABLE READ or SERIALIZABLE transaction level is used. Here is an example: 2 users run that SP at the same time with the same @ContactID value. They both pass the first statement (SELECT). As a result, they both issue shared locks which will be held until the transaction completes. As soon as they reach the second statement (UPDATE), the first trans will be locked by the second one and the second trans will be locked by the first one. This is classical deadlock. To solve it we should use UPDLOCK in the select statement.
As for the option D, I don’t see how switching from READ_COMMITTED_SNAPSHOT level to SNAPSHOT level might help.