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

Update with UPDLOCK

You May Also Like

17 thoughts on “Update with UPDLOCK

  1. The query processor requests a U lock on the read-cursor access method to help prevent a common cause of deadlocks (strictly, only a shared lock is necessary). Obviously, this is only sufficient where the read and write access methods are the same. In your example, the read access method is a non-clustered index, whereas the write access method is a table scan.

    The same issue can occur just as easily with a clustered table, where the clustered index is not also the read access method. So, a deadlock occurs for your example where the table is:

    CREATE TABLE MySignal
    (
    ID bigint,
    ID2 bigint,
    Description char(2000) not null default(”),

    PRIMARY KEY NONCLUSTERED(ID),
    UNIQUE CLUSTERED(ID2)
    ) ;

    …with ID2 populated with the same values as ID. In this case, the read access method is via the non-clustered PK, but the write method is via the clustered unique index.

    1. Your are right. Precisely, the write method is a bookmark lookup where it uses the RID to locate the row in the HEAP (Clustered index keys for B-Tree) and execute the modification. Cheers!

  2. A table is used to store auto increment id generated using code by +1 method. Now two application are running parallely and using this same table. Any application that fetch data need to autoincrement it. How we can prevent same id to be used by two application within a span of few miliseconds?

    Also 1st application uses no lock during fetching of auto increment ids.

    eg: 1st application query
    Select NextId from ABC
    45
    Update ABC SET NextId = 45+1

    In fraction of seconds, 2nd appication query also uses below query.
    Select NextId from ABC
    45
    Update ABC SET NextId = 45+1

    Note: we can only modify 2nd application

  3. Hi, could you put a SQL Server Profiler trace in both cases?

    I’ve done the same test and i got exactly the same locks events either using WITH UPDLOCK or without it.

    Thank you

  4. Hi John, I am trying to resolve a rare deadlock which locks an object. A table has 14 mil rows and a primary clustered key Col1. Simple update. Two simple update run mili sec apart
    Update Tbl1 set col2 = @p1 where col1 = @p0
    In execution plan, It’s only one step which is Clustered Index Update on Col1. Is there any other way to resolve this kind of deadlock? The database is on AG group.

      1. Thank you for your reply. I am trying to understand this deadlock but I cant figure out why it is locking an object.
        Please see below Deadlock graph. Thanks.

        unknown

        unknown

        (@P0 int,@P1 int)UPDATE Tbl SET Col2=@P0 WHERE Col1 =@P1

        unknown

        unknown

        (@P0 int,@P1 int)UPDATE Tbl SET Col2=@P0 WHERE Col1 =@P1

  5. Please see below Deadlock graph. Thanks.

    unknown

    unknown

    (@P0 int,@P1 int)UPDATE Tbl SET Col2=@P0 WHERE Col1 =@P1

    unknown

    unknown

    (@P0 int,@P1 int)UPDATE Tbl SET Col2=@P0 WHERE Col1 =@P1

    <waiter id="pr

    
    

    ocess5ff0168c8″ mode=”IX” requestType=”convert” />

Leave a Reply

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

C# | HTML | Plain Text | SQL | XHTML | XML | XSLT |

This site uses Akismet to reduce spam. Learn how your comment data is processed.