http://www.sqlservercentral.com/blogs/john-huangs-blog/2012/10/10/update-with-updlock/

Printed 2014/07/25 03:34AM

Update with UPDLOCK

By john_wong_ca, 2012/10/10

Do you think it’s a good idea to add UPDLOCK hint to the table which will be modified in an update query? Many people will say NO because it seems to be redundant. But the fact is that sometime it removes deadlocks in your application.


First, let’s prepare our test environment:

use test
go
if object_id('MySignal') is not null
	drop table MySignal
go
create table MySignal(ID bigint, Description char(2000) not null default(''), primary key nonclustered(ID)) 
go
insert into MySignal(ID)
	select partition_id from sys.partitions
go

Now, let’s observe following queries

begin transaction
update MySignal set Description  = 'Value 1' where ID = 72057594038910976 
print 'Do some work for that ID... which takes time'
update MySignal set Description = 'Value 2' where ID = 72057594038910976
rollback

At the beginning of the code, a transaction is started. Row with ID = 72057594038910976 is locked with X lock by the update statement. This lock on the row will be released when the transaction is complete (commit or rollback). Other processes will not be able to get the same lock on the same record in the duration of taht X lock. The intention of such structure is obvious – developers are trying to prevent mutiple sessions processing the data for the same ID at the same time. This’s logically correct. But deadlock can be produced by following steps.
Now let’s open session1 and run following code and leave the transaction open

--Session 1
begin transaction
update MySignal set Description  = 'Value 1' where ID = 72057594038910976

Then open session2 and run the same query as session1.

--Session 2
begin transaction
update MySignal set Description  = 'Value 1' where ID = 72057594038910976

Session2 is blocked by session1 – this is exactly what developers want to see. Now let’s run following query in session1:

--Session 1
update MySignal set Description = 'Value 2' where ID = 72057594038910976

Then you will see the query in session1 is run sucessfully. This is expected. Go back to session2, you will see the deadlock error

Msg 1205, Level 13, State 45, Line 2
Transaction (Process ID 67) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

If you change the code with UPDLOCK added, the deadlock will become unproducable

begin transaction
update MySignal with(updlock)  set Description  = 'Value 1' where ID = 72057594038910976 
print 'Do some work... which take time'
update MySignal set Description = 'Value 2' where ID = 72057594038910976
rollback

Everyone knows that while a record is being updated, SQL Server uses U lock to find the record, conver U lock to X lock, modify the record, and then release the lock(See my blog post here). This is the rule. In this example, while ID 72057594038910976 is modified, SQL server executes the same rule – go though the none clustered primary key to get the RID using U lock then X lock the row,… just as usual.
But why deadlocks? Here is the explaination

Session 1 owns X lock on the RID and trying to get U lock on the index key where Session 2 owns U lock on the index key and trying to get X lock to the RID. This is the locking behavior behind. When you have with UPDLOCK hint added, the locking sequence will be

In this case, we need UPDLOCK hint to apply U lock to the index key to prevent other sessions to get the U lock on the same resource. The alternative solution is to change MySignal table from HEAP to clustered index. From eliminating deadlock perspective, both approaches work perfectly. Further more, SQL developers should avoid the situation that using index as a navigator to update the same record in the base table twice within the same transaction without locking the navigator index key.
Brought you by http://www.sqlnotes.info

Share/Bookmark


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.