Calling sp_getapplock and sp_releaseapplock in context of a linked server

Question

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

Charlieface · Accepted Answer · 2025-10-26 02:24:12Z

You need to start a distibuted transaction first, and commit it at the end. This will automatically enlist any remote servers accessed in between. You need to enable networked DTC on both hosts, and possibly fix a single port to allow DTC through a firewall.

You also need to specify the @LockOwner as Transaction

SET XACT_ABORT ON;    -- essential for correct error handling

BEGIN DISTRIBUTED TRAN;

DECLARE @ReturnCode int;

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

SELECT @ReturnCode;

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

SELECT @ReturnCode;

COMMIT;

You could probably do this without dynamic SQL, by calling the remote procedure using four-part naming.

SET XACT_ABORT ON;    -- essential for correct error handling

BEGIN DISTRIBUTED TRAN;

DECLARE @ReturnCode int;

EXECUTE @ReturnCode = [server-2].master.sys.sp_getapplock
        @Resource = N'Test'
        ,@LockMode = 'Exclusive'
        ,@LockOwner = 'Transaction'
        ,@LockTimeout = 0
        ,@DbPrincipal = 'dbo';

SELECT @ReturnCode;

EXECUTE @ReturnCode = [server-2].master.sys.sp_releaseapplock
        @Resource = N'Test'
        ,@LockOwner = 'Transaction'
        ,@DbPrincipal = 'dbo';

SELECT @ReturnCode;

COMMIT;

Thank you! It required to configure MSDTC on both hosts, but I got it to work in both directions! — Oleksandr
– Oleksandr, Commented Oct 25 at 21:26

Stack Exchange Network

Calling sp_getapplock and sp_releaseapplock in context of a linked server

1 Answer 1

Your Answer

Hot Network Questions

Calling sp_getapplock and sp_releaseapplock in context of a linked server

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions