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

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

Re: Using sp_getapplock outside a transaction

Show all headers | View raw

On Saturday, 25 November 2017 11:56:34 UTC, Anton Shepelev  wrote:
> Hello, all
> 
> 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 exe-
>   cuted from within a transaction.
> 
> When I try, however, to get a lock owned by the ses-
> sion 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  documenta-
> tion?
> 
> -- 
> Anton Shepelev

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.

<http://technico.qnownow.com/how-to-prevent-stored-procedure-running-concurrently-in-sql-server/>

says,
"to be  able to call sp_getapplock a user calling the stored procedure must meet one of these conditions:
is dbo
is in the db_owner role
is the DB Principal ID (e.g. guest)
is in the DB Principal ID role (e.g. public)"

This is for an example where a stored procedure runs exclusively,
by the procedure taking a session lock on the procedure object 
itself.  You can try that example.

I've only looked briefly in this area - there may be a server setting,
or a difference between SQL Server versions or editions, that prevents
the session lock from being made.  Again, this does not match well
what the error message tells you.

I don't remember how legitimate it is to call a procedure, 
as you did, with parameter name @LockOwner used but not others
i.e. the first two.  I put in all parameter names I'm using,
or none.  It wouldn't do harm to try that.

I'm not sure who is still reading this group; there is more
activity in this web site <https://dba.stackexchange.com>
for "database administrators" - but I haven't opened it for 
a while.

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