次の方法で共有


デッドロックの検出と終了

デッドロックは、複数のタスクが無期限に相互ブロックすることで発生します。つまり、一方のタスクがロックを試みているリソースに対して他方のタスクがロックを獲得し、これが相互に行われるとデッドロックが発生します。次の図に、デッドロック状態の概要を示します。

  • タスク T1 は、リソース R1 のロックを所有し (R1 から T1 への矢印で表しています)、リソース R2 のロックを要求しました (T1 から R2 への矢印で表しています)。

  • タスク T2 は、リソース R2 のロックを所有し (R2 から T2 への矢印で表しています)、リソース R1 のロックを要求しました (T2 から R1 への矢印で表しています)。

  • どちらのタスクもリソースが使用できるようになるまで続行できず、どちらのリソースもタスクが続行するまで解放できないため、デッドロック状態が発生します。

デッドロック状態のタスク

SQL Server データベース エンジンは、SQL Server 内のデッドロック サイクルを自動的に検出します。データベース エンジンがセッションの 1 つをデッドロックの対象として選択すると、現在のトランザクションはエラーで終了し、デッドロックが解除されます。

デッドロックの原因となるリソース

各ユーザー セッションに含まれている 1 つ以上のタスクが、そのセッションのためにさまざまなリソースを取得したり、リソースを取得するために待機状態にある場合があります。次のような種類のリソースは、デッドロックの原因となるブロッキングを発生させる可能性があります。

  • ロック。オブジェクト、ページ、行、メタデータ、およびアプリケーションなどのリソースに対してロック取得のために待機していると、デッドロックが発生する場合があります。たとえば、トランザクション T1 では、行 r1 の共有 (S) ロックを所有しており、r2 に排他 (X) ロックがかかるのを待機しているとします。トランザクション T2 では、r2 の共有 (S) ロックを所有しており、行 r1 に排他 (X) ロックがかかるのを待機しているとします。この結果、T1 と T2 では、互いにロックされているリソースが解放されるのを待機するロック サイクルが発生します。

  • ワーカー スレッド。キューに登録されたタスクが利用可能なワーカー スレッドを待機していると、デッドロックが発生する場合があります。キューに登録されたタスクが、ワーカー スレッドをすべてブロックしているリソースを所有している場合、デッドロックが発生します。たとえば、セッション S1 で、トランザクションが開始され、行 r1 に共有 (S) ロックがかけられ、スリープ状態になったとします。使用可能なすべてのワーカー スレッドで実行中のアクティブなセッションは、行 r1 に排他 (X) ロックをかけようとします。セッション S1 ではワーカー スレッドを取得できないので、トランザクションをコミットして行 r1 のロックを解放することができません。この結果、デッドロックが発生します。

  • メモリ。同時要求で使用できるメモリ量以上のメモリ許可を待機している場合、デッドロックが発生することがあります。たとえば、2 つの同時実行クエリ Q1 と Q2 は、それぞれ 10 MB と 20 MB のメモリを取得するユーザー定義関数として実行されます。各クエリで 30 MB が必要でも、使用できるメモリの合計が 20 MB の場合、Q1 および Q2 では、互いにメモリが解放されるまで待機する必要があります。その結果、デッドロックが発生します。

  • 並列クエリ実行関連のリソース。交換ポートに関連付けられたコーディネーター、プロデューサー、またはコンシューマーのスレッドが互いをブロックし、デッドロックを発生させることがあります。通常、この現象は、並列クエリに含まれていない別のプロセスを 1 つ以上含めようとした場合に発生します。また、並列クエリの実行が開始されると、SQL Server は、現在のワークロードに基づいて並列処理の次数やワーカー スレッドの数を決定します。たとえば、サーバーで新しいクエリの実行が開始されたり、システムのワーカー スレッドが不足したりするなど、システムのワークロードが予期せず変更される場合は、デッドロックが発生する可能性があります。

  • 複数のアクティブな結果セット (MARS) のリソース。これらのリソースは、MARS でアクティブな複数の要求のインターリーブを制御する際に使用します (詳細については、「バッチ実行環境と MARS」を参照してください)。

    • ユーザー リソース。ユーザー アプリケーションで制御されている可能性のあるリソースをスレッドが待機している場合、そのリソースは、外部リソースまたはユーザー リソースと見なされ、ロックと同様に処理されます。

    • セッション ミューテックス。1 つのセッションで実行中のタスクはインターリーブされます。つまり、セッションでは、一度に 1 つのタスクしか実行できません。タスクを実行する前に、セッション ミューテックスに排他でアクセスする必要があります。

    • トランザクション ミューテックス。1 つのトランザクションで実行中のすべてのタスクはインターリーブされます。つまり、トランザクションでは、一度に 1 つのタスクしか実行できません。タスクを実行する前に、トランザクション ミューテックスに排他でアクセスする必要があります。

    タスクを MARS で実行するには、セッション ミューテックスを取得する必要があります。タスクがトランザクションで実行されている場合は、トランザクション ミューテックスを取得する必要があります。これにより、そのセッションやトランザクションでは、一度に 1 つのタスクだけがアクティブになります。必要なミューテックスを取得後に、タスクを実行できます。タスクが終了するか、または要求の途中で中断されると、取得とは逆の順序で、最初にトランザクション ミューテックスが解放され、次にセッション ミューテックスが解放されます。ただし、これらのリソースでデッドロックが発生する場合があります。次に、ユーザーの要求 U1 と U2 という 2 つのタスクが同じセッション内で実行されているコード例を示します。

    U1:    Rs1=Command1.Execute("insert sometable EXEC usp_someproc");
    U2:    Rs2=Command2.Execute("select colA from sometable");
    

    ユーザーの要求 U1 で実行されているストアド プロシージャで、セッション ミューテックスが取得されています。このストアド プロシージャの実行に時間がかかると、データベース エンジンにより、ストアド プロシージャではユーザーからの入力を待機していると見なされます。ユーザーの要求 U2 ではセッション ミューテックスが解放されるのを待機しているのに対し、ユーザーは U2 の結果セットが返されるのを待機しています。さらに、U1 では、ユーザー リソースが解放されるのを待機しています。次の図に、このデッドロック状態を論理的に示します。

