What is the usage of UPDLOCK in Select and Update statement

Question

i have seen some time people use UPDLOCK in select and some time UPDLOCK in UPDATE statement clause.

1. what would be the impact when we use UPDLOCK in select and UPDLOCK in UPDATE statement ?

2) when we do UPDATE in BEGIN TRAN AND COMMIT TRAN then rows gets locked automatically then why we need to mention UPDLOCK in UPDATE statement to lock rows explicitly ?

What is the difference between ROWLOCK and UPDLOCK ?

please share the knowledge.

Answer 1

Hi @Sudip Bhatt ,

The function of using UPDLOCK is to allow users to read data first (and not to block other users from reading data), and to ensure that when the data is updated later, the data has not been modified by other users during this period of time.When this option is selected, SQL Server uses a modification lock instead of a shared lock when reading data, and keeps this lock until the end of the entire transaction or command. Using this option can ensure that multiple processes can read data at the same time but only this process can modify the data.
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.

In your other question（What is the difference between ROWLOCK, UPDLOCK and HOLDLOCK）, I have answered the difference between ROWLOCK and UPDLOCK, please refer to.

For more details, please refer to the official Microsoft documentation, which has a detailed introduction to various types of locks：Hints (Transact-SQL) - Table

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 may refer the below link:
sql-server-update-lock-and-updlock-table-hints