Sql-server – How to limit a SQL stored procedure to be run by one person at a time

sql serversql-server-2012stored-procedurest-sql

I have a stored procedure that basically selects values from one table and inserts them to another, a sort of archiving. I want to avoid multiple people from doing that at the same time.

While this procedure is running, I don't want anyone else to be able to start it, however I don't want serialization, the other person to run the procedure after I am done with it.

What I want is for the other person trying to start it to get an error, while I am running the procedure.

I've tried with using sp_getapplock, however I can't manage to completely stop the person from running the procedure.

I also tried finding the procedure with sys.dm_exec_requests and blocking the procedure, while this does work, i think it's not optimal because on some servers I don't have the permissions to run sys.dm_exec_sql_text(sql_handle).

What is the best way for me to do this?

Best Answer

To add to @Tibor-Karaszi's answer, setting a lock timeout doesn't actually produce an error (I've submitted a PR against the docs). sp_getapplock just returns -1, so you have to check the return value. So like this:

create or alter procedure there_can_be_only_one 
begin transaction

  declare @rv int
  exec @rv = sp_getapplock 'only_one','exclusive','Transaction',0
  if @rv < 0
      throw 50001, 'There is already an instance of this procedure running.', 10

  --do stuff
  waitfor delay '00:00:20'

commit transaction