Find out if the stored procedure is already running

Question

Here is my stored procedure which I am running in SQL Server 2012.

ALTER PROCEDURE usp_ProcessCustomers
AS
BEGIN
    IF EXISTS (SELECT 1 FROM RunningProcesses WHERE ProcessId = 1 AND IsRunning = 1)
       RETURN;

    UPDATE RunningProcesses 
    SET IsRunning = 1 
    WHERE ProcessId = 1

-- Do processing here
-- Do processing here

UPDATE RunningProcesses 
    SET IsRunning = 0 
    WHERE ProcessId = 1
END
GO

This stored procedure can be triggered from several places in the app. Even the DBA can trigger the stored procedure using SSMS if needed.

So far so good.

The issue is that if something goes wrong or if the DBA cancels the execution of the stored procedure then the IsRunning value in RunningProcesses is never updated back to 0. So the system always thinks that the stored procedure is running even when its not.

I found the following script on the web which checks if a script is running.

SELECT 
    r.*, t.text 
FROM 
    sys.dm_exec_requests r 
CROSS APPLY
    sys.dm_exec_sql_text(r.sql_handle) t
WHERE 
    r.status IN (N'Suspended', N'Running', N'Runnable', N'Pending')

Would it be a good approach to use the above script to find out whether the stored procedure is already running or not? If its already running then I will exit the stored procedure using the RETURN keyword. If this is not a good idea then what would be the best way to fix this problem.

Answer 1

Use the built-in application locks. SQL Server makes sure the lock is released if the session or server shuts down unexpectedly. Be aware that the lock must be acquired inside a transaction.

ALTER PROCEDURE usp_ProcessCustomers
AS
BEGIN
    BEGIN TRANSACTION
    
    declare @Lock int
    EXEC @Lock = sp_getapplock @Resource = 'ProcessCustomers',
                               @LockMode = 'Exclusive'
    IF (@Lock < 0)  -- already locked by another process
       RETURN;

-- Do processing here
-- Do processing here

    EXEC sp_releaseapplock @Resource = 'ProcessCustomers'
    
    COMMIT TRANSACTION
END