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