[SQL]使用sp_getapplock來確保某段SQL同時間只能有一個Session執行

原帖地址：http://www.dotblogs.com.tw/rainmaker/archive/2014/04/11/144702.aspx

http://www.mssqltips.com/sqlservertip/3202/prevent-multiple-users-from-running-the-same-sql-server-stored-procedure-at-the-same-time/

今天看到「Prevent multiple users from running the same SQL Server stored procedure at the same time」這篇文章。

他使用「sp_getapplock」來取得鎖定，以確保之後的交易同時間，只能有一個Session執行。

以該文章的範例，開2個視窗(Session)來執行以下的SQL，

1	DECLARE @RC INT

2	Begin tran

3	Exec @RC =sp_getapplock @Resource='MyLock', @LockMode='Exclusive'

4	, @LockOwner='Transaction', @LockTimeout = 15000

5	SELECT @@SPID [session_id], @RC [return code], GETDATE()

6	waitfor delay '00:00:08'

7

commit

執行結果如下，

 

回傳值的說明(MSDN)

>= 0 (成功) 或 < 0 (失敗)

0:同步授與鎖定成功。
1:在等待其他不相容的鎖定釋出之後，授與鎖定成功。
-1:鎖定要求逾時。
-2:已取消鎖定要求。
-3:已將鎖定要求選為死結犧牲者。
-999:表示參數驗證或其他呼叫錯誤。

 

那如果將timeout的時間設成 5 秒，另一個Session的回傳值就會變成 -1 如下，

 

以這個需求而言，@LockOwner 使用 Transaction 比較適合。

如果 @LockOwner 是使用 Transaction ，則表示如果該交易 commit 或是 rollback ，就會釋放這個鎖定。

如果 @LockOwner 是使用 Session ，則表示要明確呼叫 sp_releaseapplock 或是 Session 關閉連線才會釋放這個鎖定。

詳細請參考「Prevent multiple users from running the same SQL Server stored procedure at the same time」這篇文章。