locked
batch updates RRS feed

  • Question

  • Hello all,

    I have a large indexed table, aaproximately half a  million records are getting updated every 15 minutes.I dont want to lock the entire table for this,data should be available to users while performing the update operation.I am using insert ..... select from a temp work table based on some filters.I have couple of  approaches in my mind .

    1. split records to batches which contains 5000 rows based on row numbers and execute it using while loop.
    2. use ALTER TABLE SET (LOCK_ESCALATION = DISABLE) and update it in one shot.
    3. use ALTER TABLE SET (LOCK_ESCALATION = DISABLE) and split it to batches in more efficient manner rather than blindly use 5000 .


    what should be my approach??


    Thanks,

    Shipin

    Tuesday, February 7, 2012 11:31 AM

Answers

  • I would advice against fiddling with lock escalation.

    Using a batched update, can very well be a reasonable approach. However, keep in mind that you must be able to handle that the operation fail half-way through, for instance because of a power failure. How do you ensure that you don't lose any updates, or that you don't perform any updates twice. Well, if you do:

       UPDATE Customers
       SET    Name = 'Lewis'
       WHERE  CustomerID = 7671

    And the name of the customer is always Lewis, this is not a big deal, but if you have updates like:

       UPDATE Customers
       SET    EarnedPoints = EarnedPoints + NewPoints
       FROM   Customers C
       JOIN   #temp t ON C.CustomerID = t.CustomerID

    You need to be careful.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by KJian_ Tuesday, February 14, 2012 2:52 AM
    Tuesday, February 7, 2012 1:07 PM

All replies

  • I would advice against fiddling with lock escalation.

    Using a batched update, can very well be a reasonable approach. However, keep in mind that you must be able to handle that the operation fail half-way through, for instance because of a power failure. How do you ensure that you don't lose any updates, or that you don't perform any updates twice. Well, if you do:

       UPDATE Customers
       SET    Name = 'Lewis'
       WHERE  CustomerID = 7671

    And the name of the customer is always Lewis, this is not a big deal, but if you have updates like:

       UPDATE Customers
       SET    EarnedPoints = EarnedPoints + NewPoints
       FROM   Customers C
       JOIN   #temp t ON C.CustomerID = t.CustomerID

    You need to be careful.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by KJian_ Tuesday, February 14, 2012 2:52 AM
    Tuesday, February 7, 2012 1:07 PM
  • In addition I would prefer the below technique

    Make sure that ID column has CI 

    DECLARE @x INT
    SET @x = 1
    WHILE @x < 44,000,000  -- Set appropriately
    BEGIN


        UPDATE Table SET a = c+d where ID BETWEEN @x AND @x + 10000


        SET @x = @x + 10000
    END


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

    Tuesday, February 7, 2012 1:09 PM
    Answerer