都内で働くSEの技術的なひとりごと / Technical soliloquy of System Engineer working in Tokyo

都内でサラリーマンやってます。SQL Server を中心とした (2023年からは Azure も。) マイクロソフト系(たまに、OSS系などマイクロソフト以外の技術も...)の技術的なことについて書いています。日々の仕事の中で、気になったことを技術要素関係なく気まぐれに選んでいるので記事内容は開発言語、インフラ等ばらばらです。なお、当ブログで発信、発言は私個人のものであり、所属する組織、企業、団体等とは何のかかわりもございません。ブログの内容もきちんと検証して使用してください。英語の勉強のため、英語の

デッドロックについて説明してみる - その 6 ( sp_getapplock と sp_releaseapplock でデッドロックを回避してみる ) -

 今年のダイヤモンド修行は例年になく辛いものでした。今年は関西方面の出張が多かったこともあり FLY ON ポイントが中々貯まらず、挫けそうな時もありましたが(笑)、なんとか達成できました。
f:id:koogucc11:20171224093914p:plain

 インデックスが上手く使われていないことも辛いことですが、デッドロックが発生してしまいデータが...とかなると更にツラいですよね。今回は、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_getapplocksp_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 で上記のクエリを実行してみましょう。デッドロックは発生しません。
f:id:koogucc11:20171223112651p:plain

 sys.dm_tran_locks から APPLICATION ロックが獲得されているのを確認できます。
f:id:koogucc11:20171223113013p:plain

 通常デッドロックを回避するにはリソース更新順番などを見直すのがベストです。アプリケーションロックを使用するとそれなりに負荷も上がるので、適用するには注意が必要です。
ryuchan.hatenablog.com

 新年会は、お肉!赤身最高。
【東京都内】肉通も太鼓判!絶品肉料理に出会えるお店17選 [食べログまとめ]

「よろにく」さん良さそう....

よろにく

食べログ よろにく

 今年の子供のクリスマスプレゼントは Switch 。なんとか手に入った。

 それ以外にも色々と購入。

【Nintendo Switch対応】全面保護PCカバー for Nintendo Switch

【Nintendo Switch対応】全面保護PCカバー for Nintendo Switch

 Switch をやっていると、結構バッテリーを消耗するようなので、自分が今使っているモバイルバッテリーを子供にあげて、自分用に MacBook Pro も充電できるモバイルバッテリーを購入。ParallelsWindows を使っていると異常にバッテリーを使うので必須ですね。丸一日のカンファレンスに参加しても安心です。