デッドロック ガイド

適用対象: SQL Server Azure SQL データベース Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

この記事では、SQL Server データベース エンジンのデッドロックの詳細について説明します。 デッドロック状態は、(多くの場合はマルチステップ トランザクションにおいて) データベース内で競合する同時実行のロックにより発生します。 トランザクション ロックの詳細については、「トランザクション ロックと行のバージョン管理ガイド」を参照してください。

Azure SQL Database でのデッドロックの識別と防止の詳細については、「Azure SQL Database でのデッドロックの分析と防止」を参照してください。

デッドロックについて理解する

デッドロックは、複数のタスクが永続的に相互ブロックすることで発生します。つまり、一方のタスクがロックを試みているリソースに他方のタスクがロックを獲得していて、これが相互に行われるとデッドロックが発生します。 次に例を示します。

• トランザクション A では、行 1 の共有ロックが取得されます。

• トランザクション B では、行 2 の共有ロックが取得されます。

• このとき、トランザクション A で行 2 の排他ロックが要求されますが、トランザクション B が終了し、保持されている行 2 の共有ロックが解放されるまで A はブロックされます。

• このとき、トランザクション B で行 1 の排他ロックが要求されると、トランザクション A が終了し、保持されている行 1 の共有ロックが解放されるまで B はブロックされます。

トランザクション A は、トランザクション B が完了するまで完了できませんが、トランザクション B もトランザクション A によってブロックされます。この状態は、循環依存関係とも呼ばれます。トランザクション A がトランザクション B に依存し、トランザクション B がトランザクション A に依存するため、依存関係が循環します。

デッドロック状態にあるどちらのトランザクションも、外部プロセスからデッドロックを解除されない限り、永久的に待機を続けます。 SQL Server データベース エンジンのデッドロック モニターは、デッドロック状態にあるタスクを定期的にチェックします。 モニターによって循環依存関係が検出されると、一方のタスクがデッドロックの犠牲者として選択され、そのトランザクションはエラーで終了されます。 その結果、もう一方のタスクのトランザクションを完了できます。 トランザクションがエラーで終了したアプリケーションは、そのトランザクションを再試行できます。通常は、デッドロック状態にある一方のトランザクションが完了してからこのトランザクションも完了します。

デッドロックが、通常のブロッキングと混同されることがあります。 あるトランザクションが、別のトランザクションによってロックされているリソースのロックを要求すると、ロックを要求したトランザクションはロックが解放されるまで待機します。 既定では、LOCK_TIMEOUT を設定しない限り、SQL Server のトランザクションはタイムアウトになりません。 この場合、ロックを要求したトランザクションはブロックされているだけで、デッドロック状態が発生しているわけではありません。つまり、ロックを要求したトランザクションは、ロックを所有しているトランザクションをブロックする操作を行っていません。 最終的には、ロックを所有しているトランザクションが完了してロックが解放され、ロックを要求したトランザクションがロックを取得し、続行されます。 デッドロックはほぼ直ちに解決されますが、ブロッキングは理論的には無期限に続く可能性があります。 デッドロックは、「破壊的な支配」と呼ばれることもあります。

デッドロック状態は、リレーショナル データベース管理システムだけでなく、複数のスレッドを使用していれば、どのようなシステムでも発生する可能性があります。また、データベース オブジェクトのロック以外でも発生する可能性があります。 たとえば、マルチスレッド オペレーティング システムの 1 つのスレッドが、メモリのブロックなど、1 つ以上のリソースを取得するとします。 取得しようとしているリソースが別のスレッドに所有されている場合、最初のスレッドはリソースを所有しているスレッドがそのリソースを解放するまで待機することになります。 このとき、待機しているスレッドのことを「そのリソースについて、所有側のスレッドに対する依存関係がある」といいます。 SQL Server データベース エンジンのインスタンスでは、メモリやスレッドなど、データベース以外のリソースを取得するときにデッドロックが発生する可能性があります。

