CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008
InfiniteLoop
InfiniteLoop

Reputation: 93

Multiple Loop gives lock in SQL Server database

I'm having a hard time understanding the behavior that I see in SQL Server 2008.

I have to create a job that deletes some data inside tables in a way that will no create any locks. I was suggested to use looping to solve this problem since the tables are used heavily and are very big.

So the following runs perfectly and the query doesn't lock the database when I'm running it on its own:

DECLARE @pkQ BIGINT
            
DECLARE DEL_CURSOR CURSOR STATIC FOR Select PK from Table1 where Inserted_Date <= DateAdd(WEEK, -1, Getdate()) order by PK desc

OPEN DEL_CURSOR

    FETCH NEXT FROM DEL_CURSOR into @pkQ
    WHILE @@FETCH_STATUS = 0
            BEGIN
                DELETE TOP(10) from Table1 where PK <= @pkQ
                FETCH NEXT FROM DEL_CURSOR into @pkQ
                PRINT '10 deleted from Table1'
                WaitFor DELAY '00:00:01'
            END
CLOSE DEL_CURSOR
DEALLOCATE DEL_CURSOR
PRINT 'Cursor Closed'

However, If I have 2 different cursors, it just breaks.

DECLARE @pkQ BIGINT
            
DECLARE DEL_CURSOR CURSOR STATIC FOR Select PK from Table1 where Inserted_Date <= DateAdd(WEEK, -1, Getdate()) order by PK desc

OPEN DEL_CURSOR

    FETCH NEXT FROM DEL_CURSOR into @pkQ
    WHILE @@FETCH_STATUS = 0
            BEGIN
                DELETE TOP(10) from Table1 where PK <= @pkQ
                FETCH NEXT FROM DEL_CURSOR into @pkQ
                PRINT '10 deleted from Table1'
                WaitFor DELAY '00:00:01'
            END
CLOSE DEL_CURSOR
DEALLOCATE DEL_CURSOR
PRINT ''Cursor Closed''


                    print N'In SecondCursor'
    DECLARE DEL_CURSORR CURSOR FOR Select top 1000 PK from Table2 where Insert_Date < DateAdd(Month, -6, Getdate()) order by PK desc
    
    OPEN DEL_CURSORR
    
        FETCH NEXT FROM DEL_CURSORR into @pkQ
        WHILE @@FETCH_STATUS = 0
                BEGIN
                    WAITFOR DELAY '00:00:02'
                    Delete top(10) from Table2 where PK <= @pkQ 
                    FETCH NEXT FROM DEL_CURSORR into @pkQ
                    WaitFor DELAY '00:00:01'
                    PRINT '10 deleted from Table2'
                END
    CLOSE DEL_CURSORR
    DEALLOCATE DEL_CURSORR

When I'm having both run and I'm try to query anything in table1 or table2, it's just locked.

Upvotes: 0

Views: 286

Answers (1)

paneerakbari
paneerakbari

Reputation: 725

Here's the test data that I set up for this:

DROP TABLE IF EXISTS #Table1;
DROP TABLE IF EXISTS #Table2;

SELECT 1001999 + n AS ID
  INTO #Table1
  FROM (SELECT TOP (30000)
               n = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
          FROM sys.all_objects      AS s1
         CROSS JOIN sys.all_objects AS s2
         ORDER BY s1.[object_id]) AS x;

SELECT 1001999 + n AS ID
  INTO #Table2
  FROM (SELECT TOP (30000)
               n = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
          FROM sys.all_objects      AS s1
         CROSS JOIN sys.all_objects AS s2
         ORDER BY s1.[object_id]) AS x;

Working with (likely) a much smaller dataset than what you're trying to delete from.

When I run the DELETE pattern that you specified, with the WAITFOR DELAY in each of the cursors, I let it run for 20 minutes before just giving up on it. Nothing was being printed to the messages pane and it behaved just like you described.

When I comment out the waits, the code (modified to fit my sample) looks like this:

DECLARE @pkQ BIGINT;

DECLARE DEL_CURSOR CURSOR STATIC FOR
    SELECT ID
      FROM #Table1
     ORDER BY ID DESC;

OPEN DEL_CURSOR;

FETCH NEXT FROM DEL_CURSOR
 INTO @pkQ;
WHILE @@FETCH_STATUS = 0
    BEGIN
        DELETE TOP (10)
        FROM #Table1
         WHERE ID <= @pkQ;
        FETCH NEXT FROM DEL_CURSOR
         INTO @pkQ;
        PRINT '10 deleted from Table1';
        --WAITFOR DELAY '00:00:01';
    END;
CLOSE DEL_CURSOR;
DEALLOCATE DEL_CURSOR;
PRINT 'Cursor Closed';


PRINT N'In SecondCursor';
DECLARE DEL_CURSORR CURSOR FOR
    SELECT TOP 1000
           ID
      FROM #Table2
     ORDER BY ID DESC;

OPEN DEL_CURSORR;

FETCH NEXT FROM DEL_CURSORR
 INTO @pkQ;
WHILE @@FETCH_STATUS = 0
    BEGIN
        --WAITFOR DELAY '00:00:02';
        DELETE TOP (10)
        FROM #Table2
         WHERE ID <= @pkQ;
        FETCH NEXT FROM DEL_CURSORR
         INTO @pkQ;
        --WAITFOR DELAY '00:00:01';
        PRINT '10 deleted from Table2';
    END;
CLOSE DEL_CURSORR;
DEALLOCATE DEL_CURSORR;

And I got a successful completion in 6 seconds. Even on a measly 30k rows, the WAITFOR DELAY 00:00:01 is going to add 50 minutes of unproductive time to this task.

Final note: Depending on the size of your tables and the amount that you're looking to delete, you may find this blog post from Brent Ozar about "Fast Ordered Deletes" - it won't get you around looping over the deletion sets, but it might help you do it without effecting concurrency https://www.brentozar.com/archive/2018/04/how-to-delete-just-some-rows-from-a-really-big-table/

Upvotes: 3

Related Questions