Commit transaction inside loops

  • Hello, our users are complaining about blocking during a delete loop so I'm thinking that adding begin tran; and commit tran; inside the loops would allow the other queries to squeeze through in between delete loops.  Is this how people would achieve a reduction in impact while deleting data and is the below query pretty standard? What happens if there is a communication problem and the connection drops. Would it leave the table locked up?

    SET NOCOUNT ON;

    DECLARE @r INT;
    SET @r = 1;

    WHILE @r > 0
    BEGIN
    BEGIN TRAN;
    DELETE top (1000) abc
    FROM [database1].[dbo].[table1] abc
    JOIN [database2].[dbo].[STG_table] stg on abc.SEQUENCE_NUMBER = stg.SEQUENCE_NUMBER
    SET @r = @@ROWCOUNT;
    COMMIT TRAN;
    END;
  • PHXHoward wrote:

    Hello, our users are complaining about blocking during a delete loop so I'm thinking that adding begin tran; and commit tran; inside the loops would allow the other queries to squeeze through in between delete loops.  Is this how people would achieve a reduction in impact while deleting data and is the below query pretty standard? What happens if there is a communication problem and the connection drops. Would it leave the table locked up?

    SET NOCOUNT ON;

    DECLARE @r INT;
    SET @r = 1;

    WHILE @r > 0
    BEGIN
    BEGIN TRAN;
    DELETE top (1000) abc
    FROM [database1].[dbo].[table1] abc
    JOIN [database2].[dbo].[STG_table] stg on abc.SEQUENCE_NUMBER = stg.SEQUENCE_NUMBER
    SET @r = @@ROWCOUNT;
    COMMIT TRAN;
    END;

    Have you looked at the execution plan to see what's going on?  Also, I don't believe that doing this in a transaction will help anything because a single delete is inherently done in a system transaction.

    The other question to ask is how many rows are you trying to delete compared to how many are in the table?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Would you be able to use TRUNCATE TABLE instead to remove all the rows?  That will be vastly faster.

    If the table has an identity column, and you want to maintain the high value, you will have to save it and reset it yourself after the TRUNCATE; the TRUNCATE will reset the identity value to the original seed value.

     

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • I can't use truncate because not all rows are deleted.  The delete is matching rows to rows in a stage table that qualify to be deleted.

    The question I have in looking at the example is, will BEGIN TRAN; and COMMIT TRAN; inside the LOOP allow other queries that are blocked to get through in between each loop.

  • Hi, yes that is my concern that BEGAN TRAN; and COMMIT TRAN; might not be improving things and could expose the query to holding a lock should something go wrong with the SSIS package where the step is executed.

  • Since you are not deleting anything out of database2, you could get a performance boost by pulling that table into a table variable/temp table and remove the cross database lookups. I have found in some cases, cross database queries can be slow.

    Now, with that being said, you said that there is blocking. My GUESS is that it is on table1, but if it is on STG_table, then pulling that into a temp table should also fix the blocking issue as a SELECT creates a shared lock so as long as nobody is trying to update STG_table, there should be no blocking there.

    If the blocking is on table1, then the explicit transaction shouldn't be required and likely won't help anything. What I have seen done before is to put a wait at the end of the WHILE loop to give other transactions a chance to run. If the delete of the 1000 rows completes in 10 seconds (for example, I imagine it is much faster than that per iteration of the loop), then having a 5 second wait at the end of the while loop means that a delete now takes 15 seconds per loop BUT this gives 5 seconds for other queries to run against the data.

    Now, what I think that Jeff was alluding to was if the table was 1 million rows and you are only saving 100 of those and deleting everything else, it MAY be faster to move the 100 rows, truncate the table, and migrate the data back. Same idea that Scott was hinting at.

    Now, as for your question about leaving the table locked up, I cannot remember offhand what happens, but I am pretty sure that SQL will roll your transaction back if the connection is dropped in the middle of a transaction, but it is easy to test. Steps to test it - restore the data onto a test system as you don't want to "test" on production, run your query, disconnect your network connection, wait for SSMS to give you an error, reconnect your network, reconnect to the database and run "DBCC OPENTRAN" to see if there are any open transactions or try to select from the table and see if it is blocked. If there are open transactions OR your select is blocked, then yep - your transaction will be stuck in an open state and will need to be force-rolled back. Otherwise, SQL terminated the connection for you. My opinion - I would test that yourself rather than following advice online as it MAY be that you have some database setting turned on or some trace flag or something that changes the default behavior and the advice you get on the forum is not true in your specific use case.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply