Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

HOLDLOCK with UPDLOCK

Tags:

sql-server

It appears using either HOLDLOCK or UPDLOCK in a transaction (say T1), will not block read access from another transaction (say T2).

As I understand, until T1 completes, HOLDLOCK will prevent updates/deletes by T2; and a UPDLOCK will prevent updates/deletes/inserts by T2. In both these T2 will have read access to these records.

But, using both (like: HOLDLOCK, UPDLOCK) blocks T2 from even a read access. What exactly happens when we use both of them?

Thanks for your insight

Update:

That is not what I am seeing though:

For example:

In Query 1:

begin tran

select * from tblTest WITH (UPDLOCK, HOLDLOCK)

WAITFOR DELAY '00:00:10'

commit tran

In Query 2:

select * from tblTest 

Query 2 does not yield results until Query 1 finishes.

like image 694
user1967701 Avatar asked Jan 10 '13 18:01

user1967701


People also ask

What is Updlock and Holdlock?

Basically, HOLDLOCK is equivalent to using a Serializable transaction, which locks everything that is affected so that the transaction is guaranteed to be fully ACID-compliant. UPDLOCK makes the locks to be taken and held until the transaction completes.

What is the difference between Rowlock Updlock and Holdlock?

UPDLOCK is telling the server that you want to take out a lock on the table as if you were getting ready to update it. Then, the HOLDLOCK hint tells it that this is part of a serializable transaction. Where as, issuing a ROWLOCK just tells SQL Server to use row level locking.

What is with no lock in SQL Server?

The WITH (NOLOCK) table hint is used to override the default transaction isolation level of the table or the tables within the view in a specific query, by allowing the user to retrieve the data without being affected by the locks, on the requested data, due to another process that is changing it.

What is Tablock?

TABLOCK. Forces SQL Server to use a table-level lock instead of row- or page-level locks. If used with HOLDLOCK, then the lock will be held until the transaction completes. Otherwise, the lock is released as soon as the data is read. For SELECT statements, this hint forces shared table locks.


2 Answers

UPDLOCK affects the type of lock. It means for a SELECT statement that U locks will be taken rather than an S lock. At default read committed level they will be released as soon as the data is read.

The above applies to row and page locks. For table level locks BOL states

If UPDLOCK is combined with TABLOCK, or a table-level lock is taken for some other reason, an exclusive (X) lock will be taken instead.

HOLDLOCK means that you get serializable isolation semantics so the locks won't be released until the end of the transaction and at least the whole range covered by your query will be locked to prevent insert of phantoms.

A U lock is compatible with other S locks but not other U locks (See Lock compatibility matrix) so if the locks were taken out at row or page level this will not block other readers unless they too use the UPDLOCK hint.

If an object level X lock is taken out due to UPDLOCK however then readers will be blocked trying to acquire an IS lock on the table. In your example query try looking at sys.dm_tran_locks whilst the second query is blocked to see what locks both transactions have / are waiting for.

For the query in your question

SELECT *
FROM   tblTest WITH (UPDLOCK, HOLDLOCK) 

You will always get an X lock on the object if the query plan shows a scan on a heap. If it is an index scan it depends upon the locking granularity used (lock escalation to table level is generally attempted after at least 5,000 lower level locks are taken).

like image 184
Martin Smith Avatar answered Nov 02 '22 04:11

Martin Smith


I believe Martin already explained how the updlock can result in an exclusive lock (+1)... and I would rather post this as a comment / question, but my comment is too large...

Here's a quick example of the updlock resulting in the x lock...

IF (OBJECT_ID('tblTest') IS NOT NULL)
    DROP TABLE tblTest

CREATE TABLE tblTest (
    ID INT NOT NULL
)

BEGIN TRANSACTION
    SELECT * FROM dbo.tblTest WITH (UPDLOCK, HOLDLOCK) WHERE ID = 1
    SELECT * FROM sys.dm_tran_locks WHERE request_session_id = @@SPID 
COMMIT

However, if you add a clustered index to your table, the exclusive table lock goes away, and is replaced with a RangeS-U lock...

ALTER TABLE dbo.tblTest 
ADD CONSTRAINT PK_tblTest 
PRIMARY KEY CLUSTERED (ID)

BEGIN TRANSACTION
    SELECT * FROM dbo.tblTest WITH (UPDLOCK, HOLDLOCK) WHERE ID = 1
    SELECT * FROM sys.dm_tran_locks WHERE request_session_id = @@SPID 
COMMIT

So basically, do you have a clustered index on this table?

EDIT:

Another example using a non-clustered index...

IF (OBJECT_ID('tblTest') IS NOT NULL)
    DROP TABLE tblTest

CREATE TABLE tblTest (
    ID INT NOT NULL
)

CREATE NONCLUSTERED INDEX 
IX_tblTest ON dbo.tblTest (ID) 

BEGIN TRANSACTION
    SELECT * FROM dbo.tblTest WITH (HOLDLOCK) WHERE ID = 1
    SELECT * FROM sys.dm_tran_locks WHERE request_session_id = @@SPID 
COMMIT

Will result in a RangeS-S lock...

But...

BEGIN TRANSACTION
    SELECT * FROM dbo.tblTest WITH (UPDLOCK, HOLDLOCK) WHERE ID = 1
    SELECT * FROM sys.dm_tran_locks WHERE request_session_id = @@SPID 
COMMIT

Will result in an exclusive table lock...

like image 33
Michael Fredrickson Avatar answered Nov 02 '22 05:11

Michael Fredrickson