question

SudipBhatt-9737 avatar image
0 Votes"
SudipBhatt-9737 asked ·

What is the difference between ROWLOCK, UPDLOCK and HOLDLOCK

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

sql-server-transact-sql
· 1
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Do you have any updates?
Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Echo

0 Votes 0 ·
EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered ·

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 serverUPDLOCK, HOLDLOCK AND NOLOCK in SQL Server 2014What 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.


· 1 · Share
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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.

ROWLOCK on its own ensures nothing of the sort you say. If you say

SELECT * FROM tbl WITH (ROWLOCK)

with the default transaction isolation level READ COMMITTED, you force SQL Server to locks row by row when reading the table, but the lock for a row will be released when row has been read.

If you leave out WITH (ROWLOCK) above, SQL Server will determine about the lock granularity on its own, and probably go for page locks or a table lock.




1 Vote 1 ·
APoblacion avatar image
0 Votes"
APoblacion answered ·

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.

· 2 · Share
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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.

This is in accurate. Both UPDLOCK and HOLDLOCK applies to the end of the transaction.

HOLDLOCK is a synonym for SERIALIZABLE.



1 Vote 1 ·

you said ROWLOCK forces the locks to be taken only on rows, without escalating to Page or Table.

1) so if we use ROWLOCK with select statement then rows will be locked at all ? if not then tell me scenario where ROWLOCK hint is used ?

2) sorry do not understand how HOLDLOCK works? does it place lock at table level instead of row lock ?

does lock the entire table ? if yes then it will create performance hit because from other session when any INSERT/UPDATE OR DELETE operation happen on a specific table where HOLDLOCK applied then those operation will be blocked. am i right ?

3) UPDLOCK & ROWLOCK both place lock then what is difference between two locking approach ? please discuss it with a example and scenario.

waiting for a good explanation with examples. thanks


0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ·

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.

· 2 · Share
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

i like to know if i use ROWLOCK hint with SQL Select statement then what will happen ?

i need a good explanation of ROWLOCK hint and i need to understand how it worked.

sql server place lock automatically during UPDATE & Delete statement but still people use UPDLock or ROWLOCK hint....just like to know why ?

please sir guide me. thanks

0 Votes 0 ·

I have already answered this in one of your many threads.

SQL Server can take locks on different levels: row, page, partition and table. Best is to let SQL Server decided what is best. But you can use hints to mandate the lock to be taken on row, page or table-level. (Don't think there is a hint for partition level locks.)

What can happen if you abuse the ROWLOCK hint and use it query will millions of rows will be read is that you can consume a lot of memory for all those rowlocks.

1 Vote 1 ·