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
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.
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.
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.
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.
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.
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).
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...
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With