SQL Server存储过程临界区：如何确保同一时间仅执行一次？

方法一：使用应用程序锁（sp_getapplock） #

这是SQL Server官方提供的应用级互斥机制，和C#的lock语义最接近，专门用于控制代码块的并发执行。

核心思路：在存储过程开头调用sp_getapplock获取指定名称的锁，执行完业务逻辑后调用sp_releaseapplock释放锁；如果获取锁失败（比如已有实例在执行），直接退出存储过程。

示例代码：

CREATE PROCEDURE dbo.YourProcedure
AS
BEGIN
    SET NOCOUNT ON;

    -- 尝试获取应用锁，锁名称自定义，这里用存储过程名
    DECLARE @LockResult INT;
    EXEC @LockResult = sp_getapplock
        @Resource = 'YourProcedure_Lock',
        @LockMode = 'Exclusive',
        @LockOwner = 'Session',
        @LockTimeout = 0; -- 超时时间设为0，立即返回，不等待

    -- 判断是否获取锁成功
    IF @LockResult NOT IN (0, 1)
    BEGIN
        -- 0=成功获取，1=已持有锁，其他值表示获取失败
        RAISERROR('存储过程已有实例在执行，本次调用终止', 16, 1);
        RETURN;
    END

    BEGIN TRY
        -- 这里写你的业务逻辑
        PRINT '执行存储过程业务逻辑...';
        -- 模拟业务耗时
        WAITFOR DELAY '00:00:10';
    END TRY
    BEGIN CATCH
        -- 捕获异常，确保锁能被释放
        THROW;
    END CATCH
    FINALLY
        -- 释放锁
        EXEC sp_releaseapplock @Resource = 'YourProcedure_Lock', @LockOwner = 'Session';
    END
END

说明：

• @LockTimeout设为0表示如果锁被占用，直接返回失败；如果需要等待一段时间再尝试，可以设置为毫秒值（比如30000表示等待30秒）。
• @LockOwner = 'Session'表示锁绑定到会话，会话结束时会自动释放锁，避免异常导致锁残留。

方法二：使用自定义锁表 #

通过创建一个专门的锁表，记录存储过程的运行状态，利用SQL Server的行级锁来保证原子性。

步骤：

1. 创建锁表：

CREATE TABLE dbo.ProcedureLocks (
    ProcedureName NVARCHAR(128) PRIMARY KEY,
    IsRunning BIT NOT NULL DEFAULT 0,
    LastRunStartTime DATETIME2 NOT NULL DEFAULT GETDATE()
);
-- 初始化存储过程的锁记录
INSERT INTO dbo.ProcedureLocks (ProcedureName) VALUES ('YourProcedure');

2. 存储过程中使用锁表控制并发：

CREATE PROCEDURE dbo.YourProcedure
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON; -- 确保事务失败时自动回滚

    BEGIN TRANSACTION;

    -- 尝试更新锁状态为1，利用行级锁保证只有一个会话能成功
    UPDATE dbo.ProcedureLocks
    SET IsRunning = 1, LastRunStartTime = GETDATE()
    WHERE ProcedureName = 'YourProcedure' AND IsRunning = 0;

    -- 检查是否更新成功，即是否获取到"锁"
    IF @@ROWCOUNT = 0
    BEGIN
        ROLLBACK TRANSACTION;
        RAISERROR('存储过程已有实例在执行，本次调用终止', 16, 1);
        RETURN;
    END

    BEGIN TRY
        -- 业务逻辑
        PRINT '执行存储过程业务逻辑...';
        WAITFOR DELAY '00:00:10';

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        THROW;
    END CATCH
    FINALLY
        -- 无论成功失败，都重置锁状态
        IF @@TRANCOUNT = 0
        BEGIN
            UPDATE dbo.ProcedureLocks
            SET IsRunning = 0
            WHERE ProcedureName = 'YourProcedure';
        END
    END
END

说明：

• SET XACT_ABORT ON确保如果业务逻辑抛出异常，事务会自动回滚，避免锁表状态残留。
• 如果存储过程异常崩溃，会话终止后，事务会自动回滚，锁状态会被重置（因为UPDATE在事务内）。

注意事项 #

• 对于应用程序锁，如果存储过程执行过程中会话意外中断，SQL Server会自动释放锁，无需手动处理。
• 自定义锁表需要考虑异常场景下的状态重置，比如可以定期检查LastRunStartTime，如果超过合理时间仍为1，手动重置状态。

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