如何在Azure SQL存储过程中实现互斥避免重复数据准备？

针对你的需求，用sp_getapplock实现基于唯一参数的会话级互斥是最优方案——只有参数相同的请求才会排队等待，确保同参数的数据准备操作仅执行一次，完美解决并行请求重复计算、锁冲突的问题。

以下是完整的存储过程实现代码：

CREATE OR ALTER PROCEDURE usp_prepare_data
    @param NVARCHAR(50) -- 唯一短字符串参数
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @LockResult INT;
    DECLARE @LockName NVARCHAR(255) = N'DataPrepareLock_' + @param; -- 按参数生成唯一锁名

    -- 1. 获取基于参数的会话级互斥锁
    EXEC @LockResult = sp_getapplock
        @Resource = @LockName,
        @LockMode = 'Exclusive',
        @LockOwner = 'Session',
        @LockTimeout = 65000; -- 超时设为65秒，覆盖60秒的处理耗时

    IF @LockResult < 0
    BEGIN
        -- 获取锁失败（超时或其他错误），返回错误状态
        RAISERROR('无法获取数据准备锁，请稍后重试', 16, 1);
        RETURN;
    END

    BEGIN TRY
        -- 2. 二次检查数据是否已就绪（关键：等待锁期间可能已有请求完成准备）
        IF EXISTS (
            SELECT 1 
            FROM YourTargetTable 
            WHERE ParamColumn = @param 
              AND ValidTo > GETDATE() -- 替换为你的就绪条件
        )
        BEGIN
            -- 数据已就绪，直接返回
            RETURN;
        END

        -- 3. 执行数据准备操作
        -- 删除当前参数的过期数据
        DELETE FROM YourTargetTable
        WHERE ParamColumn = @param
          AND ValidTo <= GETDATE();

        -- 插入新数据（源表用NOLOCK读取）
        INSERT INTO YourTargetTable (ParamColumn, DataColumn, ValidTo)
        SELECT 
            @param,
            s.Data,
            DATEADD(HOUR, 24, GETDATE()) -- 替换为你的有效期规则
        FROM YourSourceTable s WITH (NOLOCK)
        WHERE s.Param = @param;

    END TRY
    BEGIN CATCH
        -- 捕获异常并抛出
        THROW;
    END CATCH
    FINALLY
        -- 4. 无论操作成功/失败，都释放锁
        EXEC sp_releaseapplock
            @Resource = @LockName,
            @LockOwner = 'Session';
    END
END

关键逻辑说明 #

• 参数级互斥：锁名由固定前缀+传入参数拼接而成，确保只有同参数的请求会互相阻塞，不同参数的请求完全独立，不影响并行效率。
• 二次检查机制：获取锁后必须重新校验数据状态——因为等待锁的期间，先拿到锁的请求可能已经完成了数据准备，这时候直接返回即可，避免重复执行60秒的耗时操作。
• 锁的安全释放：用FINALLY块保证锁一定会被释放，即使数据操作抛出异常或事务回滚，也不会出现锁残留导致的死锁问题。
• 性能优化：源表读取用WITH (NOLOCK)减少读锁影响，删除/插入仅针对当前参数的数据，不锁定整个目标表，符合你的性能要求。

注意事项 #

• 锁超时时间建议设为数据准备最长耗时+5秒，避免合法请求因超时失败；
• sp_getapplock的锁作用域是会话，若存储过程有嵌套调用，无需额外处理锁的传递；
• 若需要支持跨事务的锁，可将@LockOwner改为Transaction，但会话级锁更适合当前场景（无需绑定事务生命周期）。

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