What effect does HOLDLOCK have on UPDLOCK?

Question

I have seen many examples of the HOLDLOCK hint being used in combination with UPDLOCK (like this). However Microsoft's documentation for these hints make it seem like 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.)

How does HOLDLOCK affect the query, if at all?

Solution 1

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.

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

Solution 2

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 tran

select * from foo with (UPDLOCK, HOLDLOCK)
where tableid = 1

UPDATE foo SET Col2 = 'tab one'
where tableid = 1

commit tran

And put this in another tab 3:

select * from foo
where 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.

Comments

[Mercurybullet]

nice explanation, that helps a lot!

[Andrew]

@Darren - I've rolled back the edit you have made, no lock is not the default and the second select should not take an update lock.

[Darren]

Your quite right NOLOCK is not the default, however please test the code you have put above without NOLOCK...you will see that the SELECT executes regardless of whether HOLDLOCK is added in the first window.

[Andrew]

I've retested and it remains correct for SQL Server 2005 / 2008 / 2008R2 - which was all that was available at the time of this question in 2009.

[Andrew]

Which verion of SQL Server was this run against? if 2012 then the behaviour may of changed, but 2005 / 2008 / 2008R2 holdlock would force it into serializable mode and block anything else from taking a shared lock - which is what the 'simple' select will attempt to issue.

[Andrew]

One other possibility, you have row versioning based isolation switched on - when row versioning based isolation is in place, the 'simple' select will not issue a shared lock, just a Sch-S lock - that would prevent it being blocked, although that isolation level is not the default in 2008. Will be interesting to identify the difference in the tests.

[Joel Purra]

@Andrew and Darren: please edit your respective posts and note which versions of SQL Server you have tested your solutions on. Links describing any changes in default behavior (or any settings you've made) would also be appreciated =)

[Andrew]

@JoelPurra : done, 3 versions, results as they were 3 years ago. I don't doubt Darren's results or that he has tested, this is too complex of an issue to do blind. Somewhat surprising for a question 3 years old to get so much attention.

[Joel Purra]

@Andrew: thanks! Needed to research locks last week, this is perfect =)

[Darren]

I tested against 2012. It was a clean new test database so settings would be default... TRANSACTION_ISOLATION_LEVEL is Readcomitted. snapshot_isolation_state is OFF. I Have not tested against 2008/2005 as yet.

[Darren]

Works as I describe against 2008R2 as well. Suggest others test for themselves.

[Darren]

I've tested (2008R2 and 2012) and I get the behaviour I describe in my answer...strange, I suggest others test for themselves.

[Geoff]

This behaves the same for me in 2008R2 as @Darren describes; if there is a transaction pending, a second select always succeeds if it doesn't use any hints.

[T.J. Crowder]

@ Andrew, @Darren: "By using holdlock, the query...prevents anyone from reading the table unless the nolock hint is used..." Where I think you're misunderstanding each other is that it will only block if the connection with the lock modifies the requested data. Only dirty reads are blocked. You can see this in action by following the steps in this pastie. Note how the select in Step 4 (after the lock, before modification) doesn't block, but the select in Step 6 (after modification) does.

[T.J. Crowder]

@ Andre, @Darren: And it's probably worth pointing out that the reading connection can avoid blocking by using set transaction isolation level read uncommitted. But of course, that would mean reading data that may well never "actually" exist in the database.

[Andrew]

setting that at the connection level, is in effect saying NOLOCK on the hint. With NOLOCK, not only can you read data that is not committed, you can on rare occasions read the same record twice.

[David Bridge]

Its an old post but I think it is still fair to add to. I got the same as Darren on a clean install of 2008 R2

[ta.speot.is]

There's an interesting discrepancy between @Andrew and @Darren's answers, and I can't see anything in the manual to explain it. For what it's worth, I don't see blocking with my "another window" query being select * from foo but I do see blocking with my "another window" query as select * from foo with (updlock). Which kind of makes sense, if you care about serialising queries making decisions about whether to update rows that they're reading they should have updlock in there somewhere. Disclaimer: I'm not seasoned at SQL Server locking.

[deniz]

More discussion of whether or not HOLDLOCK actually blocks selects: stackoverflow.com/q/7843733/12484