What effect does HOLDLOCK have on UPDLOCK?

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

Edit as Requested : Tested in SQL 2005, 2008, 2008R2 (All Enterprise) - all installed on pretty much default settings, test database created using all defaults (just entered the name of the DB only).

Andrew's answer's is correct as per MSDN documentation, however I tested against 2008R2 and 2012 and I am not seeing this behaviour so please TEST yourself

The behaviour I am seeing is as below:

First run this on a play database.

CREATE TABLE [dbo].[foo](    [tableid] [int] IDENTITY(1,1) NOT NULL,    [Col2] [varchar](100) NOT NULL,    CONSTRAINT [PK_foo] PRIMARY KEY CLUSTERED     (        [tableid] ASC    ))

...and put a few rows in.

Now paste this code into two query tabs (change the 'tab one' text in tab two):

begin transelect * from foo with (UPDLOCK, HOLDLOCK)where tableid = 1UPDATE foo SET Col2 = 'tab one'where tableid = 1commit tran

And put this in another tab 3:

select * from foowhere tableid = 1

1. Make sure your pointing at your play database where the table is.

2. Highlight everything BEFORE the update statement in tab 1 and execute.

3. Do the same in tab 2 you will find tab 2 will NOT complete and is still executing.

4. Now execute the simple SELECT in tab 3 in my environment it completes.

5. Highlight the update statement in tab 1 and execute it (do NOT do the commit yet), you will see tab 2 is STILL executing.

6. Go ahead and execute the commit in tab 1...tab 2 will now complete the select...you can run the rest.