How can I make SQL Server 2019 pretend that a stored proc is in progress?

Question

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

I run:

EXEC Reporting.dbo.RefreshTemps

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.

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.

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.

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.

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?

Answer 1

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.

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