Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
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 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 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 simple cases.

A "simple case" is where all decisions about which rows qualify are made in the hinted operators, typically an index seek on some target table index and any associated RID or Key Lookup. In this sort of situation, all update locks taken are either converted to exclusive (because the row qualifies) or released when it is determined the row does not qualify.

The subtlety is that SQL Server automatically takes U locks when finding qualifying rows, but only for the primary access method (e.g. an index seek) not any associated RID or Key Lookup. The U locks taken on the primary method are special, though, in that SQL Server releases them immediately if the row does not qualify.

When UPDLOCK is specified, the U may still be released early, but only if the row does not qualify, AND the check is performed in the primary method where SQL Server would normally take an automatic U lock anyway.

In all other cases, U locks taken on non-qualifying rows due to the UPDLOCK hint will be held to the end of the transaction. These extra U locks reduce concurrency and may cause lock escalation to a table-exclusive lock.

So, you will not want to specify UPLOCK routinely on UPDATE queries.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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