ユーザー プロセスのデッドロックを示す論理図

デッドロックの検出

上記のすべてのリソースには、データベース エンジンのデッドロック検出方式が適用されます。デッドロックの検出は、データベース エンジン インスタンスのすべてのタスクの検索を定期的に開始するロック モニター スレッドにより実行されます。検索プロセスは次のとおりです。

  • 既定の間隔は 5 秒です。

  • ロック モニター スレッドでデッドロックが検出されると、デッドロック検出の間隔は、デッドロックの頻度に応じて、5 秒より短くなります。最短の間隔は 100 ミリ秒です。

  • ロック モニター スレッドでデッドロックが検出されなくなると、データベース エンジンにより、検索の間隔は 5 秒に引き上げられます。

  • デッドロックが検出されると、ロックが解除されるのを待機する必要のある次のスレッドがデッドロック サイクルに入っていることが想定されます。デッドロックが検出されると、最初の 2 つのロック待機では、次のデッドロックの検出間隔まで待機せず、すぐにデッドロックの検索が開始されます。たとえば、検索の間隔が 5 秒に設定されている場合にデッドロックが検出されると、次のロック待機により、直ちにデッドロックの検出が開始されます。このロック待機がデッドロックの一部である場合は、このロック待機は、次のデッドロックの検索時ではなく、直ちに検出されます。

通常、データベース エンジンでは、定期的なデッドロックの検出のみが実行されます。システムでデッドロックが発生することはまれであるため、デッドロック検出を定期的に実行することにより、システムのデッドロック検出のオーバーヘッドを削減できます。

ロック モニターで、特定のスレッドに対するデッドロック検出が開始されると、スレッドが待機中のリソースが特定されます。その後、ロック モニターでは、その特定のリソースを所有するスレッドが検出され、相互の従属性を検出するまで、スレッドのデッドロック検出が繰り返されます。このように検出された相互の従属性により、デッドロックが発生します。

