Multiple Loop给SQL Server数据库锁

Question

我很难理解我在SQL Server 2008中看到的行为

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

因此，当我单独运行查询时，以下运行完美并且查询不会锁定数据库:

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;

处理(可能)一个比你想要删除的数据集小得多的数据集。

当我运行您指定的DELETE模式时，每个光标中都有WAITFOR DELAY，我让它运行20分钟，然后放弃它。没有任何东西被打印到消息窗格，它的行为就像你描述的。

当我注释掉等待时，代码(修改以适合我的示例)看起来像这样:

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/