デッドロックについて説明してみる - その 6 ( sp_getapplock と sp_releaseapplock でデッドロックを回避してみる ) -
今年のダイヤモンド修行は例年になく辛いものでした。今年は関西方面の出張が多かったこともあり FLY ON ポイントが中々貯まらず、挫けそうな時もありましたが(笑)、なんとか達成できました。
インデックスが上手く使われていないことも辛いことですが、デッドロックが発生してしまいデータが...とかなると更にツラいですよね。今回は、sp_getapplock と sp_releaseapplock を使用してデッドロックを回避する方法について説明します。過去にデッドロックについていくつか記事を書いています。今回の記事と合わせて参照してみてください。
ryuchan.hatenablog.com
ryuchan.hatenablog.com
ryuchan.hatenablog.com
ryuchan.hatenablog.com
ryuchan.hatenablog.com
下記のような二つのクエリを同時に実行すると、残念ながらデッドロックが発生してしまいます。(昔の記事で使用したクエリを使用します。よく見るとひどいクエリだ...)
- クエリ 1
DECLARE @Error_Message NVARCHAR(MAX); DECLARE @Error_Severity INT; DECLARE @Error_State INT; WHILE 0 = 0 BEGIN BEGIN TRY BEGIN TRAN -- ProductID = 2 のデータを更新する。 UPDATE Production.Product SET Name = N'Bearing Ball' WHERE ProductID = 2 -- ProductID = 1 のデータを更新する。 UPDATE Production.Product SET Name = N'Adjustable Race' WHERE ProductID = 1 COMMIT TRAN END TRY -- 例外をキャッチしたら、メッセージを出力して処理を終了する。 BEGIN CATCH SET @Error_Message = ERROR_MESSAGE(); SET @Error_Severity = ERROR_SEVERITY(); SET @Error_State = ERROR_STATE(); RAISERROR(@Error_Message, @Error_Severity, @Error_State); BREAK; END CATCH END
- クエリ 2
DECLARE @Error_Message NVARCHAR(MAX); DECLARE @Error_Severity INT; DECLARE @Error_State INT; WHILE 0 = 0 BEGIN BEGIN TRY BEGIN TRAN -- ProductID = 1 のデータを更新する。 UPDATE Production.Product SET Name = N'Adjustable Race' WHERE ProductID = 1 -- ProductID = 2 のデータを更新する。 UPDATE Production.Product SET Name = N'Bearing Ball' WHERE ProductID = 2 COMMIT TRAN END TRY -- 例外をキャッチしたら、メッセージを出力して処理を終了する。 BEGIN CATCH SET @Error_Message = ERROR_MESSAGE(); SET @Error_Severity = ERROR_SEVERITY(); SET @Error_State = ERROR_STATE(); RAISERROR(@Error_Message, @Error_Severity, @Error_State); BREAK; END CATCH END
上記のクエリに、sp_getapplock と sp_releaseapplock の処理を加えてみます。
- クエリ 1
DECLARE @Error_Message NVARCHAR(MAX); DECLARE @Error_Severity INT; DECLARE @Error_State INT; DECLARE @Return_Code INT; WHILE 0 = 0 BEGIN BEGIN TRY BEGIN TRAN -- アプリケーションロックの獲得 EXEC @Return_Code = sp_getapplock @Resource = 'lock_resource_id', @LockMode = 'Exclusive', @LockOwner = 'Transaction', @LockTimeout = 60000 IF @Return_Code >= 0 BEGIN -- ProductID = 2 のデータを更新する。 UPDATE Production.Product SET Name = N'Bearing Ball' WHERE ProductID = 2 -- ProductID = 1 のデータを更新する。 UPDATE Production.Product SET Name = N'Adjustable Race' WHERE ProductID = 1 -- アプリケーションロックの解放 EXEC sp_releaseapplock @Resource = 'lock_resource_id' END COMMIT TRAN END TRY -- 例外をキャッチしたら、メッセージを出力して処理を終了する。 BEGIN CATCH -- アプリケーションロックの解放 EXEC sp_releaseapplock @Resource = 'lock_resource_id' ROLLBACK SET @Error_Message = ERROR_MESSAGE(); SET @Error_Severity = ERROR_SEVERITY(); SET @Error_State = ERROR_STATE(); RAISERROR(@Error_Message, @Error_Severity, @Error_State); BREAK; END CATCH END
- クエリ 2
DECLARE @Error_Message NVARCHAR(MAX); DECLARE @Error_Severity INT; DECLARE @Error_State INT; DECLARE @Return_Code INT; WHILE 0 = 0 BEGIN BEGIN TRY BEGIN TRAN -- アプリケーションロックの獲得 EXEC @Return_Code = sp_getapplock @Resource = 'lock_resource_id', @LockMode = 'Exclusive', @LockOwner = 'Transaction', @LockTimeout = 60000 IF @Return_Code >= 0 BEGIN -- ProductID = 1 のデータを更新する。 UPDATE Production.Product SET Name = N'Adjustable Race' WHERE ProductID = 1 -- ProductID = 2 のデータを更新する。 UPDATE Production.Product SET Name = N'Bearing Ball' WHERE ProductID = 2 -- アプリケーションロックの解放 EXEC sp_releaseapplock @Resource = 'lock_resource_id' END COMMIT TRAN END TRY -- 例外をキャッチしたら、メッセージを出力して処理を終了する。 BEGIN CATCH -- アプリケーションロックの解放 EXEC sp_releaseapplock @Resource = 'lock_resource_id' ROLLBACK SET @Error_Message = ERROR_MESSAGE(); SET @Error_Severity = ERROR_SEVERITY(); SET @Error_State = ERROR_STATE(); RAISERROR(@Error_Message, @Error_Severity, @Error_State); BREAK; END CATCH END
SQL Server Management Studio で上記のクエリを実行してみましょう。デッドロックは発生しません。
sys.dm_tran_locks から APPLICATION ロックが獲得されているのを確認できます。
通常デッドロックを回避するにはリソース更新順番などを見直すのがベストです。アプリケーションロックを使用するとそれなりに負荷も上がるので、適用するには注意が必要です。
ryuchan.hatenablog.com
新年会は、お肉!赤身最高。
【東京都内】肉通も太鼓判!絶品肉料理に出会えるお店17選 [食べログまとめ]
「よろにく」さん良さそう....
- ジャンル:焼肉
- 住所: 港区南青山6-6-22 ルナロッサ B1F
- このお店を含むブログを見る |
- (写真提供:RyoJoGo)
- よろにくをぐるなびで見る | 表参道・青山の焼肉をぐるなびで見る
今年の子供のクリスマスプレゼントは Switch 。なんとか手に入った。
- 出版社/メーカー: 任天堂
- 発売日: 2017/03/03
- メディア: Video Game
- この商品を含むブログ (2件) を見る
それ以外にも色々と購入。
Nintendo Switch 充電ケーブルMatchdor任天堂延長ケーブルUSB3.1 Type-C 高速データ転送 放熱対策 ニンテンドースイッチ ドック用延長ケーブル【第三世代】
- 出版社/メーカー: Matchdor Tech
- メディア: Video Game
- この商品を含むブログを見る
【Nintendo Switch対応】全面保護PCカバー for Nintendo Switch
- 出版社/メーカー: ホリ
- 発売日: 2017/10/12
- メディア: Video Game
- この商品を含むブログを見る
【Nintendo Switch対応】貼りやすいブルーライトカットフィルム ピタ貼り for Nintendo Switch
- 出版社/メーカー: ホリ
- 発売日: 2017/03/03
- メディア: Video Game
- この商品を含むブログを見る
Switch をやっていると、結構バッテリーを消耗するようなので、自分が今使っているモバイルバッテリーを子供にあげて、自分用に MacBook Pro も充電できるモバイルバッテリーを購入。Parallels で Windows を使っていると異常にバッテリーを使うので必須ですね。丸一日のカンファレンスに参加しても安心です。