none
is HOLDLOCK hint is good for merge statement in SQL server 2012. RRS feed

  • Question

  • HI All,

    i want to know is HOLDLOCK hint is good for merge statement in SQL server 2012.

    Regards,

    Manish

    Tuesday, December 15, 2015 12:17 PM

Answers

  • Note here that the locks are often wider than the actual range. Say that you run

       SELECT COUNT(*) FROM tbl WHERE id BETWEEN 10 AND 20

    but there are no rows at the end points, but the nearest are id = 5 and id = 25. SQL Server will lock the entire range from 5 to 25, while semantically it is only reqiured to lock the range from 10 to 20. And thus concurrent processes that are not conflicting with our statement still will be blocked.

    Expanding on what Erland said: 

    SQL will lock the entire range for the values closest to that on the leaf level. So in this case the lock would be held for >=5  <=25 and will be held for the life of the transaction; so, if the proc does BEGIN TRAN at the top and does a whole mess of things then COMMIT at the end with the holdlock in the middle you'd be holding till everything is done... essentially till a commit or rollback is issued.

    https://technet.microsoft.com/en-us/library/jj856598.aspx

    Here's an example you can run to see how this works in action::

    Searching for WHERE CITY BETWEEN 'AZ' and 'B' will place a key range lock on the closet values (Austin & Bad Soden). See the AdventureWorks2012 example below:

    Table contains:

    Aurora
    Austell
    Austin
    Austin
    Bad Soden
    Baldwin Park
    Ballard

    As you can see AZ and B don't exist. The nearest values are Austin and Bad Soden. Locking the range for AZ to B will lock Austin to Bad Soden. Austell and Baldwin Park will still be able to be selected. See below:

    --Example that shows the leaf level locking for a range lock
    
    --Session 1:
    USE AdventureWorks2012
    BEGIN TRAN
    SELECT AddressID, City FROM Person.Address WITH (HOLDLOCK, XLOCK) WHERE City BETWEEN 'AZ' and 'B'
    
    
    --Session 2: Blocked from Key Range Lock
    USE AdventureWorks2012
    SELECT AddressID, City FROM Person.Address WITH (HOLDLOCK) WHERE City = 'Austin'
    
    --Session 3: Blocked from Key Range Lock
    USE AdventureWorks2012
    SELECT AddressID, City FROM Person.Address WITH (HOLDLOCK) WHERE City = 'Bad Soden'
    
    --Session 4: Runs no problem
    SELECT AddressID, City FROM Person.Address with (HOLDLOCK) WHERE City = 'Austell'
    
    --Session 5: Runs no problem
    SELECT AddressID, City FROM Person.Address with (HOLDLOCK) WHERE City = 'Baldwin Park'

    I hope that helps!


    I hope you found this helpful! If you did, please vote it as helpful on the left. If it answered your question, please mark it as the answer below. :)



    • Edited by Daniel Janik Tuesday, December 15, 2015 11:05 PM
    • Marked as answer by manishcal16PPS Thursday, December 17, 2015 11:21 AM
    Tuesday, December 15, 2015 10:25 PM

