SQL Server 技術情報 - デッドロックの解決方法

文書翻訳 文書翻訳
文書番号: 832524 - 対象製品
すべて展開する | すべて折りたたむ

目次


SQL Server 技術情報

この資料では、デッドロックの解決方法について説明します。

目標

デッドロックを特定し、デッドロックのトラブルシューティングを行い、デッドロックの解決方法を推奨します。

はじめに

この資料では、デッドロックの状況を調査し、デッドロックを解決するための手順について説明します。デッドロックの種類はさまざまで、いくつかの異なる環境変数が原因である場合があります。この資料に記載されている情報は、デッドロックの特定と解決に役立ちます。

ケース スタディ

ケース スタディでは、6 人のオペレーターがいる 911 緊急システムを調査します。システムの稼働率のピーク期間には、使用している Microsoft Visual Basic フロントエンド アプリケーションの接続が解除されることがあります。接続が解除された場合、オペレータはデータを再入力する必要があります。1 日 24 時間 365 日稼働する 911 緊急システムでは、このような動作は許容されません。

デッドロックとは

2 つのシステム サーバー プロセス ID (SPID) が 1 つのリソースを待機しており、一方のプロセスによってそのリソースがもう一方のプロセスで使用できなくなっているために、両方のプロセスがそれ以上処理を進めることができなくなった場合に、デッドロックが発生します。

ロック マネージャーのスレッドは、デッドロックをチェックします。ロック マネージャーのデッドロック検出アルゴリズムよってデッドロックが検出されると、ロック マネージャがいずれかの SPID を、デッドロックが発生している SPID として選択します。ロック マネージャは、クライアントに送信する 1205 エラー メッセージを発行し、選択した SPID を終了します。SPID を終了するとリソースが解放されるため、もう一方の SPID での処理を続行できます。Visual Basic フロントエンド アプリケーションで接続が解除されるのは、デッドロックが発生している SPID を終了することが原因です。

設計時にデッドロックについて十分に考慮されたアプリケーションでは、フロントエンド アプリケーションで 1205 エラーをトラップし、SQL Server に再接続してトランザクションを再送信します。

デッドロックは最小限に抑えることができますが、完全には回避できません。そのため、デッドロックを処理するようにフロントエンド アプリケーションを設計する必要があります。

デッドロックの特定方法

手順 1

デッドロックを特定するには、まず、ログ情報を入手する必要があります。デッドロックの疑いがある場合は、デッドロックに関連する SPID とリソースの情報を収集する必要があります。これを行うには、起動時のパラメーターである -T1204 および -T3605 を SQL Server のプロパティに追加します。これら 2 つの起動時パラメーターを追加するには、以下の手順を実行します。
  • SQL Server Enterprise Manager を起動します。
  • サーバーを右クリックします。
  • [プロパティ] をクリックします。
  • [起動時のパラメーター] をクリックします。
  • [起動時のパラメーター] ダイアログ ボックスの [パラメーター] ボックスに、「-T1204」と入力し、[追加] をクリックします。
  • [パラメーター] ボックスに、「-T3605」と入力し、[追加] をクリックします。
  • [OK] をクリックします。

起動時のパラメーターは、SQL Server を停止し再開したときに適用されます。

-T1204 起動時パラメーターを追加すると、デッドロック検出アルゴリズムによってデッドロックが検出された場合に、プロセスとリソースに関する情報が収集されます。-T3605 パラメーターを追加すると、その情報が SQL Server エラー ログに書き込まれます。

-T1205 起動時パラメーターを追加すると、デッドロックの検出時ではなく、デッドロック検出アルゴリズムによってデッドロックがチェックされるたびに情報が収集されます。-T1205 パラメーターは、使用しない場合も問題はありません。

-T1205 起動時パラメーターを使用する場合、SQL Server エラー ログに出力されるサンプル出力を以下に示します。

2003-05-14 11:46:26.76 spid4     Starting deadlock search 1 2003-05-14 11:46:26.76 spid4     Target Resource Owner:2003-05-14 11:46:26.76 spid4      ResType:LockOwner Stype:'OR' Mode:S SPID:55 ECID:0 Ec:(0x43CAB580) Value:0x42bdf340 2003-05-14 11:46:26.76 spid4      Node:1       ResType:LockOwner Stype:'OR' Mode:S SPID:55 ECID:0 Ec:(0x43CAB580) Value:0x42bdf340 2003-05-14 11:46:26.76 spid4 2003-05-14 11:46:26.76 spid4     End deadlock search 1 ... a deadlock was not found.2003-05-14 11:46:26.76 spid4     ---------------------------------- 2003-05-14 11:46:31.76 spid4     ---------------------------------- 2003-05-14 11:46:31.76 spid4     Starting deadlock search 2


