sql - 如何避免存储过程被并行执行？

Question

我们有以下情况:

存储过程由中间件调用，并被赋予一个 XML 文件作为参数。该过程然后解析 XML 文件并在循环内将值插入到临时表中。循环后，将临时表中的值插入到物理表中。

问题是，存储过程的运行时间相对较长(大约 5 分钟)。在此期间，很可能正在第二次调用，这将导致两个进程都挂起。

现在我的问题是:如果存储过程已经在运行，我们如何避免再次执行它？

最好的问候

Answer 1

我建议设计您的应用程序层以防止同时运行此进程的多个实例。例如，您可以将逻辑移动到一次处理 1 条消息的队列中。另一种选择是在应用程序级别锁定以防止执行数据库调用。

SQL Server 确实有一个锁定机制来确保代码块不会运行多次:“应用程序锁”。这在概念上类似于 lock C# 中的语句或您可能在其他语言中看到的其他信号量。

要获取应用程序锁，请调用 sp_getapplock .例如:

begin tran
exec sp_getapplock @Resource = 'MyExpensiveProcess', @LockMode = 'Exclusive', @LockOwner = 'Transaction'

如果另一个进程获得了锁，这个调用将被阻塞。如果第二个 RPC 调用尝试运行此进程，并且您希望进程返回一条有用的错误消息，您可以传入值为 0 的 @LockTimeout 并检查返回代码。

例如，如果无法获取锁，下面的代码会引发错误。您的代码可能会返回应用程序解释为“进程已在运行，请稍后重试”的其他内容:

begin tran
declare @result int
exec @result = sp_getapplock @Resource = 'MyExpensiveProcess', @LockMode = 'Exclusive', @LockOwner = 'Transaction', @LockTimeout = 0

if @result < 0
begin
    rollback
    raiserror (N'Could not acquire application lock', 16, 1)
end

要释放锁，调用sp_releaseapplock .

exec sp_releaseapplock @Resource = 'MyExpensiveProcess'