sql - 当存储过程没有输入参数时 sp_getapplock 正在工作

Question

我一直在使用它sp_getapplock来锁定资源的会话。

这是存储过程：

CREATE PROCEDURE [dbo].[system-LockProcedure] 
    @procname VARCHAR(200)
AS 
--BEGIN TRAN
BEGIN
    DECLARE @lockreturn INT,
            @lockresource VARCHAR(200)   -- sysname

    SELECT 
        @lockresource = @procname    

    -- The below line will try to acquire an exclusive lock on the PROC for the session, if the proc is already in execution the @lockreturn value will be > 0
    EXEC @lockreturn = sp_getapplock @lockresource, 
                                     @LockMode = 'Exclusive', 
                                     @LockOwner = 'Session', 
                                     @LockTimeout = 100

    PRINT @lockreturn 

    IF @lockreturn <> 0
    BEGIN
        RAISERROR ('Another instance of the procedure is already running', 16, 1)
        RETURN
    END

    -- The Code to be executed goes here. All the core logic of the proc goes here..

    PRINT 'Procedure execution started for user: ' + CAST(CURRENT_USER AS NVARCHAR(20)) + ' for session: ' + CAST(@@SPID AS NVARCHAR(10))

    -- This is just to make the system wait for 30 seconds and make sure if there is any concurrent execution triggered will fail
    EXEC @lockresource

    PRINT 'Procedure execution ended for user: ' +  CAST(CURRENT_USER AS NVARCHAR(20)) + ' for session: ' + CAST(@@SPID AS NVARCHAR(10))

    PRINT @lockreturn

    -- This is to release the lock once the stored procedure has run completely
    EXEC sp_releaseapplock @lockresource , 'Session'

    -- END TRY
END

我们也可以在您的本地执行此程序进行测试。

当我在一个会话中执行该过程时，如果有人想要执行这个存储过程，那么它会显示它已经在另一个会话中打开的消息。

使用资源名称执行此过程时，该资源名称也是过程对象

EXEC dbo.[system-LockProcedure]'dbo.usp_Test1'

它工作正常

EXEC dbo.[system-LockProcedure]'dbo.usp_Test2'

有输入参数并且它要求输入参数

错误：

消息 201，级别 16，状态 4，过程 USP_Test2，第 23 行
过程或函数“USP_Test2”需要未提供的参数“@ThreadID”。

标签： sqlsql-servertsql