デッドロック（SQL Server）

ITコーディネータのシュウです。

お世話になった知人の方が、仕事の都合もあって実家のある愛媛県のほうに引っ越しされるということで、瀬戸大橋を電車で移動されているときに撮られた写真を頂きました。まだ瀬戸大橋を渡ったことはないのですが、是非一度行ってみたいですね。
そういえば、瀬戸大橋を渡る瀬戸大橋線に乗っているときに、瀬戸の花嫁の曲が流れるみたいですね。「瀬戸は日暮れて～！」瀬戸に来たという実感がわくみたいです。

さて、dbSheetClientに新しい事例がアップされました。
成約引合管理、予算収集、ワークフロー、文書管理、業績見える化まで、様々な業務効率化システムを4年間で８０以上構築稼働させたナラサキ産業様の事例です。詳しくは、こちらを参照してみてください。
http://www.newcom07.jp/dbsheetclient/usrvoice/narasakisangyo.html

＜本日の題材＞
デッドロック（SQL Server）

なかなか普段はそんなにはお目にかからないデッドロックの現象ですが、複数のクライアントが同時に同じテーブルにアクセスする環境では、アプリの作り方によっては簡単に起きてしまうこともあります。
一方のタスクがロックを試みているリソースに対して他方のタスクがロックを獲得し、これが相互に行われるとデッドロックが発生するというのが一般的な現象になると思います。本日は、複数のテーブルに対して更新する処理を、処理の順番が逆になるかたちで2つのプロセスが行うかたちでのデッドロック現象を確認してみたいと思います。ちなみに、同じテーブルの異なる行に対する処理でも同様に発生します。

例）
2つのテーブル（tbl_A、tbl_B）があり、片方の処理は、トランザクション処理の中で、tbl_Aのレコード（CD_A=4）を更新した後に、tbl_Bのレコード（CD_B=4）を更新しますが、同時に、もう一つの処理が tbl_Bのレコード（CD_B=4）を更新した後に、tbl_A（CD_A=4）の値を更新します。
なお、タイミングが重なるように、処理の途中で以前ブログでも取り上げましたスリープ処理（WAITFOR DELAY）を入れてみます。

処理A）：
BEGIN TRANSACTION
UPDATE dbo.tbl_A SET
amount = 2100
WHERE CD_A = 4;

WAITFOR DELAY '00:00:10'

UPDATE dbo.tbl_B SET
amount = 3000
WHERE CD_B = 4;
COMMIT TRANSACTION

処理B）：
BEGIN TRANSACTION
UPDATE dbo.tbl_B SET
amount = 1200
WHERE CD_B = 4;

WAITFOR DELAY '00:00:3'

UPDATE dbo.tbl_A SET
amount = 1500
WHERE CD_A = 4;
COMMIT TRANSACTION

処理Aを実行した直後に、処理Bを実行します。すると、下記のようなかたちで処理が進み、デッドロックが発生します。

実際に、SQL Serverで実行してみます。
SQL Server Maangement Studioの1つのウィンドウで処理Aを実行し、すぐに別のウィンドウで処理Bを実行します。

処理A

処理B

すると、処理Aでは、上記の結果のように、下記のようなデッドロックのエラーが表示されます。

「メッセージ 1205、レベル 13、状態 51、行 9
トランザクション (プロセス ID 52) が、ロック個のリソースで他のプロセスとデッドロックして、このトランザクションがそのデッドロックの対象となりました。トランザクションを再実行してください。」

それでは、次に、デッドロックが発生したときの対応として、エラーコード：1205を取得したら処理をリトライするという仕組みを、BEGIN CATCH～END CATCHの中に組み込んでみます。

処理A)
RETRY:
BEGIN TRANSACTION
BEGIN TRY
UPDATE dbo.tbl_A SET
amount = 2100
WHERE CD_A = 4;
WAITFOR DELAY '00:00:10'
UPDATE dbo.tbl_B SET
amount = 3000
WHERE CD_B = 4;
COMMIT TRANSACTION
END TRY

BEGIN CATCH
PRINT 'ロールバックします'
ROLLBACK TRANSACTION
IF ERROR_NUMBER() = 1205 -- デッドロックエラーコード
BEGIN
WAITFOR DELAY '00:00:05'
GOTO RETRY
END
END CATCH

処理B)
RETRY:
BEGIN TRANSACTION
BEGIN TRY
UPDATE dbo.tbl_B SET
amount = 1200
WHERE CD_B = 4;
WAITFOR DELAY '00:00:3'
UPDATE dbo.tbl_A SET
amount = 1500
WHERE CD_A = 4;
COMMIT TRANSACTION
END TRY

BEGIN CATCH
PRINT 'ロールバックします'
ROLLBACK TRANSACTION
IF ERROR_NUMBER() = 1205 -- デッドロックエラーコード
BEGIN
WAITFOR DELAY '00:00:05'
GOTO RETRY
END
END CATCH

SQL Serverで実際に実行してみます。

処理A）