What is the difference between ROWLOCK, UPDLOCK and HOLDLOCK

Question

1) some time we use ROWLOCK like SELECT * FROM TESTTABLE WITH (ROWLOCK) WHERE ID IN (1,2,2,4,5)
ROWLOCK actually lock the row ? what isolation should be used for ROWLOCK ?
i always use sql server default isolation.

2) what is UPDLOCK does ? how and when it lock the row if we use UPDLOCK in select statement?

3) what is HOLDLOCK ? when it is used and what it does ?
i saw often HOLDLOCK used along with UPDLOCK. why ?

please share knowledge. thanks

Answer 1

Hi @Sudip Bhatt ,

Use of ROWLOCK

1. The ROWLOCK row-level lock ensures that when the user obtains the updated row, it will not be modified by other users during this time. Therefore, row-level locks can ensure data consistency and improve the concurrency of data operations.
2. ROWLOCK tells SQL Server to only use row-level locks. ROWLOCK syntax can be used in SELECT, UPDATE and DELETE statements.
3. For example, in the select statement --Execute in A connection SET TRANSACTION ISOLATION LEVEL REPEATABLE READ begin tran select * from tablename with (rowlock,UpdLock) where id=3 waitfor delay '00:00:05' commit tran --If executed during B connection update tablename set colname='10' where id=3 -- then wait for 5 seconds update tablename set colname='10' where id <>3 -- can be executed immediately Note:
4. If you use too many rows by mistake, the database will not be smart enough to automatically upgrade row-level locks to page locks, and the server will consume a lot of memory and CPU due to the overhead of row-level locks until it fails to respond.
5. In the select statement, RowLock is meaningless when the combination is not used. It is useful to establish a combination of With (RowLock, UpdLock). The data queried is locked by RowLock. When the data is updated, the lock will be freed

UPDLOCK uses an update lock when reading a table instead of a shared lock, and keeps the lock until the end of the statement or transaction. The advantage of UPDLOCK is that it allows you to read data (without blocking other transactions) and update the data later, while ensuring that the data has not been changed since the last time the data was read.When we use UPDLOCK to read the record, we can add an update lock to the fetched record, so that the locked record cannot be changed in other threads and can only be changed after the end of the transaction of this thread.The following example:

BEGIN TRANSACTION -- start a transaction
SELECT Qty
FROM myTable WITH (UPDLOCK)
WHERE Id in (1,2,3)
UPDATE myTable SET Qty = Qty-A.Qty
FROM myTable AS A
INNER JOIN @_Table AS B ON A.ID = B.ID
COMMIT TRANSACTION -- Commit the transaction

In this way, during the update, other threads or transactions cannot change the records with IDs 1, 2, 3 before these statements are executed. Others can be modified and read. 1, 2, 3 can only be read. If you want to modify it, you can only wait for the completion of these statements before you can operate. So as to ensure that the data is modified correctly.

HOLDLOCK, When this option is selected,SQL Server  will hold this shared lock until the end of the entire transaction, and will not release it on the way.It is similar to the highest isolation level of SERIALIZABLE.

The following article may be useful to you：Confused about UPDLOCK, HOLDLOCK、 What is the difference between HOLDLOCK and UPDLOCK in sql server、UPDLOCK, HOLDLOCK AND NOLOCK in SQL Server 2014、What is the difference between HOLDLOCK and UPDLOCK in sql server

Best Regards Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it. Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

You can see the full explanation in the Table Hints page in the documentation.

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.
HOLDLOCK only applies for the duration of the statement it is applied on, so it makes sense to combine it with UPDLOCK to extend it for the duration of the whole transaction.
ROWLOCK forces the locks to be taken only on rows, without escalating to Page or Table.

Answer 3

You have already asked in a thread - and accepted an answer - about UPDLOCK, so why do you ask about it again?

ROWLOCK is a hint you should use sparingly. Best is to let SQL Server to determine the granularity. You other thread about the queue table where we discussed the READPAST hint is the exception that proves the rule.