如何让SQL Server 2019模拟存储过程执行中，避免并发调用冲突？

这个场景我太熟了——批量刷新报表表的存储过程最怕并发调用，截断+插入的组合在并发下肯定出乱子。要实现“后续调用自动等待前一个完成，且最终都能拿到最新数据”的效果，用SQL Server的**应用程序锁（Application Lock）**是最直接的方案，完全满足你的需求。

具体实现思路 #

我们要给RefreshTemps存储过程加一个全局的排他锁：当第一个会话（用户A）执行时，先获取这个锁，执行完刷新逻辑后释放锁；后续会话（用户B）调用时，会自动等待锁释放，直到前一个执行完成才会开始自己的刷新流程。这样就不会出现同时截断、插入的冲突，所有调用最终都会拿到一致的最新数据。

修改后的存储过程代码 #

ALTER PROCEDURE Reporting.dbo.RefreshTemps
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @LockResult INT;

    -- 获取应用程序排他锁，锁名称要唯一，这里用存储过程名确保不会和其他锁冲突
    -- 超时时间设为-1表示无限等待（直到锁被释放）
    EXEC @LockResult = sp_getapplock
        @Resource = 'RefreshTemps_Lock',
        @LockMode = 'Exclusive',
        @LockOwner = 'Session',
        @LockTimeout = -1;

    -- 检查是否成功获取锁（正常情况下会等待到获取为止，这里做个防御性检查）
    IF @LockResult < 0
    BEGIN
        RAISERROR('无法获取刷新锁，请稍后重试', 16, 1);
        RETURN;
    END

    BEGIN TRY
        -- 你的原有刷新逻辑：截断表+INSERT INTO
        TRUNCATE TABLE Reporting.dbo.TempTable1;
        INSERT INTO Reporting.dbo.TempTable1
        SELECT * FROM Production.dbo.SourceTable1;

        TRUNCATE TABLE Reporting.dbo.TempTable2;
        INSERT INTO Reporting.dbo.TempTable2
        SELECT * FROM Production.dbo.SourceTable2;

        -- 其他表的刷新逻辑...

    END TRY
    BEGIN CATCH
        -- 捕获异常，抛出错误
        THROW;
    END CATCH
    FINALLY
        -- 无论成功还是失败，都要释放锁，避免锁一直占用
        EXEC sp_releaseapplock
            @Resource = 'RefreshTemps_Lock',
            @LockOwner = 'Session';
    END
END

关键细节解释 #

• 锁的唯一性：@Resource参数用了RefreshTemps_Lock，确保只有这个存储过程会使用这个锁，不会和其他业务逻辑的锁冲突。
• 排他锁模式：@LockMode = 'Exclusive'意味着同一时间只有一个会话能持有这个锁，后续调用必须等待。
• 无限等待：@LockTimeout = -1表示后续调用会一直等待直到锁释放，符合你“不执行B的调用，而是等待A完成”的需求（用户看到的就是存储过程在“运行中”，实际在排队）。
• 锁的释放：用FINALLY块确保无论存储过程成功还是出错，锁都会被释放，避免出现死锁或者锁一直占用的情况。

这样修改后，不管有多少人或程序调用EXEC Reporting.dbo.RefreshTemps，都会按顺序排队执行，完全解决并发刷新导致的数据异常问题，所有调用最终都会拿到更新后的一致数据。

内容的提问来源于stack exchange，提问作者Geoff Griswald