我很難理解我在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[/span
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 driven from Table1'
WaitFor DELAY '00:00:01'
END
CLOSE DEL_CURSOR
DEALLOCATE DEL_CURSOR
列印 '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[/span
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 driven from Table1'
WaitFor DELAY '00:00:01'
END
CLOSE DEL_CURSOR
DEALLOCATE DEL_CURSOR
PRINT ''Cursor Closed'
print N'In SecondCursor'/span>
DECLARE DEL_CURSORR CURSOR FOR Select top1000 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'/span>
Delete top(10) from Table2 where PK < =@pkQ
FETCH NEXT FROM DEL_CURSORR into @pkQ
WaitFor DELAY '00:00:01'/span>
PRINT '10 driven from Table2'
END
CLOSE DEL_CURSORR
DEALLOCATE DEL_CURSORR
當我同時運行這兩個表，并試圖查詢表1或表2中的任何內容時，它只是被鎖定。
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
身份證
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/yidong/314528.html
標籤：