使用 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. 第二个问题：有没有更好的方法？