SQL Server数据库中的多个循环产生锁

Question

我很难理解我在SQL Server2008中看到的行为。

我必须创建一个作业，以一种不创建任何锁的方式删除表中的一些数据。有人建议我使用循环来解决这个问题，因为表的使用率很高，而且非常大。

因此，当我单独运行数据库时，以下代码运行得很好，并且查询不会锁定数据库：

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'

但是，如果我有两个不同的游标，它就会断掉。

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

当我同时运行这两个函数时，当我试图查询table1或table2中的任何内容时，它被锁定了。

Answer 1

下面是我为此设置的测试数据：

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;

处理的数据集(可能)比您试图从中删除的数据集小得多。

当我使用每个游标中的WAITFOR DELAY运行您指定的DELETE模式时，我让它运行了20分钟，然后就放弃了。messages窗格中没有打印任何内容，其行为与您所描述的完全相同。

当我注释掉等待时，代码(根据我的示例进行了修改)如下所示：

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;

我在6秒内就成功完成了。即使只有30k行，WAITFOR DELAY 00:00:01也会为该任务增加50分钟的无效时间。

最后注意:根据表的大小和要删除的数量，您可能会找到Brent Ozar的这篇关于“快速有序删除”的博客文章-它不会让您遍历删除集，但它可能会帮助您在不影响并发https://www.brentozar.com/archive/2018/04/how-to-delete-just-some-rows-from-a-really-big-table/的情况下做到这一点