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.
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