question

Hi @SudipBhatt-9737,

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.