I have seen many examples of the HOLDLOCK hint being used in combination with UPDLOCK (like this). However as I read the documentation for these hints, it seems that HOLDLOCK should be redundant, since UPDLOCK already persists the lock until the end of the transaction. (Also it seems to say that HOLDLOCK only applies to shared locks anyway.)
http://technet.microsoft.com/en-us/library/ms187373.aspx
So how does HOLDLOCK affect the query, if at all?
It has a large impact.
The Update lock takes an Update lock on the row, Intent update on the page and a shared lock on the table / database.
This does not stop other queries from accessing the data within the table, since the locks on the page / database are purely share locks. They just may not clash locks against the individual row / page / table by attempting to perform an operation that would contradict locks. If that occured the request would queue behind the current locks and wait for it to come available before it could proceed.
By using holdlock, the query is being forced to be serialised, locking the table exclusively until the action has completed. This prevents anyone from reading the table unless the nolock hint is used, allowing a potentially dirty read.
To see the effect, generate an example table 'foo' and put some trash data in it.
begin tran
select * from foo with (updlock)
where tableid = 1
-- notice there is no commit tran
Open another window and try:
select * from foo
The rows come back, now commit the original query transaction. Re-run it altered to use holdlock as well:
begin tran
select * from foo with (updlock, holdlock)
where tableid = 1
Go back to the other window and try select the data again, the query will not return values since it is blocked by the exclusive lock. Commit the transaction on the first window and the results to the second query will appear since it is no longer blocked.
Final test is to use the nolock, run the transaction again using updlock and holdlock. then run the following in the second window:
select * from foo (nolock)
The results will come back automatically, since you have accepted the risk of a dirty read (read uncommitted).
So it is seen to have a large impact, in that you are forcing actions against that table to be serialised which might be what you want (depending on the update being made) or will create a very large bottleneck on that table. If everyone did that to a busy table with long running transactions then it would cause significant delays within an application.
As with all SQL features, when used correctly they can be powerful, but mis-use of a feature / hint can cause significant problems. I prefer to use hints as a last resort for when I have to override the engine - not as a default approach.