none
What is the difference between HOLDLOCK and UPDLOCK in sql server

    Question

  • just see same kind of two sql statement

    BEGIN TRAN
    
    SELECT *
    FROM   authors AU
    WITH   (HOLDLOCK, ROWLOCK)
    WHERE  AU.au_id = '274-80-9391'
    
    
    COMMIT TRAN
    BEGIN TRAN

    SELECT * FROM authors AU WITH (UPDLOCK, ROWLOCK) WHERE AU.au_id = '274-80-9391'

    COMMIT TRAN

    i like to know what is the difference between HOLDLOCK and UPDLOCK in sql server. explain with example to better understand. thanks

    Wednesday, July 15, 2015 7:57 AM

Answers

  • UPDLOCK and HOLDLOCK are actually two different types of animals.

    HOLDLOCK specifies a certain isolation level, to wite SERIALIZABLE. When you have a lock with this isolation level, you are guaranteed that a requery with in the transaction will not see any different values, including added rows. That is, if you do:

    BEGIN TRANASCTION

    SELECT COUNT(*) FROM Orders WITH (HOLDLOCK)
    SELECT COUNT(*) FROM Orders WITH

    COMMIT TRANSACTION

    HOLDLOCK is sort of deprecated, and you should use SERILIZABLE instead.

    UPDLOCK on the other specifies a certain lock type, an update lock. An update lock is not an exclusive lock in the sense that it does not block readers that only want to lock to read. However, only one process at time can hold an update lock on resource, so if two processes attempts to take an update lock on a row, one will get blocked. The purpose of an update lock is to lock in a prepartory step, because you plan to update it later.

    UPDLOCK as such does not specify an isolation level, but in practice you will get REPEATABLE READ, unless you have done SET TRANSACTION ISOLATION LEVEL SERIALIZABLE.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, July 15, 2015 10:19 PM

All replies

  • Well explained here: Confused about UPDLOCK, HOLDLOCK and UPDLOCK, HOLDLOCK AND NOLOCK in SQL Server 2014

    Please don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. web: www.ronnierahman.com

    Wednesday, July 15, 2015 8:05 AM
  • Hi Mou,

    Below goes the explanation.

    UPDLOCK is used when you want to lock a row or rows during a select statement for a future update statement. The future update might be the very next statement in the transaction.

    Other sessions can still see the data. They just cannot obtain locks that are incompatiable with the UPDLOCK and/or HOLDLOCK.

    You use UPDLOCK when you wan to keep other sessions from changing the rows you have locked. It restricts their ability to update or delete locked rows.

    You use HOLDLOCK when you want to keep other sessions from changing any of the data you are looking at. It restricts their ability to insert, update, or delete the rows you have locked. This allows you to run the query again and see the same results.

    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question.

    Regards,


    Milan Das

    Wednesday, July 15, 2015 8:19 AM
  • UPDLOCK and HOLDLOCK are actually two different types of animals.

    HOLDLOCK specifies a certain isolation level, to wite SERIALIZABLE. When you have a lock with this isolation level, you are guaranteed that a requery with in the transaction will not see any different values, including added rows. That is, if you do:

    BEGIN TRANASCTION

    SELECT COUNT(*) FROM Orders WITH (HOLDLOCK)
    SELECT COUNT(*) FROM Orders WITH

    COMMIT TRANSACTION

    HOLDLOCK is sort of deprecated, and you should use SERILIZABLE instead.

    UPDLOCK on the other specifies a certain lock type, an update lock. An update lock is not an exclusive lock in the sense that it does not block readers that only want to lock to read. However, only one process at time can hold an update lock on resource, so if two processes attempts to take an update lock on a row, one will get blocked. The purpose of an update lock is to lock in a prepartory step, because you plan to update it later.

    UPDLOCK as such does not specify an isolation level, but in practice you will get REPEATABLE READ, unless you have done SET TRANSACTION ISOLATION LEVEL SERIALIZABLE.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, July 15, 2015 10:19 PM