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

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

Re: Using sp_getapplock outside a transaction

Show all headers | View raw

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'

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