[SQL Server]how to prevent multiple user run SP at the same time

使用者在網頁點擊按鈕,難免會不小心點了兩次以上,

如果這是一個heavy query(like report),一來浪費SQL Server資源,假如該SP又包含一些資料邏輯處理更新,

那麼也有可能發生資料不一致的情況,我們來看看如何從SQL Server下手來預防這狀況。

以前我透過nowait方式,來告知前端使用者該作業目前有其他人正在處理,

這方式可以有比較好的使用者經驗(使用者無需等待),

但同事告知,這是一個背景job(沒有UI),同事希望從SQL Server下手來達到排隊(queue)效果,

避免多位使用者同一時間執行某一SP,由於我們正式環境concurrent user很高,

且我們會先撈出要處理的一批資料,並update 這些資料的狀態為處理中, 

也因為這樣,我們要避免concurrency所造成的資料問題和deadlock issue,

如果再不變更交易隔離層級+uplock的前提下,那麼透過sp_getapplock也是不錯的做法,

下面簡單驗證一下。

 

當有多條session同時執行,不管如何一定會有一條先搶到資源,

我希望,另一條session等先前session完成後後才能接續執行(避免同時執行核心商業邏輯),

declare @RC INT 
begin tran
waitfor delay '00:00:05' 
exec @RC =sp_getapplock @Resource='MyLock', @LockMode='Exclusive'
            , @LockOwner='Transaction', @LockTimeout = 10000
select @@SPID as [session_id], @RC as [return code], GETDATE() as 'begin time'
waitfor delay '00:00:05'  
select GETDATE() as 'end time'           
commit

你可以看到透過sp_getapplock的效果,第一條session執行完後,第二條session才能接續執行。

另外我建議lockowner=transaction在開發上比較簡易,如改用session的話可能會有無法釋放資源的情況,

畢竟.net connection pool預設大家都會使用,下面來看一下包在SP的測試。

alter proc dbo.critical_worker  @wait_duration varchar(30) = '00:01:00'
/* Performs a task in a critical section of code that can only be run
   by one session at a time. The task is simulated by a WAIT  */
as
declare @rc int = 0 -- return code
      , @msg varchar(1000)
set @msg = convert(varchar,getdate(), 114) + ' critical_worker starting'
raiserror (@msg, 0, 1) with nowait 
set xact_abort on
begin try
 begin tran
 set @msg= convert(varchar,getdate(), 114) + ' requesting lock'
 raiserror (@msg, 0, 1) with nowait
 Exec @rc = sp_getapplock @Resource='CriticalWorker' -- the resource to be locked
         , @LockMode='Exclusive'  -- Type of lock
         , @LockOwner='Transaction' -- Transaction or Session
         , @LockTimeout = 10000 -- timeout in milliseconds(depend on your business need), 10 seconds
                            
 set @msg= convert(varchar,getdate(), 114) + ' sp_getapplock returned ' + convert(varchar(30), @rc) + ' -- '
      + case when @rc < 0 then 'Could not obtain the lock'  else 'Lock obtained'  end
 raiserror (@msg, 0, 1) with nowait
  
 if @rc >= 0 
  begin
  set @msg= convert(varchar,getdate(), 114) + ' got lock starting critical work '
  raiserror (@msg, 0, 1) with nowait
  
  waitfor delay @wait_duration -- Critical Work simulated by waiting
  
  commit tran -- release the lock
  set @msg= convert(varchar,getdate(), 114) + ' work complete released lock' 
  raiserror (@msg, 0, 1) with nowait
  end 
 else begin
        
  rollback tran -- release the lock
   set @rc = 50000
 end
end try
begin catch
 
 set @msg = 'ERROR: ' + ERROR_MESSAGE() + ' at ' 
            + coalesce(ERROR_PROCEDURE(), '')
            + coalesce (' line:' + convert(varchar(30), ERROR_LINE()), '')
            
 raiserror (@msg, 0, 1) with nowait -- ensure the message gets out                                 
 if XACT_STATE()=-1 rollback tran
 raiserror (@msg, 16, 1)
 end catch
 return @rc
 go

 

print 'sessionid='+ cast(@@spid as varchar(10))
exec dbo.critical_worker '00:00:10'

可以看到session51釋放resource後,session68看到資源被釋放後,馬上緊接執行該作業。

但也要注意sp_getapplock所帶來的額外問題,GL :)

 

參考

[SQL SERVER]小心使用With NoLock(續)

sp_releasapplock

sp_getapplock (Transact-SQL)

Determine Which Queries Are Holding Locks

Prevent multiple users from running the same SQL Server stored procedure at the same time

Simple query to check the recent blocking history