使用 sp_getapplock 實現隊列。這是正確的嗎？有沒有更好的辦法？

我一直在閱讀 Paul White 關於SQL Server Isolation Levels的一系列文章，並遇到了一個片語：

為了強調這一點，用 T-SQL 編寫的偽約束必須正確執行，無論可能發生什麼並發修改。應用程序開發人員可能會使用 lock 語句來保護類似的敏感操作。T-SQL 程序員最接近風險儲存過程和触發程式碼的工具是相對很少使用的sp_getapplock系統儲存過程。這並不是說它是唯一的，甚至是首選的選擇，只是它存在並且在某些情況下可能是正確的選擇。

我正在使用sp_getapplock，這讓我想知道我是否正確使用它，或者有更好的方法來獲得預期的效果。

我有一個 C++ 應用程序，可以 24/7 循環處理所謂的“建構伺服器”。有一張表格，其中列出了這些建築伺服器（大約 200 行）。可以隨時添加新行，但並不經常發生。行永遠不會被刪除，但它們可以被標記為非活動狀態。處理一個伺服器可能需要幾秒到幾十分鐘，每個伺服器都不一樣，有的“小”，有的“大”。一旦伺服器被處理，應用程序必須等待至少 20 分鐘才能再次處理它（伺服器不應該被輪詢太頻繁）。應用程序啟動了 10 個並行執行處理的執行緒，但我必須保證沒有兩個執行緒嘗試同時處理同一個伺服器. 兩台不同的伺服器可以而且應該同時處理，但每台伺服器的處理頻率不得超過 20 分鐘一次。

這是一個表的定義：

