我很難理解我在 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'
但是，如果我有 2 個不同的游標，它就會中斷。
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 中的任何內容時，它只是被鎖定了。
uj5u.com熱心網友回復：
這是我為此設定的測驗資料：
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/
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/415070.html
標籤：