What is the usage of UPDLOCK in Select and Update statement

Sudip Bhatt 2,271 Reputation points
2020-09-18T11:54:00.393+00:00

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.

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,516 questions
{count} votes

2 answers

Sort by: Most helpful
  1. EchoLiu-MSFT 14,571 Reputation points
    2020-09-21T05:46:02.32+00:00

    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.

    1 person found this answer helpful.
    0 comments No comments

  2. SQLZealots 276 Reputation points
    2020-09-19T03:45:39.67+00:00

    You may refer the below link:
    sql-server-update-lock-and-updlock-table-hints

    0 comments No comments