All replies

  • If you want to be absolutely sure that no one can modify records while your UDPATE statement is running, you can use the UPDLOCK hint.

    By default, SQL Server takes shared locks first and then escalates them to UPDLOCK when it's ready to commit an UPDATE transaction. Using UPDLOCK will override such behavior to guarantee that data will be exclusively locked for the duration of the transaction.

    So I don't think you need to use this hint in merge statement.

    On the performance parameter you can check the execution time with using hint and without using hint and decide whether you should go with hint or not. 

    Tuesday, December 15, 2015 12:28 PM
  • What is your purpose of using this hint?

    Using the HOLDLOCK hint with  MERGE  will show that locks aren’t released 
    till the insert/update  complete  to prevent the concurrency problem with MERGE.


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, December 15, 2015 12:56 PM
    Answerer
  • "By default, SQL Server takes shared locks first and then escalates them to UPDLOCK when it's ready to commit an UPDATE transaction. Using UPDLOCK will override such behavior to guarantee that data will be exclusively locked for the duration of the transaction."

    Not exactly. SQL Server uses update locks while it look if a row qualifies for a modification. If it doesn't, then it moves on to next row. If it does, the update log is converted to an exclusive lock (not exactly, but conceptually) and the row is modified.


    Tibor Karaszi, SQL Server MVP | web | blog

    Tuesday, December 15, 2015 1:49 PM
    Moderator
  • The answer is that the question is wrongly asked.

    HOLDLOCK implies SERIALIZABLE isolation level. That is, once you have read a range in a transaction, you are guaranteed that that the range will not change. That is no rows in the range will be update or deleted, nor will any new row be added. To implement this, SQL Server needs to employ some heavy-duty locking which can have negative effect on concurrency, and thus on performance.

    Note here that the locks are often wider than the actual range. Say that you run

       SELECT COUNT(*) FROM tbl WHERE id BETWEEN 10 AND 20

    but there are no rows at the end points, but the nearest are id = 5 and id = 25. SQL Server will lock the entire range from 5 to 25, while semantically it is only reqiured to lock the range from 10 to 20. And thus concurrent processes that are not conflicting with our statement still will be blocked.

    Serializable may sometimes be required, but it is nothing to use lightly. Whether it's useful for MERGE? That all depends on your actual MERGE statement. Sometimes it is right, sometimes it is wrong.

    Tuesday, December 15, 2015 1:53 PM
  • thanks all,

    my question is because i have table with 7milion row, where update and insert will happen by 3 user on same table. for insert/update i am using merge. if i am run a select query in free time query will give result within 10sec, same query if i have run at peek time it will take 54sec

    Regards,

    Manish

    Tuesday, December 15, 2015 2:42 PM
  • Hi All,

    also in my database snapshot isolation  is enable.

    Regards,

    Manish 

    Tuesday, December 15, 2015 3:04 PM
  • So if you use HOLDLOCK, you will possibly lock even more and for a longer time than what you are doing now. So, that might make things worse than now. In general, you raise the isolation level to gain correctness, not concurrency!

    When you say that "also in my database snapshot isolation  is enable", do you mean "allow snapshot isolation"? IF so that doesn't do anything unless you actually specify USE TRANSACTION ISOLATION LEVEL SNAPSHOT in your code. Well, except for increasing the resource usage, since SQL Server need to maintain ld row versions.

    Or do you mean "read committed snapshot"? This will cause all those who are using the read committed isolation level to get the versioning behaviour Again, you will have a higher resource usage But reader (those in read committed) will not be blocked by writers.


    Tibor Karaszi, SQL Server MVP | web | blog

    Tuesday, December 15, 2015 6:03 PM
    Moderator
  • Note here that the locks are often wider than the actual range. Say that you run

       SELECT COUNT(*) FROM tbl WHERE id BETWEEN 10 AND 20

    but there are no rows at the end points, but the nearest are id = 5 and id = 25. SQL Server will lock the entire range from 5 to 25, while semantically it is only reqiured to lock the range from 10 to 20. And thus concurrent processes that are not conflicting with our statement still will be blocked.

    Expanding on what Erland said: 

    SQL will lock the entire range for the values closest to that on the leaf level. So in this case the lock would be held for >=5  <=25 and will be held for the life of the transaction; so, if the proc does BEGIN TRAN at the top and does a whole mess of things then COMMIT at the end with the holdlock in the middle you'd be holding till everything is done... essentially till a commit or rollback is issued.

    https://technet.microsoft.com/en-us/library/jj856598.aspx

    Here's an example you can run to see how this works in action::

    Searching for WHERE CITY BETWEEN 'AZ' and 'B' will place a key range lock on the closet values (Austin & Bad Soden). See the AdventureWorks2012 example below:

    Table contains:

    Aurora
    Austell
    Austin
    Austin
    Bad Soden
    Baldwin Park
    Ballard

    As you can see AZ and B don't exist. The nearest values are Austin and Bad Soden. Locking the range for AZ to B will lock Austin to Bad Soden. Austell and Baldwin Park will still be able to be selected. See below:

    --Example that shows the leaf level locking for a range lock
    
    --Session 1:
    USE AdventureWorks2012
    BEGIN TRAN
    SELECT AddressID, City FROM Person.Address WITH (HOLDLOCK, XLOCK) WHERE City BETWEEN 'AZ' and 'B'
    
    
    --Session 2: Blocked from Key Range Lock
    USE AdventureWorks2012
    SELECT AddressID, City FROM Person.Address WITH (HOLDLOCK) WHERE City = 'Austin'
    
    --Session 3: Blocked from Key Range Lock
    USE AdventureWorks2012
    SELECT AddressID, City FROM Person.Address WITH (HOLDLOCK) WHERE City = 'Bad Soden'
    
    --Session 4: Runs no problem
    SELECT AddressID, City FROM Person.Address with (HOLDLOCK) WHERE City = 'Austell'
    
    --Session 5: Runs no problem
    SELECT AddressID, City FROM Person.Address with (HOLDLOCK) WHERE City = 'Baldwin Park'

    I hope that helps!


    I hope you found this helpful! If you did, please vote it as helpful on the left. If it answered your question, please mark it as the answer below. :)



    • Edited by Daniel Janik Tuesday, December 15, 2015 11:05 PM
    • Marked as answer by manishcal16PPS Thursday, December 17, 2015 11:21 AM
    Tuesday, December 15, 2015 10:25 PM