使用者在網頁點擊按鈕，難免會不小心點了兩次以上，
如果這是一個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(續)
Determine Which Queries Are Holding Locks
Prevent multiple users from running the same SQL Server stored procedure at the same time