デッドロックが検出されると、データベース エンジンでは、スレッドの 1 つをデッドロックの対象として選択することによりデッドロックを終了します。データベース エンジンでは、スレッドに対して現在実行中のバッチを終了し、デッドロックの対象のトランザクションをロールバックして、アプリケーションに 1205 エラーを返します。デッドロックの対象のトランザクションをロールバックすると、そのトランザクションで保持されていたすべてのロックが解放されます。ロックが解放されると、他のスレッドのトランザクションのブロックは解除され、処理を続行することができるようになります。デッドロックの対象を示す 1205 エラーにより、デッドロックに関係するスレッドおよびリソースに関する情報がエラー ログに記録されます。

既定では、データベース エンジンにより、ロールバックに最もコストのかからないトランザクションを実行しているセッションがデッドロックの対象として選択されます。また、ユーザーは、SET DEADLOCK_PRIORITY ステートメントを使用して、デッドロックが発生した場合のセッションの優先度を指定することもできます。DEADLOCK_PRIORITY には、LOW、NORMAL、または HIGH を設定するか、あるいは -10 ~ 10 の範囲の整数値を設定することができます。DEADLOCK_PRIORITY の既定値は NORMAL です。2 つのセッションのデッドロックの優先度が異なる場合、優先度の低いセッションがデッドロックの対象として選択されます。2 つのセッションのデッドロックの優先度が同じ場合、ロールバックに最もコストのかからないトランザクションを含むセッションがデッドロックの対象として選択されます。デッドロック サイクルに関連するセッションのデッドロックの優先度とコストが同じ場合、対象はランダムに選択されます。

CLR を使用して作業する場合、デッドロック モニターでは、マネージ プロシージャ内でアクセスされる同期リソース (モニター、リーダー ロックとライター ロック、およびスレッド結合) のデッドロックが自動的に検出されます。ただし、デッドロックは、デッドロックの対象として選択されたプロシージャに例外をスローすることによって解決されます。デッドロックの対象が現在所有しているリソースは、この例外により自動的に解放されないことに注意してください。つまり、リソースは明示的に解放する必要があります。例外の動作と一貫性があるため、デッドロックの対象の特定に使用された例外は、キャッチおよび破棄できます。

デッドロック情報ツール

デッドロック情報を表示するために、データベース エンジンには 2 種類のトレース フラグ形式での監視ツールと SQL Server Profiler の Deadlock Graph イベントが用意されています。

トレース フラグ 1204 およびトレース フラグ 1222

デッドロックが発生すると、トレース フラグ 1204 およびトレース フラグ 1222 は SQL Server のエラー ログでキャプチャされる情報を返します。トレース フラグ 1204 では、デッドロックに関係するノードごとに形式が設定されたデッドロック情報をレポートします。トレース フラグ 1222 では、最初にプロセスごとに、次にリソースごとにデッドロック情報の形式を設定します。両方のトレース フラグを有効にして、同じデッドロック イベントを 2 種類の表示方法で取得することも可能です。

次の表は、トレース フラグ 1204 および 1222 のプロパティの定義の他に、類似点および相違点についても示しています。

プロパティ

トレース フラグ 1204 およびトレース フラグ 1222

トレース フラグ 1204 のみ

トレース フラグ 1222 のみ

出力形式

出力は SQL Server のエラー ログにキャプチャされます。

デッドロックに関係するノードだけが表示されます。各ノードには専用のセクションがあり、最後のセクションではデッドロック対象が示されます。

XML スキーマ定義 (XSD) スキーマには準拠していない、XML に似た形式で情報を返します。この形式には、3 つの主要なセクションがあります。最初のセクションでは、デッドロック対象が宣言されます。2 番目のセクションでは、デッドロックに関係する各プロセスが示されます。3 番目のセクションでは、トレース フラグ 1204 のノードと同義のリソースが示されます。

識別属性

SPID:<x> ECID:<x>。並列処理を行う場合に、システム プロセス ID のスレッドを識別します。エントリ SPID:<x> ECID:0 (<x> は SPID 値に置き換えられます) は、メイン スレッドを表します。エントリ SPID:<x> ECID:<y> (<x> は SPID 値に置き換えられ、<y> は 0 よりも大きくなります) は、同じ SPID のサブスレッドを表します。