SQL Server の停止と再開を実行できない場合があります。その場合は、クエリ アナライザーを使用して次のコマンドを実行し、デッドロック トレース フラグを有効にできます。

注: この方法を使用すると、デッドロックに関する情報をすぐに収集できます。"-1" は、すべての SPID を示します。

dbcc traceon (1204, 3605, -1)
go
dbcc tracestatus(-1)
go

手順 2

次に、SQL プロファイラー トレースを収集する必要があります。デッドロック トレース フラグを有効にしている場合、必要な情報のほぼすべてを入手できますが、入手できない場合もあります。たとえば、ケース スタディでは、sp_cursoropen システム ストアド プロシージャと "UPDATE tblQueuedEvents set notifyid = 3, ResynchDate" ステートメントがデッドロックに関連していたことがトレース フラグの出力から確認できます。ただし、残念ながら、sp_cursoropen システム ストアド プロシージャの定義はわかりません。また、UPDATE ステートメントは切り詰められているため、完全な UPDATE ステートメントもわかりません。

SQL プロファイラーを使用すると、ステートメントの実行プランに加えて、完全なステートメントを入手できます。また、SQL プロファイラー トレースでは、"デッドロック"、および "デッドロック チェーン" に対するロック イベントをトレースできます。"デッドロック" は -T1204 フラグに相当し、"デッドロック チェーン" は -T1205 フラグに相当します。デッドロック トレース フラグを有効にし、デッドロックが発生する期間内で SQL プロファイラーを実行すると、デッドロックのトラブルシューティングを行う必要があるデータを入手できると考えられます。ただし、この場合に限らず、SQL プロファイラーによって実行のタイミングがずれるため、デッドロックが発生しなくなります。そのため、一般には、トレース フラグを有効にしてデッドロック情報をキャプチャした後で、SQL プロファイラーを実行します。

デッドロックのトラブルシューティング

デッドロックの発生後に、sqldiag ユーティリティと SQL プロファイラーを使用してデッドロックに関する情報を収集できます。SQLDiag.txt ファイルの出力に、"Wait-for-graph" エントリが含まれているかどうかを確認します。"Wait-for graph" エントリは、デッドロックが発生したことを示します。

-T1205 起動時パラメーターを使用した場合に、SQL Server エラー ログに書き込まれる場合がある出力のサンプルを以下に示します。

2003-05-05 15:11:50.80 spid4    Wait-for graph 2003-05-05 15:11:50.80 spid4    Node:1 2003-05-05 15:11:50.80 spid4    ResType:LockOwner Stype:'OR' Mode:S SPID:55 ECID:0 Ec:(0x33AE1538) Value:0x193 2003-05-05 15:11:50.80 spid4    Victim Resource Owner:2003-05-05 15:11:50.80 spid4    ResType:LockOwner Stype:'OR' Mode:X SPID:60 ECID:0 Ec:(0x1F1BB5B0) Value:0x193 2003-05-05 15:11:50.80 spid4    Requested By:2003-05-05 15:11:50.80 spid4    Input Buf:RPC Event:sp_cursoropen;1 2003-05-05 15:11:50.80 spid4    SPID:55 ECID:0 Statement Type:EXECUTE Line #:1 2003-05-05 15:11:50.80 spid4    Owner:0x1937f2a0 Mode:S        Flg:0x0 Ref:1 Life:00000000 SPID:55 ECID:0 2003-05-05 15:11:50.80 spid4    Grant List 0::2003-05-05 15:11:50.80 spid4    KEY:8:1653632984:2 (da00ce043a9e) CleanCnt:1 Mode:U Fl ags:0x0
 
2003-05-05 15:11:50.80 spid4    Node:2 2003-05-05 15:11:50.80 spid4    ResType:LockOwner Stype:'OR' Mode:S SPID:55 ECID:0 Ec:(0x33AE1538) Value:0x193 2003-05-05 15:11:50.80 spid4    Requested By:2003-05-05 15:11:50.80 spid4    Input Buf:Language Event:Update tblQueuedEvents Set NotifyID = 2, ResynchDate 2003-05-05 15:11:50.80 spid4    SPID:60 ECID:0 Statement Type:UPDATE Line #:1 2003-05-05 15:11:50.80 spid4    Owner:0x1936e420 Mode:X        Flg:0x0 Ref:0 Life:02000000 SPID:60 ECID:0 2003-05-05 15:11:50.80 spid4    Grant List 0::2003-05-05 15:11:50.80 spid4    KEY:8:1653632984:1 (2d018af70d80) CleanCnt:1 Mode:X Flags:0x0


