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: Transaction Starts
- Session 1: Lock Acquired: IX lock on the table
- Session 1: Lock Acquired: U lock on the key of the non clustered primary key (and IU lock on the page the index key located)
- Session 1: Lock Acquired: X lock on RID (and IX lock on the page the Row located)
- Session 1: Lock Released: U lock on the key of the non clustered primary key (and IU lock on the page the index key located). This is because the index key is not changed. There is no lock conversion on the index key
- Session 1 status: update query is done. Tansaciton is open. X lock will be held until transaction complete
- Session 2: Transaction Starts
- Session 2: Lock Acquired: IX lock on the table
- Session 2: Lock Acquired: U lock on the key of the non clustered primary key (and IU lock on the page the index key located)
- Session 2: Lock Acquiring: X lock on RID — this is blocked
- Session 2 status: this session is blocked by the X lock held on RID by Session 1. Currently this session owns U lock on the index key
- Session 1: Lock Acquired: IX lock on the table — trying to update Description field to Value2
- Session 1: Lock Acquiring: U lock on the key of the none clustered primary key — this is blocked
- Session 1 status: Trying to get U lock on the index key and get X lock on the Row(although it already owns the X lock on the row)
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
- Session 1: Transaction Starts
- Session 1: Lock Acquired: IX lock on the table
- Session 1: Lock Acquired: U lock on the key of the non clustered primary key (and IU lock on the page the index key located)
- Session 1: Lock Acquired: X lock on RID (and IX lock on the page the Row located)
- Session 1 status: update query is done. Tansaciton is open. X lock on RID will be held until transaction complete. U lock on the index key will be held until transaction complete
- Session 2: Transaction Starts
- Session 2: Lock Acquired: IX lock on the table
- Session 2: Lock Acquiring: U lock on the key of the non clustered primary key — blocked becuase U lock on the key has hold by session 1
- Session 2 status: this session is blocked. It only owns IX lock on the table and IU lock on the index page
- Session 1: Lock Acquired: IX lock on the table — trying to update Description field to Value2
- Session 1: Lock Acquired: U lock on the key of the non clustered primary key — this is no blocked
- Session 1: continue to run until end of the transaction
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