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

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

Re: Using sp_getapplock outside a transaction

Show all headers | View raw

On Saturday, 25 November 2017 15:24:15 UTC, Anton Shepelev  wrote:
> 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

While replying, I noticed that sp_releaseapplock takes @LockOwner 
as well.  So perhaps that needs to be set to 'session' there.

If you don't release the lock explicitly, I gather it terminates 
when your transaction or your session does.  You could use the
Management Stidio button to "break and connect to a new server" 
to test that, I think.  

In one session you will be allowed to lock the same "resource" 
several times, because it is the same session locking it.
All the locks must be unlocked after use, either by default
or explicitly by name.

I don't have a server here to test on, but I see now 
I misunderstood something; the lock will be not on an 
existing object in the database, but on the "resource name"
as an abstract, case-sensitive string.

Accordingly, setting that to nvavrchar(255) as specified, 
or changing it to 'dbo.test', shouldn't matter - but, again,
you could try.

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