CREATE TABLE [dbo].[PortalBuildingServers](
   [InternalIP] [varchar](64) NOT NULL,
   [LastCheckStarted] [datetime] NOT NULL,
   [LastCheckCompleted] [datetime] NOT NULL,
   [IsActiveAndNotDisabled] [bit] NOT NULL,
   [MaxBSMonitoringEventLogItemID] [bigint] NOT NULL,
CONSTRAINT [PK_PortalBuildingServers] PRIMARY KEY CLUSTERED 
(
   [InternalIP] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_LastCheckCompleted] ON [dbo].[PortalBuildingServers]
(
   [LastCheckCompleted] ASC
)
INCLUDE 
(
   [LastCheckStarted],
   [IsActiveAndNotDisabled],
   [MaxBSMonitoringEventLogItemID]
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

應用程序中工作執行緒的主循環如下所示：

for(;;)
{
   // Choose building server for checking
   std::vector<SBuildingServer> vecBS = GetNextBSToCheck();
   if (vecBS.size() == 1)
   {
       // do the check and don't go to sleep afterwards
       SBuildingServer & bs = vecBS[0];
       DoCheck(bs);
       SetCheckComplete(bs);
   }
   else
   {
       // Sleep for a while
       ...
   }
}

這裡有兩個函式GetNextBSToCheck，SetCheckComplete分別呼叫對應的儲存過程。

GetNextBSToCheck返回 0 或 1 行，其中包含接下來應處理的伺服器的詳細資訊。它是一個很長時間沒有被處理的伺服器。如果這個“最舊的”伺服器在不到 20 分鐘前被處理，則不會返回任何行，執行緒將等待一分鐘。

SetCheckComplete設置處理完成的時間，因此可以在 20 分鐘後再次選擇此伺服器進行處理。

最後是儲存過程的程式碼：

GetNextToCheck:

CREATE PROCEDURE [dbo].[GetNextToCheck]
AS
BEGIN
   SET NOCOUNT ON;

   BEGIN TRANSACTION;
   BEGIN TRY
       DECLARE @VarInternalIP varchar(64) = NULL;
       DECLARE @VarMaxBSMonitoringEventLogItemID bigint = NULL;

       DECLARE @VarLockResult int;
       EXEC @VarLockResult = sp_getapplock
           @Resource = 'PortalBSChecking_app_lock',
           @LockMode = 'Exclusive',
           @LockOwner = 'Transaction',
           @LockTimeout = 60000,
           @DbPrincipal = 'public';

       IF @VarLockResult >= 0
       BEGIN
           -- Acquired the lock
           -- Find BS that wasn't checked for the longest period
           SELECT TOP 1
               @VarInternalIP = InternalIP
               ,@VarMaxBSMonitoringEventLogItemID = MaxBSMonitoringEventLogItemID
           FROM
               dbo.PortalBuildingServers
           WHERE
               LastCheckStarted <= LastCheckCompleted
               -- this BS is not being checked right now
               AND LastCheckCompleted < DATEADD(minute, -20, GETDATE())
               -- last check was done more than 20 minutes ago
               AND IsActiveAndNotDisabled = 1
           ORDER BY LastCheckCompleted
           ;

           -- Start checking the found BS
           UPDATE dbo.PortalBuildingServers
           SET LastCheckStarted = GETDATE()
           WHERE InternalIP = @VarInternalIP;
           -- There is no need to explicitly verify if we found anything.
           -- If @VarInternalIP is null, no rows will be updated
       END;

       -- Return found BS, 
       -- or no rows if nothing was found, or failed to acquire the lock
       SELECT
           @VarInternalIP AS InternalIP
           ,@VarMaxBSMonitoringEventLogItemID AS MaxBSMonitoringEventLogItemID
       WHERE
           @VarInternalIP IS NOT NULL
           AND @VarMaxBSMonitoringEventLogItemID IS NOT NULL
       ;

       COMMIT TRANSACTION;
   END TRY
   BEGIN CATCH
       ROLLBACK TRANSACTION;
   END CATCH;

END

SetCheckComplete:

CREATE PROCEDURE [dbo].[SetCheckComplete]
   @ParamInternalIP varchar(64)
AS
BEGIN
   SET NOCOUNT ON;

   BEGIN TRANSACTION;
   BEGIN TRY

       DECLARE @VarLockResult int;
       EXEC @VarLockResult = sp_getapplock
           @Resource = 'PortalBSChecking_app_lock',
           @LockMode = 'Exclusive',
           @LockOwner = 'Transaction',
           @LockTimeout = 60000,
           @DbPrincipal = 'public';

       IF @VarLockResult >= 0
       BEGIN
           -- Acquired the lock
           -- Completed checking the given BS
           UPDATE dbo.PortalBuildingServers
           SET LastCheckCompleted = GETDATE()
           WHERE InternalIP = @ParamInternalIP;
       END;

       COMMIT TRANSACTION;
   END TRY
   BEGIN CATCH
       ROLLBACK TRANSACTION;
   END CATCH;

END

如您所見，我曾經sp_getapplock保證在任何給定時間只有這兩個儲存過程的一個實例在執行。我想我需要sp_getapplock在這兩個過程中使用，因為選擇“最舊”伺服器的查詢使用LastCheckCompleted時間，該時間由SetCheckComplete.

我認為這段程式碼確實保證沒有兩個執行緒同時嘗試處理同一個伺服器，但如果您能指出這段程式碼和整體方法的任何問題，我將不勝感激。那麼，第一個問題：這種方法正確嗎？

另外，我想知道不使用 sp_getapplock. 第二個問題：有沒有更好的方法？

這種方法正確嗎？

是的。它滿足問題中所述的所有目標。

過程中的註釋以解釋策略並註意相關過程名稱可能有助於其他人將來的維護。

有沒有更好的辦法？

在我看來，沒有。

獲取單個鎖是一個非常快的操作，並且邏輯非常清晰。我不清楚在第二個過程中獲取鎖是多餘的，但即使是，省略它你真正獲得了什麼？您實施的簡單性和安全性吸引了我。

替代方案要復雜得多，可能會讓您想知道您是否真正涵蓋了所有情況，或者將來內部引擎細節是否可能會發生變化，從而打破（可能是微妙和未說明的）假設。

---

如果您需要更傳統的隊列實現，以下參考非常有用：

Remus Rusanu使用表作為隊列

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