2009/09/24

SQL Server のデッドロックハンドル方法の記事


CodeProject: SQL SERVER - How To Handle Deadlock. Free source code and programming help

英語の記事なのですが、SQL Server で起きるデッドロックのハンドリング方法が解説されています。

SQL Server は複数のトランザクションでデッドロックが発生すると、自動的に一方のトランザクションを終了しロールバックされ、エラーメッセージの 1205 を返すとのこと。

ですので、このエラーメッセージを利用してデッドロックのハンドリングができるようです。

元記事では Transaction A と Transaction B が紹介されていますが、ここでは記述方法が分かればよいと思うので Transaction A のコードのみ引用します。

RETRY: -- Label RETRY
BEGIN TRANSACTION
BEGIN TRY

  UPDATE Customer SET LastName = 'John' WHERE CustomerId=111
  WAITFOR DELAY '00:00:05'  -- Wait for 5 ms
  UPDATE Orders SET CustomerId = 1 WHERE OrderId = 221

  COMMIT TRANSACTION
END TRY
BEGIN CATCH
  PRINT 'Rollback Transaction'
  ROLLBACK TRANSACTION
  IF ERROR_NUMBER() = 1205 -- Deadlock Error Number
  BEGIN
    WAITFOR DELAY '00:00:00.05' -- Wait for 5 ms
    GOTO RETRY -- Go to Label RETRY
  END
END CATCH

また、複数スレッドでトランザクションが実行された場合を想定して、3回までリトライする方法も紹介されています。

DECLARE @RetryCounter INT
SET @RetryCounter = 1
RETRY: -- Label RETRY
BEGIN TRANSACTION
BEGIN TRY

  UPDATE Customer SET LastName = 'John' WHERE CustomerId=111
  WAITFOR DELAY '00:00:05'  -- Wait for 5 ms
  UPDATE Orders SET CustomerId = 1 WHERE OrderId = 221

  COMMIT TRANSACTION
END TRY
BEGIN CATCH
  PRINT 'Rollback Transaction'
  ROLLBACK TRANSACTION
  DECLARE @DoRetry bit; -- Whether to Retry transaction or not
  DECLARE @ErrorMessage varchar(500)
  SET @doRetry = 0;
  SET @ErrorMessage = ERROR_MESSAGE()
  IF ERROR_NUMBER() = 1205 -- Deadlock Error Number
  BEGIN
    SET @doRetry = 1; -- Set @doRetry to 1 only for Deadlock
  END
  IF @DoRetry = 1
  BEGIN
    SET @RetryCounter = @RetryCounter + 1 -- Increment Retry Counter By one
    IF (@RetryCounter > 3) -- Check whether Retry Counter reached to 3
    BEGIN
      RAISERROR(@ErrorMessage, 18, 1) -- Raise Error Message if
        -- still deadlock occurred after three retries
    END
    ELSE
    BEGIN
      WAITFOR DELAY '00:00:00.05' -- Wait for 5 ms
      GOTO RETRY  -- Go to Label RETRY
    END
  END
  ELSE
  BEGIN
    RAISERROR(@ErrorMessage, 18, 1)
  END
END CATCH

なかなか参考になりますね。

 


スポンサーリンク


このエントリーをはてなブックマークに追加




Twitter ではブログにはない、いろんな情報を発信しています。


コメント

コメントを書く



プロフィール

  • 名前:fnya
    経歴:
    SE としての経験は15年以上。様々な言語と環境で業務系システム開発を行い、セキュリティ対策などもしていました。現在は趣味SE。

    Twitter では、ブログでは取り上げない情報も公開しています。


    ブログについて

    このブログは、IT、スマートフォン、タブレット、システム開発などに関するさまざまな話題を取り上げたり、雑感などをつづっています。

    現在、Enty で支援を受け付けています。もしよければご支援ください。



    >>ブログ詳細
    >>自作ツール
    >>運営サイト
    >>Windows 10 まとめ

    Twitter のフォローはこちらから Facebook ページはこちら Google+ページはこちら RSSフィードのご登録はこちらから