"Wait-for-graph" エントリには、Node 1 および Node 2 が含まれています。各ノードには、許可セクションと要求セクションが含まれています。許可セクションとは "Grant List" のことであり、要求セクションは "Request By" のことです。
各ノードでは、以下について特定できます。
  • SPID
  • SPID で実行していたコマンド
  • リソース
  • リソースのロック モード

たとえば、Node 1 の Grant List を参照すると、SPID 55 にリソース KEY: 8:1653632984:2 の更新ロック (Mode: U) が許可されています。KEY: 8:1653632984:2 では、8 が DBID、1653632984 が ObjectID、2 が Indid を表します。データベース識別番号を取得するには、sp_helpdb ストアド プロシージャを実行します。テーブルを取得するには、次のコードを実行します。
select * from sysobjects where id = 1653632984


インデックスを取得するには、次のコードを実行します。
select * from sysindexes where indid = 2 and id = 1653632984

IndexId が 2 の場合、インデックスは非クラスター化インデックスです。SPID 55 で実行していたコマンドは、sp_cursoropen ストアド プロシージャでした。

Node 2 の Grant List を参照すると、SPID 60 にリソース KEY: 8:1653632984:1 の排他ロック (Mode: X) が許可されています。KEY: 8:1653632984:1 では、8 が DBID、1653632984 が ObjectID、1 が Indid を表します。これは、同じテーブルについて表していますが、インデックス 1 はクラスター化インデックスを表します。SPID 60 で実行していたコマンドを次に示します。
Update tblQueuedEvents Set NotifyID = 2, ResynchDate

IndexId が 1 の場合は、クラスター化インデックスです。

IndexId が 2 の場合は、非クラスター化インデックスです。

: デッドロックの発生は、タイミングに大きく依存します。

次に、Node 1 の Request By を見ると、SPID 55 が IndexId=1 の共有ロック (Mode: S) を要求しています。Node 2 の Request By を見ると、SPID 60 が IndexId=2 の排他ロック (Mode: X) を要求しています。これらのロック要求が同時に発生するのでデッドロックが発生します。各 SPID に許可されたロックによって、それぞれが要求したロックが相互に妨害されています。

次の表に、ロックの互換性を示します。ロックの互換性に関する詳細については、SQL Server 2000 Books Online の「ロックの互換性」を参照してください。

ロックの互換性の表
元に戻す全体を表示する
要求された モードISSUIXSIXX
インテント共有 (IS)×
共有 (S)XXX
更新 (U) XXXX
インテント排他 (IX)XXXX
インテント排他付き共有 (SIX) XXXXX
排他 (X)XXXXXX


次に、出力を参照すると、ObjectId 1653632984 が tblQueuedEvents テーブルであることが確認できるため、sp_help ストアド プロシージャを使用してこのテーブルの出力を入手します。このテーブルには 2 つのインデックスがありました。それらは、ix_tblQueuedEvents PK_tblQueuedEvent です。ix_tblQueuedEvents は、ResynchDate のクラスター化インデックスであり、PK_tblQueuedEvent は主キーで構成される、EventSID の一意の非クラスター化インデックスです。

SQL プロファイラー トレースでは、デッドロックの発生をキャプチャできませんでした。デッドロックは時刻に大きく依存します。SQL プロファイラーのオーバーヘッドによっていずれかのプロセスの実行に多少の時間がかかったために、SQL プロファイラーでデッドロックの状況をキャプチャできなかった可能性があります。ただし、SQL プロファイラー トレースから、この問題のトラブルシューティングに使用できる情報を入手できます。次に示す完全な tblQueuedEvents 更新ステートメントを確認できます。

Update tblQueuedEvents Set NotifyID = 2, ResynchDate = '5/7/2003 10:44:16' where eventSID = 73023
また、実行プランも参照できます。実行プランを参照しても、完全な sp_cursoropen ストアド プロシージャ ステートメントは確認できませんが、デッドロックの解決方法を推奨するために十分な情報を入手できます。

以下に、実行プランを示します。

: 次に示す実行プランは、右から左へ、下から上へ読み取られます。

StmtText                                                      
				                                                                           
				   
				                                  
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
				
Update tblQueuedEvents Set NotifyID = 2, ResynchDate = '5/7/2003 10:44:16' where eventSID = 73023                                                     
				   
				                    
|--Clustered Index Update(OBJECT:([SOTS].[dbo].[tblQueuedEvents].[ix_tblQueuedEvents ]), SET:([tblQueuedEvents].[NotifyID]=[@1], [tblQueuedEvents].[ResynchDate]=[Expr1004])) |--Top(1)                                                                 
				                                                                           
				   
				               
