Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1981

Re: Using sp_getapplock outside a transaction

Show all headers | View raw

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

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

Using sp_getapplock outside a transaction Anton Shepelev <anton.txt@gmail.com> - 2017-11-25 14:56 +0300
  Re: Using sp_getapplock outside a transaction rja.carnegie@gmail.com - 2017-11-25 05:37 -0800
    Re: Using sp_getapplock outside a transaction Anton Shepelev <anton.txt@gmail.com> - 2017-11-25 18:24 +0300
      Re: Using sp_getapplock outside a transaction rja.carnegie@gmail.com - 2017-11-25 08:36 -0800
        Re: Using sp_getapplock outside a transaction Anton Shepelev <anton.txt@gmail.com> - 2017-11-25 21:13 +0300
          Re: Using sp_getapplock outside a transaction rja.carnegie@gmail.com - 2017-11-25 16:21 -0800
      Re: Using sp_getapplock outside a transaction Erland Sommarskog <esquel@sommarskog.se> - 2017-11-25 21:36 +0100
        Re: Using sp_getapplock outside a transaction Anton Shepelev <anton.txt@gmail.com> - 2017-11-26 19:43 +0300
          Re: Using sp_getapplock outside a transaction Erland Sommarskog <esquel@sommarskog.se> - 2017-11-26 19:55 +0100
            Re: Using sp_getapplock outside a transaction Anton Shepelev <anton.txt@g{oogle}mail.com> - 2017-11-27 11:34 +0300
              Re: Using sp_getapplock outside a transaction Erland Sommarskog <esquel@sommarskog.se> - 2017-11-27 11:56 +0100
                Re: Using sp_getapplock outside a transaction rja.carnegie@gmail.com - 2017-11-27 14:23 -0800
                Re: Using sp_getapplock outside a transaction Erland Sommarskog <esquel@sommarskog.se> - 2017-11-28 08:50 +0100
                Re: Using sp_getapplock outside a transaction Anton Shepelev <anton.txt@g{oogle}mail.com> - 2017-11-29 15:11 +0300
                Re: Using sp_getapplock outside a transaction Anton Shepelev <anton.txt@g{oogle}mail.com> - 2017-11-29 15:22 +0300
                Re: Using sp_getapplock outside a transaction Erland Sommarskog <esquel@sommarskog.se> - 2017-11-29 13:44 +0100
                Re: Using sp_getapplock outside a transaction Anton Shepelev <anton.txt@g{oogle}mail.com> - 2017-11-29 18:44 +0300
                Re: Using sp_getapplock outside a transaction Anton Shepelev <anton.txt@g{oogle}mail.com> - 2017-11-29 19:42 +0300
                Re: Using sp_getapplock outside a transaction rja.carnegie@gmail.com - 2017-11-30 13:19 -0800

csiph-web