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: