SQL Server When to use RowLOCK, UPDLOCK etc

Question

BEGIN TRANSACTION
UPDATE dummy WITH (ROWLOCK)
SET   name = 'KilroyWasHere'
WHERE  id BETWEEN 1 AND 10
SELECT @@rowcount

SELECT COUNT(*) FROM TESTTABLE WITH (ROWLOCK, READPAST)

i found above code in a site.

1) see the above example where begin trans has been used but they use ROWLOCK. reason not clear.
when we update a table with in begin tran then table gets locked. so we need to mention explicit ROWLOCK ?

2) in select statement why ROWLOCK used? what is the meaning of ROWLOCK usage in select statement. i know READPAST meaning. READPAST will skip the lock rows.

3) UPDATE Lock code taken from here https://www.mssqltips.com/sqlservertip/6290/sql-server-update-lock-and-updlock-table-hints/
-- run this in query window 1
BEGIN TRANSACTION

SELECT @@SPID AS FirstTransactionProcessID

SELECT ID 
FROM ##TableB WITH (UPDLOCK)
WHERE ID=1

 --1
UPDATE ##TableA 
SET Val = 'E'
WHERE ID = 1 

what will be result if we use UPDLOCK in select statement ?

some time i have seen people use UPDLOCK hint for UPDATE table along with HOLDLOCK. when i am updating a table with in Begin Trans then table gets locked automatically then why should one explicitly lock table using UPDLOCK & HOLDLOCK.

please tell me scenario where one need to use UPDLOCK & HOLDLOCK. along with Begin Transaction.

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.
3. Use in select statement

   --A Execute in 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
   

Points to note when using ROWLOCK in SQL Server

1. 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.
2. In the select statement, RowLock is meaningless when the combination is not used. The combination With(RowLock,UpdLock) is established. The data queried is locked by RowLock. When the data is updated, the lock will be freed

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

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.

Hot issues October --https://learn.microsoft.com/en-us/answers/questions/142427/users-always-get-connection-timeout-problem-when-u.html

Answer 2

You have asked this before, and I have answered before. If you found the code "at a site", you could have given link to it, so that we get a context. (I get the feeling that I may have written the above myself. In a response to a question of yours.)

ROWLOCK - this is a hint you should very rarely use. It may make sense together with the READPAST hint if you are having something like a queue table - you don't want page or table locks in that context.

The purpose of the UPDLOCK is to make sure that the row(s) we read does not change until we come back to update them. And furthermore, it stops another process that runs the same code at the SELECT statement.

HOLDLOCK is a synonym for SERIALIZABLE. I have rarely, if ever, had need for this hint.

I would really recommend that you go back and review your old threads rather than posting the same question again.

Answer 3

Sir @Erland Sommarskog please guide me further.

Sorry Sir asking question once again

BEGIN TRANSACTION  
 UPDATE dummy WITH (ROWLOCK)  
 SET   name = 'KilroyWasHere'  
 WHERE  id BETWEEN 1 AND 10  

1) when we execute any update statement then table lock occur then why should i use ROWLOCK hint with update statememnt ?
please guide me what will be advantage of using ROWLOCK along with update statement when begin trans is used ?

2) SELECT COUNT(*) FROM TESTTABLE WITH (ROWLOCK, READPAST)

I understand the meaning of READPAST. it skip locked rows but if we use ROWLOCK in select then what it will do?
What ROWLOCK hint does when it is used in select statement ?

3) Sir you said - ROWLOCK - this is a hint you should very rarely use. It may make sense together with the READPAST hint if you are having something like a queue table - you don't want page or table locks in that context.

what special meaning it brings when ROWLOCK hint is used in select statement with READPAST ?

4) Sir you said - The purpose of the UPDLOCK is to make sure that the row(s) we read does not change until we come back to update them. And furthermore, it stops another process that runs the same code at the SELECT statement.

above statement not clear to me. please elaborate UPDLOCK hint significant used in UPDATE statement. please show a example where UPDLOCK will be useful.

Thanks

Answer 4

@EchoLiu-MSFT

Sir i test your code and comment this line SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

i also do not use UPDLOCK but code worked as expected. first transaction block the second one to update data.

--SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
begin tran
select * from employees with (rowlock) where employeeid=3
waitfor delay '00:00:25'
commit tran

1) i saw rowlock block second transaction without UPDLOCK hint and i also do not set isolation too but still code works.

2) sir you said ROWLOCK HINT is useless without UPDLOCK hint but in my case ROWLOCK works fine without UPDLOCK.

3) when we use BEGIN TRAN & COMMIT TRAN then sql server place table level lock for UPDATE statement. if it is true then why we need to use explicit lock during update statement ?

4) please guide me more to drive out my confusion. please also tell me with example like when to use UPDLOCK in update statement.
exclusive lock place table wise lock or just row lock ?

please answer sir to drive out my all confusion for lock hint. thanks