I was just exploring UPDLOCK hint in SQL-Server 2008 and I found it is very useful in avoiding certain deadlock scenarios. There are always some side effects when using these types of lock hints but I could not find any for this one.

So what happens if I use it in all my update queries? I am not talking about select, just update. How will it affect me negatively?

  • I could be wrong, but using UPDLOCK with UPDATE should have no effect whatsoever; this is the default behaviour of UPDATE. I've only ever used UPDLOCK on SELECT statements.
    – Jon Seigel
    Mar 21, 2014 at 13:53
  • 1
    it does have an effect when there are bookmark lookups involved and using UPDLOCK avoids deadlock situations by holding locks on the nonclustered index keys till the end of the transaction. But thats a positive thing. Iam searching for a negative impact of using this lock with the update statement.
    – user35964
    Mar 25, 2014 at 10:29

1 Answer 1


So what happens if I use it in all my update queries?

Like all hints, UPDLOCK should only be used where there is a clear benefit.

How will it affect me negatively?

It depends on the query and the execution plan. Assuming you are only proposing to add UPDLOCK to the target table, there may be no negative effects at all in very simple cases.

A "very simple case" is where all decisions about which rows qualify are made in a single plan operator. In this situation, update locks are only taken on rows that will definitely qualify, so no harm is done.

SQL Server automatically takes U locks when searching for qualifying rows even without an UPDLOCK hint, but only for the primary access method (e.g. an index seek and any associated RID or Key Lookup). These automatic U locks are special, in that SQL Server can still release them early if later operators in the plan determine that the row does not qualify after all.

When UPDLOCK is specified, any update locks taken cannot be released before the end of the transaction, even if other operators in the plan determine that the row does not actually qualify for the update. These extra U locks reduce concurrency and may cause lock escalation to a table-exclusive lock.

For these reasons, it is generally not a good idea to specify UPLOCK routinely on UPDATE queries.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.