如何让 SQL Server 2019 假装存储过程正在进行中? [英] How can I make SQL Server 2019 pretend that a stored proc is in progress?

问题描述

我有一个存储过程，可以从我的实时生产数据库刷新我的报告数据库中的几个表.

I have a stored procedure that refreshes several tables in my reporting database from my live production database.

我跑:

EXEC Reporting.dbo.RefreshTemps

这个存储过程非常简单，它所做的只是截断几个表，然后运行 ​​INSERT INTO 来获取刷新的数据快照.大约需要 10 分钟才能完成，因为它们是非常厚实的桌子.

This stored proc is super simple, all it does is truncate several tables and then runs an INSERT INTO them to get a refreshed snapshot of data. It takes around 10 minutes to complete as they're pretty chunky tables.

但是，许多人、流程或其他程序可能希望在白天使用此程序.在用户 A 的运行完成之前，用户 A 可能会启动 proc 运行，然后用户 B 启动相同的 proc 运行，这是完全合理的.

However, many people, processes or other procedures might want to use this procedure during the day. It's entirely plausible that User A may start the proc running then User B starts the same proc running before User A's run has completed.

这意味着一旦用户 A 的 proc 完成，用户 B 的 proc 将处于截断和刷新相同表的过程中.这会很糟糕.

This would mean once User A's proc completed, user B's proc would be in the process of truncating and refreshing the same tables. Which would be bad.

理想情况下，SQL 不会运行用户 B 的 proc，等待用户 A 的运行完成并且用户 A 和用户 B 将同时暴露给更新的表.

Ideally, SQL would not run User B's proc, wait for User A's run to complete and both User A and User B would be exposed to the updated tables at the same time.

有什么办法可以让 SQL 误以为用户 B 的 proc 运行正常，但它实际上在等待用户 A 的运行完成吗?

Is there a way I can fool SQL into thinking that User B's proc is running normally, but what it's actually doing is waiting for User A's run to complete?

推荐答案

这里最简单的方法是使用 SQL Server 代理作业.您可以使用 sp_start_job，并且任何时候都只能运行一个作业副本.

The simplest thing to do here is to use a SQL Server Agent job. You can start a job with sp_start_job, and only one copy of a job can run at any time.

就像 Dan Guzman 建议你也可以使用 sp_getapplock.但是您可能想要使用会话锁，否则您的长时间运行的作业将不得不在事务中运行.会话锁可能很棘手.很容易得到一个阻塞的进程.无论如何，它可能看起来像这样:

Like Dan Guzman suggested you could also use sp_getapplock. But you'd probably want to use session locks, otherwise your long-running job would have to run in a transaction. And session locks can be tricky. It's easy to get a blocked process. Anyway it might look something like this:

create or alter procedure ThereCanBeOnlyOne @sql nvarchar(max)
as
begin

  --hash the sql batch to generate the app lock name
  declare @lockname nvarchar(255) =  concat('lock_',convert(nvarchar(255), hashbytes('MD5', @sql) , 1));
  print @lockname

  declare @result int
  --request an applock with an immediate timeout
  exec @result = sp_getapplock @Resource=@lockname, @LockMode='Exclusive',@LockOwner='Session',@LockTimeout=0
  if @result = -1 --lock timeout procedure is already running
  begin
     --wait for other session to finish and return
     exec sp_getapplock @Resource=@lockname, @LockMode='Exclusive',@LockOwner='Session';
     exec sp_releaseapplock @Resource=@lockname, @LockOwner='Session';
     print 'batch completed in another session';
     return 0;
  end
  begin try
    --actually run the batch
    exec (@sql);
    exec sp_releaseapplock @Resource=@lockname, @lockOwner='Session';
    print 'batch completed in this session';
    return 0;
  end try
  begin catch
       if (APPLOCK_MODE('public', @lockname, 'Session') = 'Exclusive')
        exec sp_releaseapplock @Resource=@lockname, @lockOwner='Session';
       throw;
  end catch


end

这篇关于如何让 SQL Server 2019 假装存储过程正在进行中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！