BatchID (トレース フラグ 1222 の sbid)。コードの実行でロックを要求または保持しているバッチを識別します。複数のアクティブな結果セット (MARS) が無効になっている場合、BatchID の値は 0 になります。MARS が有効になっている場合、アクティブなバッチの値は 1 から n になります。セッションにアクティブなバッチが存在しない場合、BatchID は 0 になります。

Mode。スレッドによって要求、許可、または待機される特定のリソースに対して、ロックの種類を指定します。モードには、IS (インテント共有)、S (共有)、U (更新)、IX (インテント排他)、SIX (インテント排他付き共有)、および X (排他) があります。詳細については、「ロック モード」を参照してください。

Line # (トレース フラグ 1222 の)。デッドロックが発生したときに実行されていた、現在のステートメントのバッチの行番号が表示されます。

Input Buf (トレース フラグ 1222 の inputbuf)。現在のバッチに含まれるステートメントをすべて表示します。

Node。デッドロック チェーンに含まれるエントリ番号を表します。

Lists。次の一覧にロックの所有者が含まれる場合があります。

  • Grant List。リソースの現在の所有者を列挙します。

  • Convert List。ロックを高いレベルに変換しようとしている現在の所有者を列挙します。

  • Wait List。リソースに対する現在の新しいロック要求を列挙します。

Statement Type。権限を持つスレッドにおける DML ステートメントの種類 (SELECT、INSERT、UPDATE、または DELETE) を示します。

Victim Resource Owner。SQL Server がデッドロック サイクルを解除する対象として選択する、参加スレッドを指定します。選択したスレッドと既存のすべてのサブスレッドを終了します。

Next Branch。デッドロック サイクルに関係する、同じ SPID からの 2 つ以上のサブスレッドを表します。

deadlock victim。デッドロックの対象として選択されたタスクの物理メモリ アドレス (「sys.dm_os_tasks (Transact-SQL)」を参照) を表します。デッドロックが未解決の場合は、0 になることがあります。ロールバックを実行中のタスクは、デッドロックの対象として選択できません。

executionstack。デッドロックの発生時に実行されている Transact-SQL コードを表します。

priority。デッドロックの優先度を表します。データベース エンジンでは、同時実行性を向上させるために、短期間でデッドロックの優先度が変更されることがあります。

logused。タスクで使用されているログ領域です。

owner id。要求を制御するトランザクションの ID です。

status。タスクの状態。次の値のいずれかになります。

  • pending。ワーカー スレッドを待機しています。

  • runnable。実行できる状態ですが、クォンタムを待機しています。

  • running。スケジューラで現在実行中です。

  • suspended。実行は中断されます。

  • done。タスクが完了しました。

  • spinloop。スピンロックが解放されるのを待機しています。

waitresource。タスクで必要なリソースです。

waittime。リソースを待機する時間 (ミリ秒単位) です。

schedulerid。このタスクに関連付けられたスケジューラです。「sys.dm_os_schedulers (Transact-SQL)」を参照してください。

hostname。ワークステーション名です。

isolationlevel。現在のトランザクション分離レベルです。

Xactid。要求を制御するトランザクションの ID です。

currentdb。データベースの ID です。

lastbatchstarted。クライアント プロセスで最後にバッチ実行が開始した時刻です。

lastbatchcompleted。クライアント プロセスで最後にバッチ実行が完了した時刻です。

clientoption1 と clientoption2。このクライアント接続にオプションを設定します。これは、通常 SET NOCOUNT や SET XACTABORT などの SET ステートメントで制御されているオプションに関する情報を含むビットマスクです。

associatedObjectId。HoBT (ヒープまたは B-Tree) の ID を表します。

リソース属性

RID。ロックが保持または要求されているテーブル内の単一行を識別します。RID は RID: db_id:file_id:page_no:row_no で表されます。たとえば、RID: 6:1:20789:0 のようにします。

