Path: csiph.com!news.fcku.it!aioe.org!eternal-september.org!feeder.eternal-september.org!.POSTED!not-for-mail
From: Erland Sommarskog <esquel@sommarskog.se>
Newsgroups: comp.databases.ms-sqlserver
Subject: Re: Using sp_getapplock outside a transaction
Date: Sat, 25 Nov 2017 21:36:06 +0100
Organization: Erland Sommarskog
Lines: 40
Message-ID: <XnsA838DBBEAC219Yazorman@127.0.0.1>
References: <20171125145629.a8f9f75371e03662bf0496dd@gmail.com> <e7eeb2b3-4502-4512-a2c8-a20e908fa080@googlegroups.com> <20171125182411.7cc99cc41acef664ea224b1c@gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: 8bit
Injection-Info: reader02.eternal-september.org; posting-host="8e1316773b3f4e18b1849d6f2288210f"; logging-data="25948"; mail-complaints-to="abuse@eternal-september.org";	posting-account="U2FsdGVkX18c8BY1A/E/7bKltL1XkiJi"
User-Agent: Xnews/2006.08.24 Mime-proxy/2.1.c.0 (Win32)
Cancel-Lock: sha1:WJMHUoT64Y1ulNvER0MSQCH/Dvs=
Xref: csiph.com comp.databases.ms-sqlserver:1986

Anton Shepelev (anton.txt@gmail.com) writes:
> Does not seem to be permissions, for I am testing it
> with full rights as 'sa'.  Furhtermore,  the  return
> value  indicates the lock has been acquired.  When I
> execute:
> 
> DECLARE @getRes INT
> DECLARE @resName VARCHAR(16) = 'test'
> BEGIN TRAN
>   EXEC @getRes = sp_getapplock @resName, 'Exclusive', @LockOwner = 
> 'Session'
>   IF @getRes >= 0
>   BEGIN
>     PRINT 'Lock acquired successfully. Releasing...'
>     EXEC sp_releaseapplock @resName
>   END
>   ELSE PRINT 'Failed to acquire lock'
> ROLLBACK TRAN
> 
> I get the following surprising output:
> 
>   Lock acquired successfully. Releasing...
>   Msg 1223, Level 16, State 1, Procedure xp_userlock, Line 1
>   Cannot release the application lock (Database Principal:
>   'public', Resource: 'test') because it is not currently held.
> 

As RJA says, you need to specify LockOwner to sp_releaseapplock as well.

And you don't need the transaction. This runs successfully on my machine:

DECLARE @getRes INT
DECLARE @resName VARCHAR(16) = 'test'
  EXEC @getRes = sp_getapplock @resName, 'Exclusive', @LockOwner = 'Session'
  IF @getRes >= 0
  BEGIN
    PRINT 'Lock acquired successfully. Releasing...'
    EXEC sp_releaseapplock @resName, 'Session'
  END
  ELSE PRINT 'Failed to acquire lock'