デッドロック ガイド
適用対象: 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 でアクティブな複数の要求のインターリーブを制御する際に使用します。 詳しくは、「複数のアクティブな結果セット (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 の範囲の整数値を設定することができます。 DEADLOCK_PRIORITY の既定値は NORMAL です。 2 つのセッションのデッドロックの優先度が異なる場合、優先度の低いセッションがデッドロックの対象として選択されます。 2 つのセッションのデッドロックの優先度が同じ場合、ロールバックに最もコストのかからないトランザクションを含むセッションがデッドロックの対象として選択されます。 デッドロック サイクルに関連するセッションのデッドロックの優先度とコストが同じ場合、対象はランダムに選択されます。
CLR を使用して作業する場合、デッドロック モニターでは、マネージド プロシージャ内でアクセスされる同期リソース (モニター、リーダー ロックとライター ロック、およびスレッド結合) のデッドロックが自動的に検出されます。 ただし、デッドロックは、デッドロックの対象として選択されたプロシージャに例外をスローすることによって解決されます。 デッドロックの対象が現在所有しているリソースは、この例外により自動的に解放されないことに注意してください。つまり、リソースは明示的に解放する必要があります。 例外の動作と一貫性があるため、デッドロックの対象の特定に使用された例外は、キャッチおよび破棄できます。
デッドロック情報ツール
デッドロック情報を表示するために、SQL Server データベース エンジンには、system_health
xEvent セッション形式の監視ツール、2 種類のトレース フラグ、SQL Profiler の Deadlock Graph イベントが用意されています。
Note
このセクションには拡張イベント、トレース フラグ、トレースに関する情報が含まれていますが、デッドロック情報をキャプチャするための推奨される方法は Deadlock 拡張イベントです。
デッドロック拡張イベント
SQL Server 2012 (11.x)以降、SQL トレースまたは SQL Profiler ではデッドロック グラフ イベント クラスではなく xml_deadlock_report
拡張イベント (xEvent) を使用する必要があります。
また、SQL Server 2012 (11.x)以降では、デッドロックが発生したときに、デッドロック グラフを含むすべての 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 を使用しないようにしてください。 これらのトレースフラグを使用すると、パフォーマンスの問題が発生する可能性があります。 代わりに、Deadlock 拡張イベントを使用して、必要な情報をキャプチャします。
次の表は、トレース フラグ 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 の line )。 デッドロックが発生したときに実行されていた、現在のステートメントのバッチの行番号が表示されます。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 デッドロックの優先度を表します。 SQL Server データベース エンジンでは、コンカレンシーを向上させるために、短期間でデッドロックの優先度が変更されることがあります。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 ツリー) の 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 (インデックス キー ハッシュ値) として表されます。 たとえば、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 ツリーを表します。 |
このトレース フラグに限定されるリソース属性はありません。 | このトレース フラグに限定されるリソース属性はありません。 |
トレース フラグ 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 からの出力を示しています。
重要
SQL Profiler はトレースを作成します。トレースは 2016 年に非推奨となり、拡張イベントに置き換えられました。 拡張イベントのパフォーマンス オーバーヘッドはトレースよりもはるかに少なく、はるかに構成が容易です。 トレースの代わりに拡張イベントのデッドロック イベント を使用することをご検討ください。
デッドロック イベントの詳細については、「Lock:Deadlock Event Class」(Lock:Deadlock イベント クラス) を参照してください。 SQL Profiler Deadlock Graph の実行について詳しくは、「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 をトラップするエラー ハンドラーを実装すると、アプリケーションでデッドロックの状況に対応し、デッドロックと関連のあるクエリの自動再送信などの救済措置を講じることができます。 クエリを自動的に再送信すると、ユーザーにデッドロックが発生したことを通知する必要はありません。
アプリケーションは、クエリを再送信する前に少しの間停止する必要があります。 この一時停止により、デッドロックにかかわっている他のトランザクションでは、処理を完了し、デッドロックのサイクルの一因となっているロックを解放することができます。 このようにすることで、再送信されたクエリからロックが要求されたときに、デッドロックが再発する可能性を最小限に抑えられます。
デッドロックを最小化する
デッドロックを完全に回避することはできませんが、コーディング上の一定の規則に従うことにより、デッドロックが発生する可能性を最小限に抑えることができます。 デッドロックの発生数を抑えると、以下の理由から、トランザクションのスループットが向上し、システムのオーバーヘッドが減少します。
- ロールバック (トランザクションが実行したすべての処理の取り消し) の対象となるトランザクションの数が減少します。
- デッドロック後にロールバックされたトランザクションをアプリケーションが再実行する場合、対象となるトランザクションの数が減少します。
デッドロックを最小限に抑えるには、次の手順を実行します。
- 同じ順序でオブジェクトにアクセスします。
- トランザクション内でのユーザーとのやり取りを避けます。
- トランザクションを短くして 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 分離レベルで実行されているトランザクションでは、読み取り操作中に、共有ロックではなく行のバージョン管理を使用します。
Note
一部のアプリケーションは、READ COMMITTED 分離レベルのロックおよびブロックの動作に依存します。 このようなアプリケーションについては、このオプションを使用する前に多少の変更が必要になります。
スナップショット分離レベルでも行のバージョン管理を使用します。行のバージョン管理では、読み取り操作中に共有ロックを使用しません。 スナップショット分離レベルでトランザクションを実行する前に、ALLOW_SNAPSHOT_ISOLATION
データベース オプションを ON
に設定する必要があります。
これらの分離レベルを実装すると、読み取り操作と書き込み操作の間に発生する可能性があるデッドロックを最小限に抑えることができます。
バインドされた接続の使用
バインドされた接続を使用すると、同じアプリケーションによって開かれた複数の接続が相互に協調動作できます。 最初の接続が取得したロックと同じように次の接続が取得したロックも保持されます。また、その逆の場合も同様に保持されます。 したがって、相互にブロックすることはありません。
トランザクションを停止する
デッドロックシナリオでは、対象トランザクションは自動的に停止され、ロールバックされます。 デッドロック シナリオでトランザクションを停止する必要はありません。
デッドロックの原因
Note
この例は、READ_COMMITTED_SNAPSHOT が有効になっているときに、既定のスキーマとデータを使って AdventureWorksLT2019
データベースで動作します。 このサンプルをダウンロードするには、AdventureWorks サンプル データベースにアクセスしてください。
デッドロックを引き起こすには、2 つのセッションを AdventureWorksLT2019
データベースに接続する必要があります。 これらのセッションはセッション A とセッション B と見なします。SQL Server Management Studio (SSMS) で 2 つのクエリ ウィンドウを作成するだけで、これら 2 つのセッションを作成できます。
セッション A で、次の Transact-SQL を実行します。 このコードは、明示的なトランザクションを開始し、SalesLT.Product
テーブルを更新する 1 つのステートメントを実行します。 これを行うために、トランザクションはテーブル の 1 つの行で 更新 (U) ロックSalesLT.Product
を取得し、これを排他 (X) ロックに変換します。 トランザクションは開いたままにします。
BEGIN TRAN
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
JOIN SalesLT.ProductModelProductDescription as pmpd on
pd.ProductDescriptionID = pmpd.ProductDescriptionID
JOIN SalesLT.ProductModel as pm on
pmpd.ProductModelID = pm.ProductModelID
JOIN SalesLT.Product as p on
pm.ProductModelID=p.ProductModelID
WHERE p.Color = 'Silver';
この更新を完了するには、セッション B は、SalesLT.Product
セッション A によってロックされている行を含め、テーブル の行に対する共有 (S) ロックが必要です。セッション B は SalesLT.Product
でブロックされます。
セッション A に戻ります。次の Transact-SQL ステートメントを実行します。 これにより、開いているトランザクションの一部として 2 番目の UPDATE ステートメントが実行されます。
UPDATE SalesLT.ProductDescription SET Description = Description
FROM SalesLT.ProductDescription as pd
JOIN SalesLT.ProductModelProductDescription as pmpd on
pd.ProductDescriptionID = pmpd.ProductDescriptionID
JOIN SalesLT.ProductModel as pm on
pmpd.ProductModelID = pm.ProductModelID
JOIN SalesLT.Product as p on
pm.ProductModelID=p.ProductModelID
WHERE p.Color = 'Red';
セッション A の 2 番目の更新ステートメントは、の セッション BSalesLT.ProductDescription
によってブロックされます。
セッション 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 [Deadlock_XML] = CAST(target_data AS 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
Deadlock_XML = x.Graph.query('(event/data/value/deadlock)[1]')
, when_occurred = x.Graph.value('(event/data/value/deadlock/process-list/process/@lastbatchstarted)[1]', 'datetime2(3)')
, DB = DB_Name(x.Graph.value('(event/data/value/deadlock/process-list/process/@currentdb)[1]', 'int')) --Current database of the first listed process
FROM (
SELECT Graph.query('.') AS Graph
FROM cteDeadLocks c
CROSS APPLY c.[Deadlock_XML].nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS Deadlock_Report(Graph)
) AS x
ORDER BY when_occurred desc;
ハイパーリンクとして表示されるセルを選択すると、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 つのセッションで次の TSQL コマンドを実行すると、テーブル t2
にデッドロックが発生します。
セッション 1:
--session 1
BEGIN TRAN foo;
UPDATE t2 SET b = b+ 10 WHERE a = 1;
セッション 2:
--session 2:
BEGIN TRAN 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 には、最適化されたロックに固有の要素と属性が含まれています。