この例では、トランザクション T1 は Part テーブルのロック リソースに関して、トランザクション T2 に依存関係があります。 同様に、Supplier テーブルのロック リソースに関しては、トランザクション T2 がトランザクション T1 に対する依存関係を持っています。 これらの依存関係は相互に働くため、トランザクション T1 と T2 の間でデッドロック状態が発生します。

デッドロック状態は、テーブルがパーティション分割されており、ALTER TABLE の LOCK_ESCALATION 設定が AUTO に設定されている場合にも発生することがあります。 LOCK_ESCALATION を AUTO に設定すると、SQL Server データベース エンジンがテーブル レベルではなく HoBT レベルでテーブル パーティションをロックできるようになるため、コンカレンシーが向上します。 ただし、個々のトランザクションがテーブルのパーティション ロックを保持し、他のトランザクション パーティションのどこかをロックする必要がある場合、デッドロックが発生します。 この種類のデッドロック状態は、 LOCK_ESCALATION を TABLE に設定することで回避できます。 ただし、この設定では、テーブル ロックを待機するようにパーティションに大きな更新を強制することで、コンカレンシーが低下します。

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

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

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

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

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

  

SQL Server データベース エンジンは、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 でアクティブな複数の要求のインターリーブを制御する際に使用します。 詳しくは、「SQL Server Native Client の複数のアクティブな結果セット (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 で実行されているストアド プロシージャで、セッション ミューテックスが取得されています。 このストアド プロシージャの実行に時間がかかると、SQL Server データベース エンジンにより、ストアド プロシージャではユーザーからの入力を待機していると見なされます。 ユーザーの要求 U2 ではセッション ミューテックスが解放されるのを待機しているのに対し、ユーザーは U2 の結果セットが返されるのを待機しています。さらに、U1 では、ユーザー リソースが解放されるのを待機しています。 次の図に、このデッドロック状態を論理的に示します。

    

デッドロックの検出

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

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

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

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

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

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

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

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

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

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

デッドロック情報ツール

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

デッドロック拡張イベント

SQL Server 2012 (11.x) 以降、SQL トレースまたは SQL Profiler では Deadlock Graph イベント クラスではなく xml_deadlock_report 拡張イベント (XEvent) を使用する必要があります。

デッドロック状態が発生した際に、デッドロック グラフを含むすべての xml_deadlock_report XEvent は system_health セッションによって既にキャプチャされています。 system_health セッションは既定で有効になっているため、デッドロック情報をキャプチャするように別の XEvent セッションを構成する必要はありません。 xml_deadlock_report XEvent を使用してデッドロック情報をキャプチャするための追加のアクションは必要ありません。

通常キャプチャされるデッドロック グラフには、3 つの個別のノードがあります。

• victim-list= デッドロック犠牲者プロセス識別子。
• process-list= デッドロックに関係するすべてのプロセスについての情報。
• resource-list= デッドロックに関係するリソースについての情報。

system_health セッション ファイルまたはリング バッファーを開くと、xml_deadlock_report XEvent が記録されている場合、Management Studio は次の例のようにデッドロックに関係するタスクとリソースをグラフィカルに表示します。

次のクエリでは、system_health セッションのリング バッファーによってキャプチャされたすべてのデッドロック イベントを表示できます。

SELECT xdr.value('@timestamp', 'datetime') AS [Date],
       xdr.query('.') AS [Event_Data]
FROM (SELECT CAST ([target_data] AS XML) AS Target_Data
      FROM sys.dm_xe_session_targets AS xt
           INNER JOIN sys.dm_xe_sessions AS xs
               ON xs.address = xt.event_session_address
      WHERE xs.name = N'system_health'
            AND xt.target_name = N'ring_buffer') AS XML_Data
      CROSS APPLY Target_Data.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(xdr)
ORDER BY [Date] DESC;

結果セットは次のとおりです。

次の例では、結果の最初の行にある Event_Data のリンクを選択した後の出力を示します:

<event name="xml_deadlock_report" package="sqlserver" timestamp="2022-02-18T08:26:24.698Z">
  <data name="xml_report">
    <type name="xml" package="package0" />
    <value>
      <deadlock>
        <victim-list>
          <victimProcess id="process27b9b0b9848" />
        </victim-list>
        <process-list>
          <process id="process27b9b0b9848" taskpriority="0" logused="0" waitresource="KEY: 5:72057594214350848 (1a39e6095155)" waittime="1631" ownerId="11088595" transactionname="SELECT" lasttranstarted="2022-02-18T00:26:23.073" XDES="0x27b9f79fac0" lockMode="S" schedulerid="9" kpid="15336" status="suspended" spid="62" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2022-02-18T00:26:22.893" lastbatchcompleted="2022-02-18T00:26:22.890" lastattention="1900-01-01T00:00:00.890" clientapp="SQLCMD" hostname="ContosoServer" hostpid="7908" loginname="CONTOSO\user" isolationlevel="read committed (2)" xactid="11088595" currentdb="5" lockTimeout="4294967295" clientoption1="538968096" clientoption2="128056">
            <executionStack>
              <frame procname="AdventureWorks2022.dbo.p1" line="3" stmtstart="78" stmtend="180" sqlhandle="0x0300050020766505ca3e07008ba8000001000000000000000000000000000000000000000000000000000000">
SELECT c2, c3 FROM t1 WHERE c2 BETWEEN @p1 AND @p1+    </frame>
              <frame procname="adhoc" line="4" stmtstart="82" stmtend="98" sqlhandle="0x020000006263ec01ebb919c335024a072a2699958d3fcce60000000000000000000000000000000000000000">
unknown    </frame>
            </executionStack>
            <inputbuf>
SET NOCOUNT ON
WHILE (1=1)
BEGIN
    EXEC p1 4
END
   </inputbuf>
          </process>
          <process id="process27b9ee33c28" taskpriority="0" logused="252" waitresource="KEY: 5:72057594214416384 (e5b3d7e750dd)" waittime="1631" ownerId="11088593" transactionname="UPDATE" lasttranstarted="2022-02-18T00:26:23.073" XDES="0x27ba15a4490" lockMode="X" schedulerid="6" kpid="5584" status="suspended" spid="58" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2022-02-18T00:26:22.890" lastbatchcompleted="2022-02-18T00:26:22.890" lastattention="1900-01-01T00:00:00.890" clientapp="SQLCMD" hostname="ContosoServer" hostpid="15316" loginname="CONTOSO\user" isolationlevel="read committed (2)" xactid="11088593" currentdb="5" lockTimeout="4294967295" clientoption1="538968096" clientoption2="128056">
            <executionStack>
              <frame procname="AdventureWorks2022.dbo.p2" line="3" stmtstart="76" stmtend="150" sqlhandle="0x03000500599a5906ce3e07008ba8000001000000000000000000000000000000000000000000000000000000">
UPDATE t1 SET c2 = c2+1 WHERE c1 = @p    </frame>
              <frame procname="adhoc" line="4" stmtstart="82" stmtend="98" sqlhandle="0x02000000008fe521e5fb1099410048c5743ff7da04b2047b0000000000000000000000000000000000000000">
unknown    </frame>
            </executionStack>
            <inputbuf>
SET NOCOUNT ON
WHILE (1=1)
BEGIN
    EXEC p2 4
END
   </inputbuf>
          </process>
        </process-list>
        <resource-list>
          <keylock hobtid="72057594214350848" dbid="5" objectname="AdventureWorks2022.dbo.t1" indexname="cidx" id="lock27b9dd26a00" mode="X" associatedObjectId="72057594214350848">
            <owner-list>
              <owner id="process27b9ee33c28" mode="X" />
            </owner-list>
            <waiter-list>
              <waiter id="process27b9b0b9848" mode="S" requestType="wait" />
            </waiter-list>
          </keylock>
          <keylock hobtid="72057594214416384" dbid="5" objectname="AdventureWorks2022.dbo.t1" indexname="idx1" id="lock27afa392600" mode="S" associatedObjectId="72057594214416384">
            <owner-list>
              <owner id="process27b9b0b9848" mode="S" />
            </owner-list>
            <waiter-list>
              <waiter id="process27b9ee33c28" mode="X" requestType="wait" />
            </waiter-list>
          </keylock>
        </resource-list>
      </deadlock>
    </value>
  </data>
</event>

詳細については、「system_health セッションの使用」を参照してください

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

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

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

トレース フラグ 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=2022-02-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=2022-02-05T11:22:42.733
   lastbatchcompleted=2022-02-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=AdventureWorks2022.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=2022-02-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=2022-02-05T11:22:44.077
   lastbatchcompleted=2022-02-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=AdventureWorks2022.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=AdventureWorks2022.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=AdventureWorks2022.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 Profiler のイベントです。 次の例は、Deadlock Graph イベントがオンになっている場合の SQL Profiler からの出力を示しています。

デッドロック イベントの詳細については、「Lock:Deadlock Event Class」(Lock:Deadlock イベント クラス) を参照してください。 SQL Profiler Deadlock Graph の実行について詳しくは、「デッドロック グラフの保存 (SQL Server Profiler)」をご覧ください。

拡張イベントには SQL トレース イベント クラスと同等のものがあります。「SQL トレース イベント クラスと同等の拡張イベントを表示する」を参照してください。 拡張イベントは、SQL トレースよりも推奨されています。

デッドロックの処理

SQL Server データベース エンジンのインスタンスにより、デッドロックの対象となっているトランザクションが判断されると、現在のバッチが終了され、そのトランザクションがロールバックされます。また、このインスタンスからエラー メッセージ 1205 がアプリケーションに返されます。

Your transaction (process ID #52) was deadlocked on {lock | communication buffer | thread} resources with another process and has been chosen as the deadlock victim. Rerun your transaction.

Transact-SQL クエリを送信するアプリケーションはどれも、デッドロックの対象と判断される可能性があるため、アプリケーションには、エラー メッセージ 1205 をトラップできるエラー ハンドラーを実装する必要があります。 このエラーがアプリケーションでトラップされない場合、アプリケーションでは自身のトランザクションがロールバックされたことが認識されず、エラーが発生することがあります。

エラー メッセージ 1205 をトラップするエラー ハンドラーを実装すると、アプリケーションでデッドロックの状況に対応し、デッドロックと関連のあるクエリの自動再送信などの救済措置を講じることができます。 クエリを自動的に再送信すると、ユーザーにデッドロックが発生したことを通知する必要はありません。

アプリケーションは、クエリを再送信する前に少しの間停止する必要があります。 この一時停止により、デッドロックにかかわっている他のトランザクションでは、処理を完了し、デッドロックのサイクルの一因となっているロックを解放することができます。 このようにすることで、再送信されたクエリからロックが要求されたときに、デッドロックが再発する可能性を最小限に抑えられます。

TRY...CATCH を使用したハンドル

TRY...CATCH を使用してデッドロック状態を処理します。 1205 デッドロックの対象エラーは、 CATCH ブロックによってキャッチされ、スレッドのロックが解除されるまでトランザクションをロールバックできます。

詳細については、「デッドロック状態の処理」を参照してください。

デッドロックを最小化する

デッドロックを完全に回避することはできませんが、コーディング上の一定の規則に従うことにより、デッドロックが発生する可能性を最小限に抑えることができます。 デッドロックの発生数を抑えると、以下の理由から、トランザクションのスループットが向上し、システムのオーバーヘッドが減少します。

• ロールバック (トランザクションが実行したすべての処理の取り消し) の対象となるトランザクションの数が減少します。
• デッドロック後にロールバックされたトランザクションをアプリケーションが再実行する場合、対象となるトランザクションの数が減少します。

デッドロックを最小限に抑えるには、次の手順を実行します。

• 同じ順序でオブジェクトにアクセスします。
• トランザクション内でのユーザーとのやり取りを避けます。
• トランザクションを短くして 1 つのバッチ内に収めます。
• 低い分離レベルを使用します。
• 行のバージョン管理に基づく分離レベルを使用します。
  • 行のバージョン管理を使用するには、READ_COMMITTED_SNAPSHOT データベース オプションを ON に設定して、READ COMMITTED トランザクションを有効にします。
  • スナップショット分離を使用します。
• バインドされた接続を使用します。

同じ順序でのオブジェクトへのアクセス

すべての同時実行トランザクションが同じ順序でオブジェクトにアクセスすると、デッドロックの発生する可能性は低くなります。 たとえば、2 つの同時実行トランザクションが Supplier テーブルでロックを取得してから、Part テーブルでロックを取得する場合、一方のトランザクションは、もう一方のトランザクションが完了するまで Supplier テーブルでブロックされます。 1 番目のトランザクションがコミットまたはロールバックされた後に 2 番目のトランザクションが続行されるので、デッドロックは発生しません。 すべてのデータ変更にストアド プロシージャを使用すると、オブジェクトへのアクセス順序を統一できます。

トランザクション内でのユーザーとのやり取りの回避

ユーザーとのやり取りを含むトランザクションを避けます。ユーザーの介入なしにバッチを実行すれば、ユーザーが手動でクエリに応答する場合、つまりアプリケーションによるパラメーター入力要求に手動で応答する場合などよりはるかに高速に処理できます。 たとえば、トランザクションがユーザーの入力を待機しているときに、ユーザーが昼食に出ていたり、週末で家に帰っていると、トランザクションの完了は大幅に延期されます。 トランザクションが保持するロックを解除するにはトランザクションのコミットまたはロールバックが必要なので、このような状況ではシステムのスループットが低下してしまいます。 デッドロック状態が発生しないとしても、トランザクションが完了するまで、同じリソースにアクセスしている他のトランザクションはブロックされます。

トランザクションを短くして 1 つのバッチ内に収める

デッドロックは主に、同じデータベースで長時間動作するトランザクションがいくつか同時に実行されている場合に発生します。 トランザクションが長くなれば、排他ロックまたは更新ロックが長時間になり、他の処理をブロックしてしまうので、デッドロックが発生する可能性が高くなります。

トランザクションを 1 つのバッチ内に収めると、トランザクション実行時のネットワーク経由のやり取りを最小限に抑えられ、トランザクションの完了やロックの解除が遅延する可能性を低くすることができます。

更新ロックの詳細については、「トランザクションのロックおよび行のバージョン管理ガイド」を参照してください。

低い分離レベルの使用

低い分離レベルでトランザクションが実行可能かどうかを調べます。 READ COMMITTED を実装すると、トランザクションは、別のトランザクションが以前に読み取った (変更されていない) データを読み取ることができるので、最初のトランザクションが完了するまで待機する必要がありません。 READ COMMITTED などの低い分離レベルでは、SERIALIZABLE などの高い分離レベルの場合よりも共有ロックの保持時間が短くなります。 これにより、ロックの競合が減少します。

行のバージョン管理に基づく分離レベルの使用

READ_COMMITTED_SNAPSHOT データベース オプションが ON に設定されている場合、READ COMMITTED 分離レベルで実行されているトランザクションでは、読み取り操作中に、共有ロックではなく行のバージョン管理を使用します。

スナップショット分離レベルでも行のバージョン管理を使用します。行のバージョン管理では、読み取り操作中に共有ロックを使用しません。 スナップショット分離レベルでトランザクションを実行する前に、ALLOW_SNAPSHOT_ISOLATION データベース オプションを ON に設定する必要があります。

これらの分離レベルを実装すると、読み取り操作と書き込み操作の間に発生する可能性があるデッドロックを最小限に抑えることができます。

バインドされた接続の使用

バインドされた接続を使用すると、同じアプリケーションによって開かれた複数の接続が相互に協調動作できます。 最初の接続が取得したロックと同じように次の接続が取得したロックも保持されます。また、その逆の場合も同様に保持されます。 したがって、相互にブロックすることはありません。

トランザクションを停止する

デッドロックシナリオでは、対象トランザクションは自動的に停止され、ロールバックされます。 デッドロック状態シナリオでトランザクションを停止する必要はありません。

デッドロックの原因

デッドロック状態を引き起こすには、2 つのセッションを AdventureWorksLT2019 データベースに接続する必要があります。 これらのセッションはセッション A とセッション B と見なします。SQL Server Management Studio (SSMS) で 2 つのクエリ ウィンドウを作成するだけで、これら 2 つのセッションを作成できます。

セッション A で、次の Transact-SQL を実行します。 このコードは、明示的なトランザクションを開始し、SalesLT.Product テーブルを更新する 1 つのステートメントを実行します。 これを行うために、トランザクションはテーブル の 1 つの行で 更新 (U) ロックSalesLT.Product を取得し、これを排他 (X) ロックに変換します。 トランザクションは開いたままにします。

BEGIN TRANSACTION;

UPDATE SalesLT.Product
    SET SellEndDate = SellEndDate + 1
WHERE Color = 'Red';

ここで、セッション B で、次の Transact-SQL を実行します。 このコードは、トランザクションを明示的に開始しません。 代わりに、オートコミット トランザクション モードで動作します。 このステートメントは、SalesLT.ProductDescription テーブルを更新します。 この更新により、SalesLT.ProductDescription テーブルの 72 行の更新 (U) ロックが解除されます。 クエリは、テーブルを含む他のテーブルに SalesLT.Product 結合されます。

UPDATE SalesLT.ProductDescription
    SET Description = Description
FROM SalesLT.ProductDescription AS pd
     INNER JOIN SalesLT.ProductModelProductDescription AS pmpd
         ON pd.ProductDescriptionID = pmpd.ProductDescriptionID
     INNER JOIN SalesLT.ProductModel AS pm
         ON pmpd.ProductModelID = pm.ProductModelID
     INNER JOIN SalesLT.Product AS p
         ON pm.ProductModelID = p.ProductModelID
WHERE p.Color = 'Silver';

この更新を完了するには、セッション B は、セッション A によってロックされている行を含め、SalesLT.Product テーブル の行に対する共有 (S) ロックが必要です。セッション B は SalesLT.Product でブロックされます。

セッション A に戻ります。次の Transact-SQL ステートメントを実行します。 これにより、開いているトランザクションの一部として 2 番目の UPDATE ステートメントが実行されます。

UPDATE SalesLT.ProductDescription
    SET Description = Description
FROM SalesLT.ProductDescription AS pd
     INNER JOIN SalesLT.ProductModelProductDescription AS pmpd
         ON pd.ProductDescriptionID = pmpd.ProductDescriptionID
     INNER JOIN SalesLT.ProductModel AS pm
         ON pmpd.ProductModelID = pm.ProductModelID
     INNER JOIN SalesLT.Product AS p
         ON pm.ProductModelID = p.ProductModelID
WHERE p.Color = 'Red';

セッション A の 2 番目の更新ステートメントは、SalesLT.ProductDescription のセッション B によってブロックされます。

セッション A とセッション B が相互にブロックし合うようになりました。 どちらのトランザクションも、それぞれが他方によってロックされているリソースを必要とするため、続行できません。

数秒後、デッドロック モニターは、セッション A とセッション B のトランザクションが相互にブロックし合っており、どちらも進行できないことを識別します。 デッドロックが発生し、セッション A がデッドロックの対象として選択されているはずです。 セッション B が正常に完了します。 セッション A に次の例のようなテキストを含むエラー メッセージが表示されます。

Msg 1205, Level 13, State 51, Line 7
Transaction (Process ID 51) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

デッドロック状態が発生しない場合は、サンプル データベースで READ_COMMITTED_SNAPSHOT が有効になっていることを確認します。 デッドロック状態は任意のデータベース構成で発生する可能性がありますが、この例では READ_COMMITTED_SNAPSHOT を有効にする必要があります。

その後、system_health拡張イベント セッションのring_buffer ターゲットでデッドロックの詳細を表示できます。これは、SQL Server で既定で有効およびアクティブになっています。 次のクエリがあるとします。

WITH cteDeadLocks ([Deadlock_XML])
AS (SELECT CAST (target_data AS XML) AS [Deadlock_XML]
    FROM sys.dm_xe_sessions AS xs
         INNER JOIN sys.dm_xe_session_targets AS xst
             ON xs.[address] = xst.event_session_address
    WHERE xs.[name] = 'system_health'
          AND xst.target_name = 'ring_buffer')
SELECT x.Graph.query('(event/data/value/deadlock)[1]') AS Deadlock_XML,
       x.Graph.value('(event/data/value/deadlock/process-list/process/@lastbatchstarted)[1]', 'datetime2(3)') AS when_occurred,
       DB_Name(x.Graph.value('(event/data/value/deadlock/process-list/process/@currentdb)[1]', 'int')) AS DB --Current database of the first listed process
FROM (SELECT Graph.query('.') AS Graph
      FROM cteDeadLocks AS c
      CROSS APPLY c.[Deadlock_XML].nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS Deadlock_Report(Graph)) AS x
ORDER BY when_occurred DESC;

HYPERLINK として表示されるセルを選択すると、SSMS 内の Deadlock_XML 列に XML を表示できます。 この出力を .xdl ファイルとして保存し、閉じてから SSMS で .xdl ファイルを開き直して、視覚的なデッドロック グラフを表示します。 デッドロック グラフは次の画像のようになります。

最適化されたロックとデッドロック

適用対象: Azure SQL Database

最適化されたロックでは、排他的 TID ロックを含むデッドロックのレポート方法を変更するロックのしくみに対して、別の方法が導入されました。 デッドロック レポート <resource-list>の各リソースの下で、各 <xactlock> 要素は、基になるリソースと、デッドロックの各メンバーのロックに関する特定の情報をレポートします。

最適化されたロックが有効になっている次の例を考えてみましょう。

CREATE TABLE t2
(
    a INT PRIMARY KEY NOT NULL,
    b INT NULL
);

INSERT INTO t2
VALUES (1, 10),
(2, 20),
(3, 30);
GO

2 つのセッションで次の Transact-SQL コマンドを実行すると、テーブル t2にデッドロックが発生します。

セッション 1:

--session 1
BEGIN TRANSACTION foo;

UPDATE t2
    SET b = b + 10
WHERE a = 1;

セッション 2:

--session 2:
BEGIN TRANSACTION bar;

UPDATE t2
    SET b = b + 10
WHERE a = 2;

セッション 1:

--session 1:
UPDATE t2
    SET b = b + 100
WHERE a = 2;

セッション 2:

--session 2:
UPDATE t2
    SET b = b + 20
WHERE a = 1;

UPDATE [ステートメントが競合するこのシナリオでは、デッドロックが発生します。 この場合、キーロック リソースでは、各セッションが独自の TID で X ロックを保持し、他の TID で S ロックを待機しているため、デッドロックが発生します。 デッドロック レポートとしてキャプチャされた次の XML には、最適化されたロックに固有の要素と属性が含まれています。

関連するコンテンツ

• 拡張イベントの概要
• sys.dm_tran_locks (Transact-SQL)
• Deadlock Graph イベント クラス
• Read Repeatable 分離レベルでのデッドロック
• Lock:Deadlock Chain イベント クラス
• Lock:Deadlock イベント クラス
• SET DEADLOCK_PRIORITY (Transact-SQL)
• Azure SQL Database でデッドロックを分析および防止する
• SQL Server Management Studio (SSMS) でデッドロック ファイルを開く、表示する、印刷する