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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With