如何避免大事務鎖定？

我們有一個大型應用程序正在執行，出於性能原因需要清理。然而，在設計應用程序時沒有預見到這一點。

基本上，刪除是從一個儲存過程中執行的，它首先執行幾個選擇來定義要刪除的數據，然後開始從不同的表中刪除。由於此數據之間的連結對於刪除至關重要，因此必須避免刪除，例如，在不刪除某些依賴項的情況下進行排序。因此它必須在一個事務中執行。

問題是，每當腳本執行時，應用程序本身就會變得不可用：從 Web 獲取數據或嘗試更新某個記錄時會超時。所有這些查詢都被執行事務的會話阻止

被刪除的數據不再相關，因此不應由應用程序更新。

我試過在不同的隔離級別執行事務，包括快照，但它仍然不起作用。

我怎樣才能避免這些鎖？我應該使用 READ_COMMITTED_SNAPSHOT 嗎？

提前致謝…

我認為您不必強制所有刪除都發生在一個單一的整體事務中。而不是進行以下交易：

BEGIN TRANSACTION;
 DELETE all the things from child table 1;
 DELETE all the things from child table 2;
 ...
 DELETE all the things from child table N;
 DELETE all the things from parent table;
COMMIT TRANSACTION;

為什麼不分塊刪除？您可以TOP (?)根據多少行導致什麼樣的事務持續時間來使用該參數（即使我們確實有關於您的架構的更多資訊，也沒有對此的神奇公式）。虛擬碼：

DECLARE @p TABLE(p INT PRIMARY KEY);

SELECT @rc = 1;

WHILE @rc > 0
BEGIN
 DELETE @p;

 INSERT @p SELECT TOP (?) primary_key FROM parent table 
   WHERE (clause that defines the data to be deleted);

 SET @rc = @@ROWCOUNT;

 BEGIN TRANSACTION;
   DELETE child table 1 WHERE parentID IN (SELECT p FROM @p);
   DELETE child table 2 WHERE parentID IN (SELECT p FROM @p);
   ...
   DELETE child table N WHERE parentID IN (SELECT p FROM @p);

   DELETE parent table WHERE parentID IN (SELECT p FROM @p);
 COMMIT TRANSACTION;
 -- to minimize log impact you may want to CHECKPOINT
 -- or backup the log here, every loop or every N loops
END

這可能會延長操作所花費的總時間（特別是如果您在每個循環上備份或檢查點，或使用添加人為延遲WAITFOR，或兩者兼而有之），但應該允許其他事務在塊之間潛入，而是等待較短的事務的整個過程。

我在這裡寫了很多關於這種技術的文章。

我們有類似的情況，我們必須創建一個更大的腳本來以適當的方式解決刪除問題。

注意：大型刪除操作（一個或多個事務）會遇到的另一個問題是，您的事務日誌在刪除執行時會增長得非常快。因此，就磁碟空間而言，請為此做好準備。

可能需要您將父數據集標記為“已刪除”，以使其與您的應用程序正常工作。

我們已將問題解決如下：

• 創建一個工作表，其中您計劃刪除的所有父記錄都被選中。該表將記錄要刪除的父記錄，以及一些時間統計資訊，您何時選擇刪除它，何時開始刪除以及何時完成。父對象僅由其主鍵引用。

• 如果可行，在同一個表中創建或為父母的孩子添加更多工作表，統計資訊是可選的，但要使其正常工作，您至少需要一個狀態“成功刪除”或“待完成”。

• 現在選擇插入所有註定記錄的主鍵到工作表中

• 完成此操作後，您現在可以進行大規模刪除了：

  • 為您的樹的葉子創建一個游標，並開始逐個刪除它們或每個事務中的一堆。
  • 當沒有更多的外部葉子時，繼續下一個級別，直到您位於父對象處。

這似乎是“只是為了刪除”的大量工作，但使用這種方法，您的刪除只會創建非常非常短的事務，並且具有非常短的鎖。鎖總是只覆蓋一張桌子，因此打擾某人的風險非常低。

如果您的刪除時間過長，您可以隨時停止該過程並稍後重新啟動。您甚至可以停止該過程，添加更多記錄並重新啟動該過程。

您將沒有部分刪除的記錄，因為您跟踪了每條記錄的刪除狀態。

注2：我們沒有做的，但也是有效的，是在你想刪除父級的那一刻收集子級。儘管如此，您仍遵循逐個記錄的刪除策略以保持事務較小且鎖定較短。

引用自：https://dba.stackexchange.com/questions/49264