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

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)godbcc 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 起動時パラメーターの出力を入手する必要があります。実際のデッドロックでは、さまざまなプロセスとリソースが関連しています。そのため、解決方法は場合によって異なります。デッドロックの解決に使用できる一般的な方法を以下に示します。
  • インデックスの追加および削除を行います。
  • インデックス ヒントを追加します。
  • 類似したパターンでリソースにアクセスするアプリケーションを変更します。
  • トリガーなどのトランザクションから、操作を削除します。既定では、トリガーはトランザクションの一部です。
  • トランザクションをできる限り短くします。
プロパティ

文書番号:832524 - 最終更新日: 07/02/2013 13:55:00 - リビジョン: 3.0

Microsoft SQL Server 2000 Standard Edition

  • kbsqlsetup kbtypenonkb kbpubtypett kbresource kbquery kbperformance kbserver kbdatabase kbhowto kbinfo kbcode KB832524
フィードバック