I am designing a stored procedure in SQL server that has to manipulate data across multiple servers, let's call them server-1 and server-2. Practically, it's always a local instance and a remote one with the remote accessed via a linked server. The problem is that the roles of the instances can flip, I mean, one can run this SP on server-1 locally and then server-2 acts as a remote one, and, one can run the SP on server-2 locally and then server-1 becomes a remote one. During data manipulation, I want to place exclusive lock on resources so no one else runs the SP same time on both instances. For that, I planned to use sp_getapplock and sp_releaseapplock SPs. On local instance it is not a problem. But, I observe a weird behavior w.r.t remote execution. The following sample throws Cannot release the application lock (Database Principal: 'dbo', Resource: 'Test') because it is not currently held. error when calling sp_releaseapplock. Let's assume I am logged in locally to server-1. The following code works just fine, the lock is placed and released successfully:

DECLARE @ReturnCode [int];

USE [master];
EXEC @ReturnCode = sp_getapplock
    @Resource = N'Test'
    ,@LockMode = 'Exclusive'
    ,@LockOwner = 'Session'
    ,@LockTimeout = 0
    ,@DbPrincipal = 'dbo';

SELECT @ReturnCode;

USE [master];
EXEC @ReturnCode = sp_releaseapplock
    @Resource = N'Test'
    ,@LockOwner = 'Session'
    ,@DbPrincipal = 'dbo';

SELECT @ReturnCode;

But, if I turn the code into remote via EXECUTE(...) AT..., it throws the error I mentioned:

DECLARE @ReturnCode [int];

EXECUTE ('
    USE [master];
    EXEC ? = sp_getapplock
        @Resource = N''Test''
        ,@LockMode = ''Exclusive''
        ,@LockOwner = ''Session''
        ,@LockTimeout = 0
        ,@DbPrincipal = ''dbo'';
    '
    ,@ReturnCode OUTPUT
) AT [server-2];

SELECT @ReturnCode;

EXECUTE('
    USE [master];
    EXEC ? = sp_releaseapplock
        @Resource = N''Test''
        ,@LockOwner = ''Session''
        ,@DbPrincipal = ''dbo'';
    '
    ,@ReturnCode OUTPUT
) AT [server-2];

SELECT @ReturnCode;

Why is it so? Is there any other way to achieve exclusive locking across multiple instances?

PS. I tried to google it, but there is no mention about remote calls for sp_getapplock or sp_releaseapplock, even in the official documentation

I am designing a stored procedure in SQL server that has to manipulate data across multiple servers, let's call them server-1 and server-2. Practically, it's always a local instance and a remote one with the remote accessed via a linked server. The problem is that the roles of the instances can flip, I mean, one can run this SP on server-1 locally and then server-2 acts as a remote one, and, one can run the SP on server-2 locally and then server-1 becomes a remote one. During data manipulation, I want to place exclusive lock on resources so no one else runs the SP same time on both instances. For that, I planned to use sp_getapplock and sp_releaseapplock SPs. On local instance it is not a problem. But, I observe a weird behavior w.r.t remote execution. The example below throws Cannot release the application lock (Database Principal: 'dbo', Resource: 'Test') because it is not currently held. error when calling sp_releaseapplock on the remote server. For example, let's assume I am logged in locally to server-1. The following code works just fine, the lock is placed and released successfully:

DECLARE @ReturnCode [int];

USE [master];
EXEC @ReturnCode = sp_getapplock
    @Resource = N'Test'
    ,@LockMode = 'Exclusive'
    ,@LockOwner = 'Session'
    ,@LockTimeout = 0
    ,@DbPrincipal = 'dbo';

SELECT @ReturnCode;

USE [master];
EXEC @ReturnCode = sp_releaseapplock
    @Resource = N'Test'
    ,@LockOwner = 'Session'
    ,@DbPrincipal = 'dbo';

SELECT @ReturnCode;

But, if I turn the code into remote via EXECUTE(...) AT..., it throws the error I mentioned:

DECLARE @ReturnCode [int];

EXECUTE ('
    USE [master];
    EXEC ? = sp_getapplock
        @Resource = N''Test''
        ,@LockMode = ''Exclusive''
        ,@LockOwner = ''Session''
        ,@LockTimeout = 0
        ,@DbPrincipal = ''dbo'';
    '
    ,@ReturnCode OUTPUT
) AT [server-2];

SELECT @ReturnCode;

EXECUTE('
    USE [master];
    EXEC ? = sp_releaseapplock
        @Resource = N''Test''
        ,@LockOwner = ''Session''
        ,@DbPrincipal = ''dbo'';
    '
    ,@ReturnCode OUTPUT
) AT [server-2];

SELECT @ReturnCode;

Why is it so? Is there any other way to achieve exclusive locking across multiple instances?

PS. I tried to google it, but there is no mention about remote calls for sp_getapplock or sp_releaseapplock, even in the official documentation