none
Use of the "updlock" hint with update and insert statements RRS feed

  • Question

  • I have inherited some stored procedures and am trying to figure out why the developers decided to use the "updlock" hint on many of the update and insert statements. I have looked around everywhere and have found only one explanation of why "update...with (updlock)" can be useful, namely when a table has no clustered index: http://www.sqlnotes.info/2012/10/10/update-with-updlock/ I have found nothing yet that mentions why "insert into...with (updlock)" might be used. I understand why the hint might be useful on select statements in some cases, but if all of the tables have clustered indexes, is there any good reason to use it on update and insert statements?

    Thanks,

    Ron


    Ron Rice

    Friday, September 5, 2014 7:36 PM

Answers

  • This form of deadlock error can occur on a table which has a clustered index.

    If you are doing updates on a table which has a clustered index and that table also has a nonclustered index and the nonclustered index is used to find the row to update you can see this type of deadlock.  For example create a table with a clustered primary key index and a nonclustered index by running

    Create Table Foo(PK int primary key identity, OtherKey varchar(10), OtherData int);
    go
    Insert Foo Default Values;
    go 10000
    Update Foo Set OtherKey = 'C' + Cast(PK As varchar(10))
    Create Unique Index FooIdx On Foo(OtherKey);
    

    That creates a table with 10000 rows, a clustered index and a nonclustered index.  Then run

    Begin Transaction
    Update Foo Set OtherData = 1 Where OtherKey = 'C5'

    That will use the FooIdx index to find the row that needs to be updated.  It will get a U lock on the index row in the FooIdx index, then an X lock on the row in the clustered index, update that row, then free the U lock on FooIdx, but keep the X lock on the row in the clustered index.  (There is other locking going on, but to simplify things, I'm only showing the locks that lead to the deadlock).

    Then in another window, run

    Begin Transaction
    Update Foo Set OtherData = 2 Where OtherKey = 'C5'

    This will get a U lock on the index row in the FooIdx index, then try to get an X lock on the row in the clustered index.  But that row is already exclusively locked, so this second window will wait holding a U lock on FooIdx row and is waiting for an X lock on the clustered index row.

    Now go back to the first window and run

    Update Foo Set OtherData = 3 Where OtherKey = 'C5'

    This will once again try to get the U lock on the FooIdx row, but it is blocked by the U lock the second window holds.  Of course the second window is blocked by the X lock on the clustered index row and you have a deadlock.

    All that said, I certainly do not routinely code my updates with UPDLOCK.  I try to design databases and write code so that deadlocks will be rare without holding excessive locks.  The more locks you hold and the longer you hold them, the more blocking you will get and the slower your system will run.  So I write code that if a deadlock exception occurs, it is properly handled.  Then if too many deadlocks occur, that is the time to go back to the code to see what changes are needed to decrease the number of deadlocks (one way to do that may be to get locks earlier and/or hold them longer. 

    But I wouldn't worry much about this form of deadlock.  It is, in my experience, vary rare.  I don't recall ever seeing it in a production environment.

    Tom

    Friday, September 5, 2014 10:29 PM

All replies

  • Hi Ron,

    According to MSDN http://msdn.microsoft.com/en-us/library/ms187373.aspx this locks are used and  held till the transaction are goind on, so It has nothing to do with Clustered index or any kind of indexes.

    ------------------Taken from MSDN ------------------------------

    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.


    Thanks
    Manish
    ___________________________________________________________________________________
    Please click Mark as Answer if my post solved your problem and click Vote as Helpful if this post was useful.



    Friday, September 5, 2014 7:47 PM
  • This form of deadlock error can occur on a table which has a clustered index.

    If you are doing updates on a table which has a clustered index and that table also has a nonclustered index and the nonclustered index is used to find the row to update you can see this type of deadlock.  For example create a table with a clustered primary key index and a nonclustered index by running

    Create Table Foo(PK int primary key identity, OtherKey varchar(10), OtherData int);
    go
    Insert Foo Default Values;
    go 10000
    Update Foo Set OtherKey = 'C' + Cast(PK As varchar(10))
    Create Unique Index FooIdx On Foo(OtherKey);
    

    That creates a table with 10000 rows, a clustered index and a nonclustered index.  Then run

    Begin Transaction
    Update Foo Set OtherData = 1 Where OtherKey = 'C5'

    That will use the FooIdx index to find the row that needs to be updated.  It will get a U lock on the index row in the FooIdx index, then an X lock on the row in the clustered index, update that row, then free the U lock on FooIdx, but keep the X lock on the row in the clustered index.  (There is other locking going on, but to simplify things, I'm only showing the locks that lead to the deadlock).

    Then in another window, run

    Begin Transaction
    Update Foo Set OtherData = 2 Where OtherKey = 'C5'

    This will get a U lock on the index row in the FooIdx index, then try to get an X lock on the row in the clustered index.  But that row is already exclusively locked, so this second window will wait holding a U lock on FooIdx row and is waiting for an X lock on the clustered index row.

    Now go back to the first window and run

    Update Foo Set OtherData = 3 Where OtherKey = 'C5'

    This will once again try to get the U lock on the FooIdx row, but it is blocked by the U lock the second window holds.  Of course the second window is blocked by the X lock on the clustered index row and you have a deadlock.

    All that said, I certainly do not routinely code my updates with UPDLOCK.  I try to design databases and write code so that deadlocks will be rare without holding excessive locks.  The more locks you hold and the longer you hold them, the more blocking you will get and the slower your system will run.  So I write code that if a deadlock exception occurs, it is properly handled.  Then if too many deadlocks occur, that is the time to go back to the code to see what changes are needed to decrease the number of deadlocks (one way to do that may be to get locks earlier and/or hold them longer. 

    But I wouldn't worry much about this form of deadlock.  It is, in my experience, vary rare.  I don't recall ever seeing it in a production environment.

    Tom

    Friday, September 5, 2014 10:29 PM