|--Compute Scalar(DEFINE:([Expr1004]=Convert([@2])))                
				                                                                           
				   
				                
|--Index Seek(OBJECT:([SOTS].[dbo].[tblQueuedEvents].[PK_tblQueuedEvents]), SEEK:([tblQueuedEvents].[EventSID]=[@3])

デッドロックの解決方法の推奨

UPDATE ステートメントはクラスター化インデックスに対して "クラスター化インデックスの更新" を実行しています。そのため、非クラスター化インデックスとクラスター化インデックスの両方が更新される必要があります。クラスター化インデックスは ix_tblQueuedEvents であり、非クラスター化インデックスは PK_tblQueuedEvents です。UPDATE ステートメントは、更新を実行するために両方のインデックスの排他ロックを取得する必要があります。これら 2 つのインデックスは、デッドロックに関連するインデックスです。SQL プロファイラー トレースを確認すると、WHERE 句で ResynchDate を使用しているクエリはありません。どのステートメントも非常に特殊なステートメントであり、WHERE 句では EventSID を使用していました。クラスター化インデックスでは EventSID を使用する方が適切と考えられます。この情報と、ユーザーとの話し合いを基に、ResynchDate インデックスは古いため不要であると判断しました。そこで、ユーザーに ResynchDate の ix_tblQueuedEvents インデックスを削除し、PK_tblQueuedEvent をクラスター化インデックスにするように勧めました。このようにして、このデッドロックを解決しました。

これは、ロックが関連しているデッドロックの一例にすぎません。デッドロックには、並列実行およびスレッドも関連している場合があります。その場合、1 つ以上の SPID およびリソースが関係することがあります。すべてのデッドロックにおいて、デッドロックを特定し、トラブルシューティングを行い解決するには、-T1204 起動時パラメーターの出力を入手する必要があります。実際のデッドロックでは、さまざまなプロセスとリソースが関連しています。そのため、解決方法は場合によって異なります。デッドロックの解決に使用できる一般的な方法を以下に示します。
  • インデックスの追加および削除を行います。
  • インデックス ヒントを追加します。
  • 類似したパターンでリソースにアクセスするアプリケーションを変更します。
  • トリガーなどのトランザクションから、操作を削除します。既定では、トリガーはトランザクションの一部です。
  • トランザクションをできる限り短くします。

関連情報

デッドロックの詳細については、次のマイクロソフト Web サイトを参照してください。

http://msdn2.microsoft.com/ja-jp/library/Aa213040

http://msdn2.microsoft.com/ja-jp/library/aa213042(SQL.80).aspx

http://msdn2.microsoft.com/ja-jp/library/aa213028(SQL.80).aspx

http://msdn2.microsoft.com/ja-jp/library/aa937573(SQL.80).aspx

http://msdn2.microsoft.com/ja-jp/library/aa213041(SQL.80).aspx

プロパティ

文書番号: 832524 - 最終更新日: 2013年7月2日 - リビジョン: 3.0
この資料は以下の製品について記述したものです。
  • Microsoft SQL Server 2000 Standard Edition
Microsoft Knowledge Base の免責: Microsoft Knowledge Baseに含まれている情報は、いかなる保証もない現状ベースで提供されるものです。Microsoft Corporation及びその関連会社は、市場性および特定の目的への適合性を含めて、明示的にも黙示的にも、一切の保証をいたしません。さらに、Microsoft Corporation及びその関連会社は、本文書に含まれている情報の使用及び使用結果につき、正確性、真実性等、いかなる表明・保証も行ないません。Microsoft Corporation、その関連会社及びこれらの権限ある代理人による口頭または書面による一切の情報提供またはアドバイスは、保証を意味するものではなく、かつ上記免責条項の範囲を狭めるものではありません。Microsoft Corporation、その関連会社 及びこれらの者の供給者は、直接的、間接的、偶発的、結果的損害、逸失利益、懲罰的損害、または特別損害を含む全ての損害に対して、状況のいかんを問わず一切責任を負いません。(Microsoft Corporation、その関連会社 またはこれらの者の供給者がかかる損害の発生可能性を了知している場合を含みます。) 結果的損害または偶発的損害に対する責任の免除または制限を認めていない地域においては、上記制限が適用されない場合があります。なお、本文書においては、文書の体裁上の都合により製品名の表記において商標登録表示、その他の商標表示を省略している場合がありますので、予めご了解ください。

フィードバック

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com