了解存储过程是否已在运行

Question

这是我在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

可以从应用中的多个位置触发此存储过程。如果需要，甚至 DBA 也可以使用 SSMS 触发存储过程。

目前为止，一切都好。

问题是，如果出现问题或 DBA 取消了存储过程的执行，则RunningProcesses中的IsRunning值永远不会更新回 0。因此，系统始终认为存储过程正在运行，即使它没有运行。

我在网络上找到了以下脚本，该脚本检查脚本是否正在运行。

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')

使用上述脚本来确定存储过程是否已在运行是否是一种好方法？如果它已经在运行，那么我将使用RETURN关键字退出存储过程。如果这不是一个好主意，那么解决此问题的最佳方法是什么。

Answer 1

使用内置的应用程序锁。SQL Server 确保在会话或服务器意外关闭时释放锁。请注意，必须在事务中获取锁。

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