Path: csiph.com!news.redatomik.org!aioe.org!.POSTED!not-for-mail
From: Anton Shepelev <anton.txt@gmail.com>
Newsgroups: comp.databases.ms-sqlserver
Subject: Re: Using sp_getapplock outside a transaction
Date: Sat, 25 Nov 2017 18:24:11 +0300
Organization: Aioe.org NNTP Server
Lines: 57
Message-ID: <20171125182411.7cc99cc41acef664ea224b1c@gmail.com>
References: <20171125145629.a8f9f75371e03662bf0496dd@gmail.com> <e7eeb2b3-4502-4512-a2c8-a20e908fa080@googlegroups.com>
NNTP-Posting-Host: Bi9VZxU/8bdAeaqLIY+52Q.user.gioia.aioe.org
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
X-Complaints-To: abuse@aioe.org
X-Notice: Filtered by postfilter v. 0.8.2
X-Newsreader: Sylpheed 3.5.0 (GTK+ 2.24.23; i686-pc-mingw32)
Xref: csiph.com comp.databases.ms-sqlserver:1981

rja.carnegie to Anton Shepelev:

> > According to the documentation for sp_getapplock
> > available at
> >
> >   https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-getapplock-transact-sql
> >
> >   lock_owner is nvarchar(32).  The value can  be
> >   Transaction  (the  default)  or Session.  When
> >   the  lock_owner  value  is   Transaction,   by
> >   default or specified explicitly, sp_getapplock
> >   must be executed from within a transaction.
> >
> > When I try, however, to get a lock owned by  the
> > session outside a transaction:
> >
> >   EXEC sp_getapplock 'test', 'Exclusive', @LockOwner = 'Session'
> >   EXEC sp_releaseapplock 'test'
> >
> > it fails with:
> >
> >   The  statement or function must be executed in
> >   the context of a user transaction.
> >
> > Does not this behavor it contradict the documen-
> > tation?
>
> I  don't  know why it isn't working, but it may be
> permissions -- in which case the error message  is
> not a very good message.

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.

-- 
Anton Shepelev