“簽出”記錄以進行處理的策略

我不確定是否有為此命名的模式，或者是否沒有，因為這是一個糟糕的主意。但我需要我的服務在主動/主動負載平衡環境中執行。這只是應用程序伺服器。數據庫將位於單獨的伺服器上。我有一項服務需要為表中的每條記錄執行一個程序。此過程可能需要一兩分鐘，並且每 n 分鐘重複一次（可配置，通常為 15 分鐘）。

對於需要此處理的包含 1000 條記錄的表，以及針對同一數據集執行的兩個服務，我希望每個服務“簽出”一條記錄以進行處理。我需要確保一次只有一個服務/執行緒在處理每條記錄。

我的同事過去曾使用過“鎖表”。將一條記錄寫入此表以在邏輯上鎖定另一個表中的記錄（另一個表是相當靜態的順便說一句，並且偶爾會添加一條新記錄），然後刪除以釋放鎖定。

我想知道如果新表有一個列指示它何時被鎖定，並且它目前被鎖定，而不是不斷插入刪除，這是否會更好。

有沒有人有這種事情的提示？長期（ish）長期邏輯鎖定是否存在既定模式？有關如何確保一次只有一項服務抓住鎖的任何提示？（我的同事使用 TABLOCKX 鎖定整個表。）

我不喜歡額外的“鎖定”表或鎖定整個表以獲取下一條記錄的想法。我明白為什麼要這樣做，但這也會損害正在更新以釋放鎖定記錄的操作的並發性（當兩個程序不可能在同時）。

我的偏好是將 ProcessStatusID（通常是 TINYINT）列添加到正在處理數據的表中。LastModifiedDate 有欄位嗎？如果沒有，則應添加。如果是，那麼這些記錄是否會在此處理之外更新？如果可以在此特定流程之外更新記錄，則應添加另一個欄位來跟踪 StatusModifiedDate（或類似的東西）。對於這個答案的其餘部分，我將只使用“StatusModifiedDate”，因為它的含義很清楚（事實上，即使目前沒有“LastModifiedDate”欄位，也可以用作欄位名稱）。

ProcessStatusID 的值（應放置在名為“ProcessStatus”的新查找表中，並在該表中使用外鍵）可以是：

1. 已完成（在這種情況下甚至是“待處理”，因為兩者都表示“準備好處理”）
2. 處理中（或“處理中”）
3. 錯誤（或“WTF？”）

在這一點上，可以安全地假設從應用程序中，它只是想獲取下一條記錄來處理，並且不會傳遞任何東西來幫助做出決定。所以我們想要獲取設置為“Completed”/“Pending”的最舊的（至少就 StatusModifiedDate 而言）記錄。類似於以下內容：

SELECT TOP 1 pt.RecordID
FROM   ProcessTable pt
WHERE  pt.StatusID = 1
ORDER BY pt.StatusModifiedDate ASC;

我們還希望同時將該記錄更新為“In Process”，以防止其他程序抓取它。我們可以使用該OUTPUT子句讓我們在同一個事務中執行 UPDATE 和 SELECT：

UPDATE TOP (1) pt
SET    pt.StatusID = 2,
      pt.StatusModifiedDate = GETDATE() -- or GETUTCDATE()
OUTPUT INSERTED.RecordID
FROM   ProcessTable pt
WHERE  pt.StatusID = 1;

這裡的主要問題是，雖然我們可以TOP (1)在UPDATE操作中執行 a，但無法執行ORDER BY. 但是，我們可以將它包裝在 CTE 中以結合這兩個概念：

;WITH cte AS
(
  SELECT TOP 1 pt.RecordID
  FROM   ProcessTable pt (READPAST, ROWLOCK, UPDLOCK)
  WHERE  pt.StatusID = 1
  ORDER BY pt.StatusModifiedDate ASC;
)
UPDATE cte
SET    cte.StatusID = 2,
      cte.StatusModifiedDate = GETDATE() -- or GETUTCDATE()
OUTPUT INSERTED.RecordID;

顯而易見的問題是兩個同時執行 SELECT 的程序是否可以獲取相同的記錄。我很確定帶有 OUTPUT 子句的 UPDATE，尤其是結合 READPAST 和 UPDLOCK 提示（有關更多詳細資訊，請參見下文）會很好。但是，我還沒有測試過這個確切的場景。如果由於某種原因上述查詢沒有處理競爭條件，則添加以下內容：應用程序鎖。

上面的 CTE 查詢可以包裝在sp_getapplock和sp_releaseapplock中，為程序創建一個“看門人”。這樣做時，一次只能進入一個程序以執行上述查詢。其他程序將被阻塞，直到具有 applock 的程序釋放它。並且由於整個過程的這一步只是獲取 RecordID，因此它相當快，並且不會長時間阻塞其他程序。而且，與 CTE 查詢一樣，我們不會阻塞整個表，從而允許對其他行進行其他更新（將它們的狀態設置為“已完成”或“錯誤”）。本質上：

BEGIN TRANSACTION;
EXEC sp_getapplock @Resource = 'GetNextRecordToProcess', @LockMode = 'Exclusive';

  {CTE UPDATE query shown above}

EXEC sp_releaseapplock @Resource = 'GetNextRecordToProcess';
COMMIT TRANSACTION;

應用程序鎖非常好，但應謹慎使用。

最後，您只需要一個儲存過程來處理將狀態設置為“已完成”或“錯誤”。這可以很簡單：

CREATE PROCEDURE ProcessTable_SetProcessStatusID
(
  @RecordID INT,
  @ProcessStatusID TINYINT
)
AS
SET NOCOUNT ON;

UPDATE pt
SET    pt.ProcessStatusID = @ProcessStatusID,
      pt.StatusModifiedDate = GETDATE() -- or GETUTCDATE()
FROM   ProcessTable pt
WHERE  pt.RecordID = @RecordID;

表提示（可在提示 (Transact-SQL) - 表中找到）：

• READPAST（似乎適合這個確切的場景）

指定數據庫引擎不讀取被其他事務鎖定的行。指定 READPAST 時，會跳過行級鎖。也就是說，數據庫引擎跳過行而不是阻塞目前事務，直到釋放鎖…READPAST 主要用於在實現使用 SQL Server 表的工作隊列時減少鎖定爭用。使用 READPAST 的隊列讀取器跳過被其他事務鎖定的隊列條目到下一個可用隊列條目，而不必等到其他事務釋放它們的鎖。

• ROWLOCK（為了安全起見）

指定在通常採用頁鎖或表鎖時採用行鎖。

• 上鎖

指定在事務完成之前獲取並保持更新鎖。UPDLOCK 僅在行級或頁級為讀取操作獲取更新鎖。

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