OBJECT。ロックが保持または要求されているテーブルを識別します。OBJECT は OBJECT: db_id:object_id で表されます。たとえば、TAB: 6:2009058193 のようにします。

KEY。ロックが保持または要求されているインデックス内のキー範囲を識別します。KEY は KEY: db_id:hobt_id (index key hash value) で表されます。たとえば、KEY: 6:72057594057457664 (350007a4d329) のようにします。

PAG。ロックが保持または要求されているページ リソースを識別します。PAG は PAG: db_id:file_id:page_no で表されます。たとえば、PAG: 6:1:20789 のようにします。

EXT。エクステント構造を識別します。EXT は EXT: db_id:file_id:extent_no で表されます。たとえば、EXT: 6:1:9 のようにします。

DB。データベース ロックを識別します。DB は次のいずれかで表されます。

  • DB: db_id

  • DB: db_id[BULK-OP-DB]。データベースのバックアップに使用されたデータベース ロックを識別します。

  • DB: db_id[BULK-OP-LOG]。特定のデータベースのバックアップ ログに使用されたロックを識別します。

APP。アプリケーション リソースに使用されたロックを識別します。APP は APP: lock_resource で表されます。たとえば、APP: Formf370f478 のようにします。

METADATA。デッドロックに関係するメタデータ リソースを表します。METADATA には多数のサブリソースがあるため、返される値はデッドロックされたサブリソースに依存します。たとえば、METADATA.USER_TYPE は user_type_id = <integer_value> を返します。METADATA のリソースおよびサブリソースの詳細については、「sys.dm_tran_locks (Transact-SQL)」を参照してください。

HOBT。デッドロックに関係するヒープまたは B-Tree を表します。

このトレース フラグに限定されるリソース属性はありません。

このトレース フラグに限定されるリソース属性はありません。

トレース フラグ 1204 の例

次の例は、トレース フラグ 1204 がオンになっている場合の出力を示しています。この場合、ノード 1 のテーブルはインデックスのないヒープ、ノード 2 のテーブルは非クラスター化インデックスのあるヒープになります。ノード 2 のインデックス キーは、デッドロックの発生時に更新されます。

Deadlock encountered .... Printing deadlock information
Wait-for graph

Node:1

RID: 6:1:20789:0               CleanCnt:3 Mode:X Flags: 0x2
 Grant List 0:
   Owner:0x0315D6A0 Mode: X        
     Flg:0x0 Ref:0 Life:02000000 SPID:55 ECID:0 XactLockInfo: 0x04D9E27C
   SPID: 55 ECID: 0 Statement Type: UPDATE Line #: 6
   Input Buf: Language Event: 
BEGIN TRANSACTION
   EXEC usp_p2
 Requested By: 
   ResType:LockOwner Stype:'OR'Xdes:0x03A3DAD0 
     Mode: U SPID:54 BatchID:0 ECID:0 TaskProxy:(0x04976374) Value:0x315d200 Cost:(0/868)

Node:2

KEY: 6:72057594057457664 (350007a4d329) CleanCnt:2 Mode:X Flags: 0x0
 Grant List 0:
   Owner:0x0315D140 Mode: X        
     Flg:0x0 Ref:0 Life:02000000 SPID:54 ECID:0 XactLockInfo: 0x03A3DAF4
   SPID: 54 ECID: 0 Statement Type: UPDATE Line #: 6
   Input Buf: Language Event: 
     BEGIN TRANSACTION
       EXEC usp_p1
 Requested By: 
   ResType:LockOwner Stype:'OR'Xdes:0x04D9E258 
     Mode: U SPID:55 BatchID:0 ECID:0 TaskProxy:(0x0475E374) Value:0x315d4a0 Cost:(0/380)

Victim Resource Owner:
 ResType:LockOwner Stype:'OR'Xdes:0x04D9E258 
     Mode: U SPID:55 BatchID:0 ECID:0 TaskProxy:(0x0475E374) Value:0x315d4a0 Cost:(0/380)

トレース フラグ 1222 の例

