デッドロック(SQL Server)

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

IMG_0641

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

さて、dbSheetClientに新しい事例がアップされました。
成約引合管理、予算収集、ワークフロー、文書管理、業績見える化まで、様々な業務効率化システムを4年間で80以上構築稼働させたナラサキ産業様の事例です。詳しくは、こちらを参照してみてください。
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
デッドロック処理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)

デッドロック処理A_retry

 処理B)

デッドロック処理B_retry

処理Aのほうが、一旦はデッドロックのエラーが発生しましたが、5秒後に再度リトライを実施して、最終的には更新が終了している様子が確認できます。

処理後の、tbl_A、tbl_B のデータを確認してみると

tbl_Aは

tbl_A結果

 tbl_Bは

tbl_B結果

 上記から、最後は処理Aの更新処理がされていることが確認できました。

さて、実際には、デッドロックは上記のようなケースだけではなく、ロックのエスカレーションによるブロッキングが原因で発生する場合などもあります。これについては、また機会があるときに取りあげてみたいと思います。

今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村