次の例は、トレース フラグ 1222 がオンになっている場合の出力を示しています。この場合、一方のテーブルがインデックスのないヒープになり、他方のテーブルが非クラスター化インデックスのあるヒープになります。2 番目のテーブルでは、デッドロックの発生時にインデックス キーが更新されます。

deadlock-list
 deadlock victim=process689978
  process-list
   process id=process6891f8 taskpriority=0 logused=868 
   waitresource=RID: 6:1:20789:0 waittime=1359 ownerId=310444 
   transactionname=user_transaction 
   lasttranstarted=2005-09-05T11:22:42.733 XDES=0x3a3dad0 
   lockMode=U schedulerid=1 kpid=1952 status=suspended spid=54 
   sbid=0 ecid=0 priority=0 transcount=2 
   lastbatchstarted=2005-09-05T11:22:42.733 
   lastbatchcompleted=2005-09-05T11:22:42.733 
   clientapp=Microsoft SQL Server Management Studio - Query 
   hostname=TEST_SERVER hostpid=2216 loginname=DOMAIN\user 
   isolationlevel=read committed (2) xactid=310444 currentdb=6 
   lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200
    executionStack
     frame procname=AdventureWorks2008R2.dbo.usp_p1 line=6 stmtstart=202 
     sqlhandle=0x0300060013e6446b027cbb00c69600000100000000000000
     UPDATE T2 SET COL1 = 3 WHERE COL1 = 1;     
     frame procname=adhoc line=3 stmtstart=44 
     sqlhandle=0x01000600856aa70f503b8104000000000000000000000000
     EXEC usp_p1     
    inputbuf
      BEGIN TRANSACTION
       EXEC usp_p1
   process id=process689978 taskpriority=0 logused=380 
   waitresource=KEY: 6:72057594057457664 (350007a4d329)   
   waittime=5015 ownerId=310462 transactionname=user_transaction 
   lasttranstarted=2005-09-05T11:22:44.077 XDES=0x4d9e258 lockMode=U 
   schedulerid=1 kpid=3024 status=suspended spid=55 sbid=0 ecid=0 
   priority=0 transcount=2 lastbatchstarted=2005-09-05T11:22:44.077 
   lastbatchcompleted=2005-09-05T11:22:44.077 
   clientapp=Microsoft SQL Server Management Studio - Query 
   hostname=TEST_SERVER hostpid=2216 loginname=DOMAIN\user 
   isolationlevel=read committed (2) xactid=310462 currentdb=6 
   lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200
    executionStack
     frame procname=AdventureWorks2008R2.dbo.usp_p2 line=6 stmtstart=200 
     sqlhandle=0x030006004c0a396c027cbb00c69600000100000000000000
     UPDATE T1 SET COL1 = 4 WHERE COL1 = 1;     
     frame procname=adhoc line=3 stmtstart=44 
     sqlhandle=0x01000600d688e709b85f8904000000000000000000000000
     EXEC usp_p2     
    inputbuf
      BEGIN TRANSACTION
        EXEC usp_p2    
  resource-list
   ridlock fileid=1 pageid=20789 dbid=6 objectname=AdventureWorks2008R2.dbo.T2 
   id=lock3136940 mode=X associatedObjectId=72057594057392128
    owner-list
     owner id=process689978 mode=X
    waiter-list
     waiter id=process6891f8 mode=U requestType=wait
   keylock hobtid=72057594057457664 dbid=6 objectname=AdventureWorks2008R2.dbo.T1 
   indexname=nci_T1_COL1 id=lock3136fc0 mode=X 
   associatedObjectId=72057594057457664
    owner-list
     owner id=process6891f8 mode=X
    waiter-list
     waiter id=process689978 mode=U requestType=wait

Profiler の Deadlock Graph イベント

デッドロックに関係するタスクおよびリソースをグラフィカルに表す SQL Server Profiler のイベントです。次の例は、Deadlock Graph イベントがオンになっている場合の SQL Server Profiler からの出力を示しています。

ユーザー プロセスのデッドロックを示す論理フロー図

SQL Server Profiler Deadlock Graph の実行方法の詳細については、「SQL Server Profiler を使用したデッドロックの分析」を参照してください。