Azure SQL Database でデッドロックを分析および防止する
適用対象: 
Azure SQL Database
この記事では、Azure SQL Database でデッドロックを特定し、デッドロック グラフとクエリ ストアを使用してデッドロック内のクエリを識別し、デッドロックの再発を防ぐための変更をプランしテストする方法について説明します。
この記事では、ロック競合によるデッドロックの特定と分析に焦点を当てます。 その他の種類のデッドロックの詳細については、「デッドロックが発生する可能性のあるリソース」を参照してください。
Azure SQL Database でデッドロックが発生するしくみ
Azure SQL Database の新しいデータベースではそれぞれ、READ COMMITTED SNAPSHOT (RCSI) データベース設定が既定で有効になっています。 行のバージョン管理を使用してコンカレンシーを高める RCSI のもとで、データを読み取るセッションとデータを書き込むセッション間のブロックが最小限に抑えられます。 ただし、それでも、次の理由によって Azure SQL Database のデータベースでブロックやデッドロックが発生する場合があります。
- データを変更するクエリが互いをブロックする場合があります。
- ブロックを増加させる分離レベルでクエリが実行される場合があります。 分離レベルは、Transact-SQL のクライアント ライブラリ メソッド、クエリ ヒント、または SET ステートメントを使用して指定できます。
- RCSI が無効になっている場合があります。この場合、データベースでは共有 (S) ロックを使用して、READ COMMITTED 分離レベルで実行される SELECT ステートメントを保護します。 これにより、ブロックとデッドロックが増加する場合があります。
デッドロックの例
デッドロックは、2 つ以上のタスクが相互に永続的なブロックを行っている場合に発生します。これは、各タスクが、他のタスクがロックしようとしているリソースをロックしているためです。 デッドロックは循環依存関係とも呼ばれ、2 タスクのデッドロックの場合、トランザクション A はトランザクション B に依存し、トランザクション B はトランザクション A に依存するため、依存関係が循環します。
次に例を示します。
- セッション A は明示的なトランザクションを開始し、排他 (X) ロックに変換されたテーブル
SalesLT.Productの 1 行に対して更新 (U ) ロックを取得する update ステートメントを実行します。 - セッション B は、
SalesLT.ProductDescriptionテーブルを変更する update ステートメントを実行します。 update ステートメントはテーブルに結合して、SalesLT.Product更新する正しい行を見つけます。- セッション B は、
SalesLT.ProductDescriptionテーブルの 72 行の更新 (U) ロックを取得します。 - セッション B には、
SalesLT.Productセッション A によってロックされた行を含め、テーブル の行に対する共有ロックが必要です。セッション B はSalesLT.Productでブロックされます。
- セッション B は、
- セッション A はトランザクションを続行し、
SalesLT.ProductDescriptionテーブルに対して更新を実行するようになりました。 セッション A は、SalesLT.ProductDescriptionのセッション B によってブロックされます。
デッドロック内のすべてのトランザクションは、参加しているトランザクションの 1 つがロールバックされない限り (セッションが終了したなど)、無期限に待機します。
データベース エンジンのデッドロック モニターは、デッドロック状態にあるタスクを定期的にチェックします。 デッドロック モニターが循環依存性を検出すると、いずれかのタスクを対象として選択し、そのトランザクションがエラー1205「トランザクション (プロセス ID N) が別のプロセスとのロック リソースでデッドロック発生したため、デッドロック状態として選択されました。」で終了します。 トランザクションを再実行してください。この方法でデッドロックを解消すると、デッドロック内の他のタスクがトランザクションを完了できるようになります。
注意
デッドロックの対象を選択するための基準詳細については、この記事の「デッドロック プロセスの一覧」セクションを参照してください。
デッドロックの対象として選択されたトランザクションを持つアプリケーションは、トランザクションを再試行する必要があります。トランザクションは、通常、デッドロックに関連する他のトランザクションが終了した後に完了します。
同じデッドロックの再発生を回避するには、再試行の前に短いランダム化された遅延を取り入れることをお勧めします。 詳細については、「一時的なエラーの再試行ロジック」を設計する方法を参照してください。
Azure SQL データベースの既定の分離レベル
Azure SQL Database の新しいデータベースでは、既定で読み取りコミット済みスナップショット (RCSI) が有効になります。 RCSI は、読み取りコミット済み分離レベルの動作を変更し、行バージョン管理を使用して、SELECT ステートメントに共有 (S) ロックを使用せずにステートメント レベルの一貫性を提供します。
RCSI が有効になっている場合:
- データを読み取るステートメントは、データを変更するステートメントをブロックしません。
- データを変更するステートメントは、データを読み取るステートメントをブロックしません。
Azure SQL Database の新しいデータベースでは、スナップショット分離レベルも既定で有効になっています。 スナップショット分離は行ベースの追加の分離レベルであり、トランザクションレベルのデータ整合性を提供し、行のバージョンを使用して更新する行を選択します。 スナップショット分離を使用するには、クエリまたは接続でそのトランザクション分離レベルを明示的に SNAPSHOT に設定する必要があります。 これは、データベースでスナップショット分離が有効である場合にのみ行うことができます。
RCSI とスナップショット分離の一方または両方が有効になっているかどうかは、Transact-SQL を使用して識別できます。 Azure SQL Database のデータベースに接続し、次のクエリを実行します。
SELECT name, is_read_committed_snapshot_on, snapshot_isolation_state_desc
FROM sys.databases
WHERE name = DB_NAME();
GO
RCSI が有効な場合、is_read_committed_snapshot_on 列は値 1 を返します。 スナップショット分離が有効な場合、snapshot_isolation_state_desc 列は値 ON を返します。
Azure SQL Database のデータベースの RCSI が無効になっている場合は、RCSI が無効になった理由を調べてから再度有効にします。 アプリケーション コードは、データを書き込むクエリによってデータを読み取るクエリがブロックされることを想定して記述されている可能性があり、RCSI が有効になっている場合に競合状態による誤った結果が発生する可能性があります。
デッドロック イベントの解釈
デッドロック イベントは、Azure SQL Database のデッドロック マネージャーがデッドロックを検出し、トランザクションを対象として選択した後に生成されます。 つまり、デッドロックのアラートを設定すると、個々のデッドロックが解決された後に通知が発生します。 そのデッドロックに対して実行する必要があるユーザー アクションはありません。 アプリケーションは、エラー1205 「トランザクション (プロセス ID N) が別のプロセスとのロック リソースでデッドロック状態になり、デッドロックの原因として選択されました。」 を受け取った後に自動的に続行されるように、再試行ロジックを含むように記述する必要があります。 トランザクションを再実行してください。"
ただし、デッドロックが再発する可能性があるため、アラートを設定すると便利です。 デッドロック アラートを使用すると、データベースで繰り返しデッドロックのパターンが発生しているかどうかを調査できます。その場合は、デッドロックが再発しないようにアクションを実行することを選択できます。 アラートの詳細については、この記事の「監視とデッドロックに関するアラート」セクションを参照してください。
デッドロックを防ぐための上位のメソッド
デッドロックの再発を防ぐための最もリスクの低いアプローチは、通常、デッドロックに関連するクエリを最適化するために非クラスター化インデックスを調整することです。
- 非クラスター化インデックスのチューニングではクエリ コード自体を変更する必要がなく、Transact-SQL を書き換えるときにユーザー エラーが発生して誤ったデータがユーザーに返されるリスクが軽減されるため、このアプローチではリスクが低くなります。
- 効果的な非クラスター化インデックスのチューニングは、クエリが読み取りと変更を行うデータをより効率的に見つけるのに役立ちます。 クエリがアクセスする必要があるデータ量を減らすことで、ブロックの可能性が低くなり、デッドロックを防止できることがよくあります。
場合によっては、クラスター化インデックスを作成またはチューニングすることで、ブロックとデッドロックを減らすことができます。 クラスター化インデックスは、すべての非クラスター化インデックス定義に含まれているため、クラスター化インデックスの作成または変更は、既存の非クラスター化インデックスを持つ大規模なテーブルに対する IO の負荷が高く、時間のかかる操作になる可能性があります。 詳細については、「クラスター化インデックスの設計ガイドライン」を参照してください。
インデックスのチューニングがデッドロックの防止に成功しない場合は、他の方法を使用できます。
- デッドロックに関連するクエリの 1 つに特定のプランが選択されている場合にのみデッドロックが発生する場合は、クエリ ストアを使用してクエリ プランを強制すると、デッドロックの再発を防ぐ可能性があります。
- デッドロックに関連する 1 つ以上のトランザクションに対して Transact-SQL を書き直すことも、デッドロックを防ぐのに役立ちます。 明示的なトランザクションを小さなトランザクションに分割するには、同時に変更が発生したときにデータの有効性を確認するために、慎重なコーディングとテストが必要です。
これらの各方法の詳細については、この記事の「デッドロックが再発しないようにする」セクションを参照してください。
デッドロックの監視とアラート
この記事では、AdventureWorksLTのサンプル データベースを使用して、デッドロックのアラートの設定、デッドロックの例の原因、デッドロックの例のデッドロック グラフの分析、デッドロックの再発を防ぐための変更のテストを行います。
この記事では、SQL Server Management Studio (SSMS) クライアントを使用します。これには、対話型のビジュアル モードでデッドロック グラフを表示する機能が含まれています。 Azure Data Studio などの他のクライアントを使用して例に従うことができますが、デッドロック グラフは XML としてしか表示できない場合があります。
AdventureWorksLT データベースを作成する
例に従って、Azure SQL Databaseに新しいデータベースを作成し、データ ソースとして [サンプル データ] を選択します。
Azure portal、Azure CLI、または PowerShell を使用して AdventureWorksLT を作成する方法の詳細については、「クイック スタート: Azure SQL データベース 単一データベースの作成」で、任意の方法を選択してください。
Azure portal でデッドロック アラートを設定する
デッドロック イベントのアラートを設定するには、「Azure portal を使用して Azure SQL Database および Azure Synapse Analytics のアラートを作成する」の記事の手順に従います。
アラートのシグナル名として [デッドロック] を選択します。 電子メール/SMS/プッシュ/音声アクションの種類など、選択した方法を使用して通知するようにアクショングループを構成します。
拡張イベントを使用して Azure SQL Database でデッドロック グラフを収集する
デッドロック グラフは、デッドロックに関連するプロセスとロックに関する豊富なソースです。 Azure SQL Database で拡張イベント (XEvents) を使用してデッドロック グラフを収集するには、sqlserver.database_xml_deadlock_report イベントをキャプチャします。
リング バッファー ターゲットまたはイベント ファイル ターゲットのいずれかを使用して、XEvent でデッドロック グラフを収集できます。 適切なターゲットの種類を選択するための考慮事項を次の表にまとめます。
| アプローチ | メリット | 考慮事項 | 使用シナリオ |
|---|---|---|---|
| リング バッファー ターゲット |
|
|
|
| イベント ファイル ターゲット |
|
|
|
使用するターゲット タイプを選択します。
リング バッファー ターゲットは便利で設定が簡単ですが、容量が限られているため、古いイベントが失われる可能性があります。 リング バッファーはイベントをストレージに永続化せず、XEvents セッションが停止するとリング バッファー ターゲットはクリアされます。 つまり、収集された XEvent は、フェールオーバーなどの何らかの理由でデータベース エンジンが再起動したときに使用できなくなります。 イベント ファイル ターゲットへの XEvents セッションをすぐに設定できない場合、リング バッファー ターゲットは学習と短期的なニーズに最適です。
このサンプル コードでは、リング バッファー ターゲット を使用してメモリ内のデッドロック グラフをキャプチャする XEvents セッションを作成します。 リング バッファー ターゲットに許可される最大メモリは 4 MB で、フェールオーバー後など、データベースがオンラインになるとセッションが自動的に実行されます。
リング バッファー ターゲットに書き込む sqlserver.database_xml_deadlock_report イベントの XEvents セッションを作成して開始するには、データベースに接続し、次の Transact-SQL を実行します。
CREATE EVENT SESSION [deadlocks] ON DATABASE
ADD EVENT sqlserver.database_xml_deadlock_report
ADD TARGET package0.ring_buffer
WITH (STARTUP_STATE=ON, MAX_MEMORY=4 MB)
GO
ALTER EVENT SESSION [deadlocks] ON DATABASE
STATE = START;
GO
AdventureWorksLT でデッドロックが発生する
注意
この例は、RCSI が有効になっているときに、既定のスキーマとデータを使って AdventureWorksLT データベースで動作します。 データベースを作成する手順については、「AdventureWorksLT データベースの作成」を参照してください。
デッドロックを引き起こすには、2 つのセッションを AdventureWorksLT データベースに接続する必要があります。 これらのセッションを セッション A および セッション B と呼びます。
セッション 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 がデッドロックの対象として選択されているはずです。 セッション A に次のようなテキストを含むエラー メッセージが表示されます。
メッセージ 1205、レベル 13、状態 51、行 7 トランザクション (プロセス ID 91) が、別のプロセスとのロック リソースでデッドロックされ、デッドロックの対象として選択されました。 トランザクションを再実行してください。
セッション B は正常に完了します。
Azure portal でデッドロック アラートを設定する場合は、デッドロックが発生した直後に通知を受け取る必要があります。
XEvents セッションからのデッドロック グラフ表示
デッドロックを収集するように XEvents セッションを設定して、セッションの開始後にデッドロックが発生した場合は、デッドロック グラフの対話式グラフィック表示とデッドロック グラフの XML を表示できます。
リング バッファー ターゲットおよびイベント ファイル ターゲットのデッドロック情報を取得するには、さまざまな方法を使用できます。 XEvents セッションに使用したターゲットを選択します。
リング バッファーへの書き込みを行う XEvents セッションを設定する場合は、次の Transact-SQL を使用してデッドロック情報を照会できます。 クエリを実行する前に、@tracename の値を XEvents セッションの名前に置き換えます。
DECLARE @tracename sysname = N'deadlocks';
WITH ring_buffer AS (
SELECT CAST(target_data AS XML) as rb
FROM sys.dm_xe_database_sessions AS s
JOIN sys.dm_xe_database_session_targets AS t
ON CAST(t.event_session_address AS BINARY(8)) = CAST(s.address AS BINARY(8))
WHERE s.name = @tracename and
t.target_name = N'ring_buffer'
), dx AS (
SELECT
dxdr.evtdata.query('.') as deadlock_xml_deadlock_report
FROM ring_buffer
CROSS APPLY rb.nodes('/RingBufferTarget/event[@name=''database_xml_deadlock_report'']') AS dxdr(evtdata)
)
SELECT
d.query('/event/data[@name=''deadlock_cycle_id'']/value').value('(/value)[1]', 'int') AS [deadlock_cycle_id],
d.value('(/event/@timestamp)[1]', 'DateTime2') AS [deadlock_timestamp],
d.query('/event/data[@name=''database_name'']/value').value('(/value)[1]', 'nvarchar(256)') AS [database_name],
d.query('/event/data[@name=''xml_report'']/value/deadlock') AS deadlock_xml,
LTRIM(RTRIM(REPLACE(REPLACE(d.value('.', 'nvarchar(2000)'),CHAR(10),' '),CHAR(13),' '))) as query_text
FROM dx
CROSS APPLY deadlock_xml_deadlock_report.nodes('(/event/data/value/deadlock/process-list/process/inputbuf)') AS ib(d)
ORDER BY [deadlock_timestamp] DESC;
GO
デッドロックグラフを XML で表示して保存する
デッドロック グラフを XML 形式で表示すると、デッドロックに関連する Transact-SQL ステートメントの inputbuffer をコピーできます。 デッドロックをテキストベースの形式で分析することもできます。
Transact-SQL クエリを使用してデッドロック グラフ情報を返す場合、デッドロック グラフの XML を表示するには、任意の行から列 deadlock_xml の値を選択して、SSMS の新しいウィンドウでデッドロック グラフの XML を開きます。
この例のデッドロックグラフの XML は次のとおりです。
<deadlock>
<victim-list>
<victimProcess id="process24756e75088" />
</victim-list>
<process-list>
<process id="process24756e75088" taskpriority="0" logused="6528" waitresource="KEY: 8:72057594045202432 (98ec012aa510)" waittime="192" ownerId="1011123" transactionname="user_transaction" lasttranstarted="2022-03-08T15:44:43.490" XDES="0x2475c980428" lockMode="U" schedulerid="3" kpid="30192" status="suspended" spid="89" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2022-03-08T15:44:49.250" lastbatchcompleted="2022-03-08T15:44:49.210" lastattention="1900-01-01T00:00:00.210" clientapp="Microsoft SQL Server Management Studio - Query" hostname="LAPTOP-CHRISQ" hostpid="16716" loginname="chrisqpublic" isolationlevel="read committed (2)" xactid="1011123" currentdb="8" currentdbname="AdventureWorksLT" lockTimeout="4294967295" clientoption1="671096864" clientoption2="128056">
<executionStack>
<frame procname="unknown" queryhash="0xef52b103e8b9b8ca" queryplanhash="0x02b0f58d7730f798" line="1" stmtstart="2" stmtend="792" sqlhandle="0x02000000c58b8f1e24e8f104a930776e21254b1771f92a520000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
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' </inputbuf>
</process>
<process id="process2476d07d088" taskpriority="0" logused="11360" waitresource="KEY: 8:72057594045267968 (39e18040972e)" waittime="2641" ownerId="1013536" transactionname="UPDATE" lasttranstarted="2022-03-08T15:44:46.807" XDES="0x2475ca80428" lockMode="S" schedulerid="2" kpid="94040" status="suspended" spid="95" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2022-03-08T15:44:46.807" lastbatchcompleted="2022-03-08T15:44:46.760" lastattention="1900-01-01T00:00:00.760" clientapp="Microsoft SQL Server Management Studio - Query" hostname="LAPTOP-CHRISQ" hostpid="16716" loginname="chrisqpublic" isolationlevel="read committed (2)" xactid="1013536" currentdb="8" currentdbname="AdventureWorksLT" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="unknown" queryhash="0xef52b103e8b9b8ca" queryplanhash="0x02b0f58d7730f798" line="1" stmtstart="2" stmtend="798" sqlhandle="0x020000002c85bb06327c0852c0be840fc1e30efce2b7c8090000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
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'; </inputbuf>
</process>
</process-list>
<resource-list>
<keylock hobtid="72057594045202432" dbid="8" objectname="9e011567-2446-4213-9617-bad2624ccc30.SalesLT.ProductDescription" indexname="PK_ProductDescription_ProductDescriptionID" id="lock2474df12080" mode="U" associatedObjectId="72057594045202432">
<owner-list>
<owner id="process2476d07d088" mode="U" />
</owner-list>
<waiter-list>
<waiter id="process24756e75088" mode="U" requestType="wait" />
</waiter-list>
</keylock>
<keylock hobtid="72057594045267968" dbid="8" objectname="9e011567-2446-4213-9617-bad2624ccc30.SalesLT.Product" indexname="PK_Product_ProductID" id="lock2474b588580" mode="X" associatedObjectId="72057594045267968">
<owner-list>
<owner id="process24756e75088" mode="X" />
</owner-list>
<waiter-list>
<waiter id="process2476d07d088" mode="S" requestType="wait" />
</waiter-list>
</keylock>
</resource-list>
</deadlock>
デッドロック グラフを XML ファイルとして保存するには:
- [ファイル]、[名前を付けて保存]の順に選択します。
- [名前を付けて保存] の種類の値は既定の XML ファイル (*.xml) のままにします。
- ファイル名を任意の名前に設定します。
- [保存] を選択します。
デッドロックグラフを SSMS で対話形式表示できる XDL ファイルとして保存する
デッドロック グラフの対話型表現を表示すると、デッドロックに関連するプロセスとリソースの概要を簡単に把握し、デッドロックの対象をすばやく特定するのに役立ちます。
SSMS でグラフィカルに表示できるファイルとしてデッドロック グラフを保存するには:
任意の行から
deadlock_xml列の値を選択して、SSMS の新しいウィンドウでデッドロック グラフの XML を開きます。[ファイル]、[名前を付けて保存]の順に選択します。
[名前を付けて保存]を[すべてのファイル]に設定します。
ファイル名 を任意の名前に設定し、拡張子を .xdl に設定します。
[保存] を選択します。
ウィンドウ上部のタブで [X] を選択するか、[ファイル] を選択してから [閉じる] を選択して、ファイルを閉じます。
SSMS でファイルを再度開くには、[ファイル]、[開く]、[ファイル]の順に選択します。
.xdl拡張子で保存したファイルを選択します。デッドロック グラフが SSMS に表示され、デッドロックに関連するプロセスとリソースが視覚的に表示されます。
Azure SQL Database のデッドロックを分析する
デッドロック グラフには通常、次の 3 つのノードがあります。
- victim-list。 デッドロック犠牲者プロセス識別子。
- Process-list。 デッドロックに関係するすべてのプロセスについての情報。 デッドロック グラフでは、「プロセス」という用語を使用して、トランザクションを実行しているセッションを表します。
- Resource-list。 デッドロックに関係するリソースについての情報。
デッドロックを分析するときは、これらのノードをステップ実行しておくと便利です。
デッドロックの対象一覧
デッドロックの対象一覧には、デッドロックの対象として選択されたプロセスが表示されます。 デッドロック グラフの視覚的表現では、プロセスは楕円形で表されます。 デッドロックの対象プロセスには、楕円形の上に「X」が描かれています。
デッドロック グラフの XML ビュー では、victim-listノードはデッドロックの対象であったプロセスの ID を提供します。
デッドロックの例では、対象のプロセス ID は process24756e75088 です。 この ID は、プロセス一覧のノードとリソース一覧のノードを調べて、対象プロセスと、ロックまたはロックを要求していたリソースの詳細を確認するときに使用できます。
デッドロック プロセスの一覧
デッドロック プロセスの一覧は、デッドロックに関係するトランザクションに関する豊富なソースです。
デッドロック グラフのグラフィック表現には、デッドロック グラフ XML に含まれる情報のサブセットのみが表示されます。 デッドロック グラフの楕円はプロセスを表し、次のような情報を表示します。
サーバー プロセス ID (セッション ID または SPID とも呼ばれます)。
セッションのデッドロック優先順位。 2 つのセッションのデッドロックの優先度が異なる場合、優先度の低いセッションがデッドロックの対象として選択されます。 この例では、両方のセッションのデッドロック優先順位が同じです。
セッションによって使用されるトランザクション ログの量 (バイト単位)。 両方のセッションのデッドロック優先順位が同じ場合、デッドロック モニターは、ロールバックに負担がかからないセッションをデッドロックの対象として選択します。 コストは、各トランザクションでその時点に書き込まれたログのバイト数を比較することによって決定されます。
このデッドロックの例では、session_id 89 ではトランザクション ログの量が少なくなっていますが、デッドロックの対象として選択されています。
さらに、デッドロックの前に各セッションで最後に実行されたステートメントの 入力バッファー を表示するには、各プロセスの上にマウスを置きます。 入力バッファーがツールヒントに表示されます。
デッドロック グラフの XML ビュープロセスについては、次のような追加情報を利用できます。
- セッションの識別情報 (クライアント名、ホスト名、ログイン名など)。
- デッドロックの前に各セッションによって実行された最後のステートメントのクエリ プラン ハッシュ。 クエリ プラン ハッシュは、クエリに関する詳細情報を クエリ ストア から取得する場合に役立ちます。
デッドロックの例では:
- 両方のセッションが chrisqpublic ログインで SSMS クライアントを使用して実行されたことがわかります。
- デッドロックの対象がデッドロックの前に実行した最後のステートメントのクエリ プラン ハッシュが0x02b0f58d7730f798 です。 このステートメントのテキストは入力バッファーに表示されます。
- デッドロック内の他のセッションによって実行された最後のステートメントのクエリ プラン ハッシュも0x02b0f58d7730f798 です。 このステートメントのテキストは入力バッファーに表示されます。 この場合、等値述語として使用されるリテラル値を除いて、クエリは同一であるため、両方のクエリは同じクエリ プラン ハッシュを持ちます。
この記事の後半でこれらの値を使用して、クエリ ストアで追加情報を見つます。
デッドロック プロセスの一覧内の入力バッファーの制限事項
デッドロック プロセスの一覧内の入力バッファー情報に関して注意すべきいくつかの制限事項があります。
クエリ テキストは、入力バッファーで切り捨てられる場合があります。 入力バッファーは、実行されるステートメントの最初の 4,000 文字に制限されます。
さらに、デッドロックに関連する一部のステートメントは、デッドロック グラフに含まれていない場合があります。 この例では、セッション A は 1 つのトランザクション内で 2 つの更新ステートメントを実行しました。 デッドロック グラフには、2 番目の update ステートメント (デッドロックの原因となった更新) のみが含まれます。 セッション A によって実行される最初の更新ステートメントは、セッション B をブロックすることによってデッドロックの一因となりました。query_hashSession A によって実行される最初のステートメントの入力バッファー、、および関連情報は、デッドロック グラフには含まれません。
デッドロックに関連する複数ステートメント トランザクションで実行される Transact-SQL の完全な実行を特定するには、クエリを実行したストアド プロシージャまたはアプリケーション コードで関連情報を見つけるか、拡張イベントを使用してトレースを実行して、デッドロックが発生している間にデッドロックに関連するセッションによって実行される完全なステートメントをキャプチャする必要があります。 デッドロックに関連するステートメントが切り捨てられ、Transact-SQL の一部のみが入力バッファーに表示される場合は、実行プラン付きのクエリ ストアでステートメントの Transact-SQLを見つけることができます。
デッドロック リソースの一覧
デッドロック リソースの一覧には、デッドロック内のプロセスによって所有され、待機しているロック リソースが表示されます。
リソースは、デッドロックの視覚的表現では四角形で表されます。
注意
データベース名は、Azure SQL Database のデータベースのデッドロック グラフで一意の識別子として表されることがあります。 これは、sys.databases および sys.dm_user_db_resource_governance 動的管理ビューに一覧表示されているデータベースの physical_database_name です。
このデッドロックの例では:
デッドロックの対象は、セッション A と呼ばれています。
PK_Product_ProductIDテーブルのSalesLT.Productインデックス上のキーに対する排他 (X) ロックを所有します。PK_ProductDescription_ProductDescriptionIDテーブルのSalesLT.ProductDescriptionインデックスのキーに対する更新 (U) ロックを要求します。
セッション B と呼ばれるもう 1 つのプロセスは次のとおりです。
PK_ProductDescription_ProductDescriptionIDテーブルのSalesLT.ProductDescriptionインデックスのキーに対する更新 (U) ロックを所有します。PK_ProductDescription_ProductDescriptionIDテーブルのSalesLT.ProductDescriptionインデックス上のキーに対する共有 (S) ロックを要求します。
リソース一覧ノードのデッドロック グラフの XML にも同じ情報があります。
クエリ ストアでクエリ実行プランを検索する
多くの場合、デッドロックに関連するステートメントのクエリ実行プランを調べるのに役立ちます。 これらの実行プランは、多くの場合、デッドロック グラフの プロセス一覧の XML ビューのクエリ プラン ハッシュを使用してクエリ ストアで見つけることができます。
この Transact-SQL クエリでは、デッドロックの例で見つかったクエリ プラン ハッシュと一致するクエリ プランが検索されます。 Azure SQL データベースのユーザー データベースに接続して、クエリを実行します。
DECLARE @query_plan_hash binary(8) = 0x02b0f58d7730f798
SELECT
qrsi.end_time as interval_end_time,
qs.query_id,
qp.plan_id,
qt.query_sql_text,
TRY_CAST(qp.query_plan as XML) as query_plan,
qrs.count_executions
FROM sys.query_store_query as qs
JOIN sys.query_store_query_text as qt on qs.query_text_id=qt.query_text_id
JOIN sys.query_store_plan as qp on qs.query_id=qp.query_id
JOIN sys.query_store_runtime_stats qrs on qp.plan_id = qrs.plan_id
JOIN sys.query_store_runtime_stats_interval qrsi on qrs.runtime_stats_interval_id=qrsi.runtime_stats_interval_id
WHERE query_plan_hash = @query_plan_hash
ORDER BY interval_end_time, query_id;
GO
クエリ ストアの CLEANUP_POLICY やQUERY_CAPTURE_MODE の設定によっては、クエリ ストアからクエリ実行プランを取得できない場合があります。 この場合多くは、クエリの 推定実行プランの表示 によって必要な情報を取得できます。
ブロックを増やすパターンを探す
デッドロックに関連するクエリ実行プランを調べる場合は、ブロックとデッドロックに寄与する可能性があるパターンを調べる必要があります。
テーブルまたはインデックスのスキャン。 データを変更する照会が RCSI の下で実行される場合、更新する行の選択は、データ値が読み取られるときにデータ行に対して更新 (U) ロックが取られるブロッキング スキャンを使用して行われます。 データ行が更新基準を満たさない場合、更新ロックが解除され、次の行がロックされてスキャンされます。
変更クエリが行をより効率的に検索できるようにインデックスを調整すると、発行される更新ロックの数が減ります。 これにより、ブロックやデッドロックが発生する可能性が低くなります。
複数のテーブルを参照するインデックス付きビュー。 インデックス付きビューで参照されるテーブルを変更する場合、データベース エンジンはインデックス付きビューも維持する必要があります。 これには、より多くのロックを取り出す必要があり、ブロッキングとデッドロックが増加する可能性があります。 インデックス付きビューによって、更新操作がコミットされた読み取り分離レベルで内部的に実行されることもあります。
外部キー制約で参照される列への変更。 FOREIGN KEY 制約で参照されるテーブルのカラムを変更する場合、データベース エンジンは参照元のテーブル内の関連する行を検索する必要があります。 行バージョンは、これらの読み取りには使用できません。 連鎖更新または削除が有効になっている場合は、ファントム挿入から保護するために、ステートメントの期間中、分離レベルがシリアル化可能にエスカレートされる可能性があります。
ロックヒント。 より多くのロックを必要とする分離レベルを指定するテーブル ヒントを探します。 これらのヒントには、
HOLDLOCK(シリアライズ可能と同等)、SERIALIZABLE、READCOMMITTEDLOCK(RCSI を使用不可にする)、およびREPEATABLEREADが含まれます。 さらに、PAGLOCK、TABLOCK、UPDLOCK、XLOCKなどのヒントは、ブロックやデッドロックのリスクを高める可能性があります。これらのヒントが整っている場合は、ヒントが実装された理由を調査します。 これらのヒントは、競合状態を防ぎ、データの有効性を保証する可能性があります。 必要に応じて、この記事の「デッドロックが再発しないようにする」セクションの別の方法を使用して、これらのヒントを設定したまま、将来のデッドロックを防ぐことができます。
注意
行のバージョン管理を使用してデータを変更する場合の動作の詳細については、「トランザクションのロックと行のバージョン管理ガイド」を参照してください。
実行プランまたはアプリケーション クエリ コードのいずれかでトランザクションの完全なコードを調べるときは、さらに問題のあるパターンを探します。
トランザクションでのユーザー操作。 明示的な複数ステートメント トランザクション内でのユーザー操作により、トランザクションの期間が大幅に長くなります。 これにより、これらのトランザクションが重複し、ブロックとデッドロックが発生する可能性が高くなります。
同様に、開いているトランザクションを保持し、トランザクションの途中で無関係なデータベースまたはシステムをクエリを実行すると、ブロックやデッドロックの可能性が大幅に高まります。
異なる順序でオブジェクトにアクセスするトランザクション。 同時明示的な複数ステートメント トランザクションが同じパターンに従い、同じ順序でオブジェクトにアクセスすると、デッドロックは発生しにくくなります。
デッドロックの再発を防ぐ
インデックスのチューニング、クエリ ストアを使用したプランの強制、Transact SQL クエリの変更など、デッドロックの再発を防ぐ方法は複数あります。
テーブルのクラスター化インデックスを確認します。 ほとんどのテーブルはクラスター化インデックスの恩恵を受けますが、多くの場合、テーブルは誤ってヒープとして実装されます。
クラスター化インデックスを確認する 1 つの方法は、sp_helpindex システム ストアド プロシージャを使用することです。 たとえば、次のステートメントを実行して、
SalesLT.Productテーブルのインデックスの概要を表示することができます。exec sp_helpindex 'SalesLT.Product'; GO[index_description] 列を確認します。 テーブルに含めることができるのは、1 つのクラスター化インデックスのみです。 テーブルにクラスター化インデックスが実装されている場合、index_description には「clustered」という単語が含まれます。
クラスター化インデックスが存在しない場合、テーブルはヒープになります。 この場合は、特定のパフォーマンスの問題を解決するために、テーブルがヒープとして意図的に作成されているかどうかを確認します。 クラスター化インデックスの設計ガイドラインに基づいてクラスター化インデックスを実装することを検討してください。
場合によっては、クラスター化インデックスを作成またはチューニングすることで、デッドロックのブロックを軽減または排除できます。 また、この一覧の他の手法など、追加の手法を採用する必要がある場合もあります。
非クラスター化インデックスを作成または変更します。 非クラスター化インデックスをチューニングすると、変更クエリで更新するデータをより迅速に見つけるのに役立ち、必要な更新ロックの数が減ります。
このデッドロックの例では、クエリ ストアで見つかったクエリ実行プランに、
PK_Product_ProductIDインデックスに対するクラスター化インデックス スキャンが含まれています。 デッドロック グラフは、このインデックスの共有 (S) ロック待機がデッドロックのコンポーネントであることを示しています。
このインデックス スキャンが実行されているのは、更新クエリで
vProductAndDescriptionという名前のインデックス付きビューを変更する必要があるためです。 この記事の「ブロッキングを増加させるパターンを探す」セクションで説明したように複数のテーブルを参照するインデックス付きビューは、ブロッキングとデッドロックの可能性を高める可能性があります。インデックス付きビューによって参照される
SalesLT.Productからの列を「カバー」する次の非クラスター化インデックスをAdventureWorksLTデータベースに作成すると、クエリはより効率的に行を見つけることができます。CREATE INDEX ix_Product_ProductID_Name_ProductModelID on SalesLT.Product (ProductID, Name, ProductModelID); GOこのインデックスを作成すると、デッドロックは再発しなくなります。
デッドロックによって外部キー制約で参照される列の変更が含まれる場合は、FOREIGN KEY の参照元テーブルのインデックスが、関連する行の効率的な検索をサポートしていることを確認します。
インデックスにより、クエリのパフォーマンスが大幅に向上する場合がありますが、インデックスのオーバーヘッドと管理コストも発生します。 インデックスを作成する前に、インデックスの利点、特に大きなテーブルのワイド インデックスとインデックスを評価するために、一般的なインデックス設計ガイドラインを確認します。
インデックス付きビューの価値を評価します。 デッドロックの例の再発を防ぐもう 1 つのオプションは、インデックス付きビュー
SalesLT.vProductAndDescriptionを削除することです。 インデックス付きビューが使用されていない場合、インデックス付きビューを時間の経過と同時に維持するオーバーヘッドが軽減されます。スナップショット分離を使用します。 場合によっては、デッドロックに関係する 1 つ以上のトランザクションのスナップショットをトランザクション分離レベルの設定にすると、ブロックやデッドロックの再発を防ぐことができます。
この手法は、SELECT ステートメントで データベースでコミットされたスナップショットの読み取りが無効になっている場合に使用すると、成功する可能性が最も高くなります。 コミットされた読み取りスナップショットが無効になっている場合、コミットされた読み取り分離レベルを使用する SELECT クエリでは、共有 (S) ロックが必要です。 これらのトランザクションでスナップショットの分離を使用すると、共有ロックが不要になり、ブロックやデッドロックを防ぐことができます。
読み取りコミットされたスナップショット分離が有効になっているデータベースでは、SELECT クエリでは共有 (S) ロックが必要ないので、データを変更しているトランザクション間でデッドロックが発生する可能性が高くなります。 データを変更する複数のトランザクション間でデッドロックが発生した場合、スナップショットの分離によってデッドロックではなく更新の競合が発生する可能性があります。 同様に、トランザクションの 1 つがその操作を再試行する必要があります。
クエリ ストアでプランを強制します。 デッドロック内の照会の 1 つに複数の実行プランがあり、デッドロックは特定のプランが使用された場合にのみ発生することがあります。 プランを強制的にクエリ ストアすることで、デッドロックが再発しないようにできます。
Transact-SQL を変更します。 デッドロックが再発しないように、Transact-SQL を変更する必要がある場合があります。 Transact-SQL の変更は慎重に行い、変更が同時に実行されるときにデータが正しいことを確認するために、変更を厳密にテストする必要があります。 Transact-SQL を書き直すときは、次の点を考慮してください。
- トランザクション内のステートメントを順序付けて、同じ順序でオブジェクトにアクセスするようにします。
- 可能な場合は、トランザクションをより小さなトランザクションに分割します。
- 必要に応じてクエリ ヒントを使用して、パフォーマンスを最適化します。 クエリ ストアを使用して、アプリケーション コードを変更せずにヒントを適用できます。
「デッドロック ガイド」でデッドロックを最小限にするための他の方法を確認してください。
注意
場合によっては、デッドロックに関係する 1 つ以上のセッションのデッドロック優先順位を調整する必要がある場合がありますが、セッションの 1 つが再試行せずに正常に完了することが重要な場合や、デッドロックに関連するクエリの 1 つが重大ではなく、常に対象として選択する必要がある場合に便利です。 これによりデッドロックが再発することを防ぐわけではありませんが、将来のデッドロックの影響を軽減する可能性があります。
XEvents セッションを削除する
重要なデータベースでデッドロック情報を収集する XEvents セッションを長期間実行したままにしておくことができます。 イベント ファイル ターゲットを使用する場合、複数のデッドロックが発生すると、ファイルのサイズが大きくなる可能性があることに注意してください。 現在書き込まれているファイルを除き、アクティブなトレースの Azure Storage から BLOB ファイルを削除できます。
XEvents セッションを削除する場合、Transact-SQL ドロップのセッションは、選択したターゲットの種類に関係なく同じです。
XEvents セッションを削除するには、次の Transact-SQL を実行します。 コードを実行する前に、セッションの名前を適切な値に置き換えます。
ALTER EVENT SESSION [deadlocks] ON DATABASE
STATE = STOP;
GO
DROP EVENT SESSION [deadlocks] ON DATABASE;
GO
Azure Storage Explorer を使用する
Azure Storage Explorer は、Azure Storage の BLOB に保存されているイベント ファイル ターゲットの操作を簡素化するスタンドアロン アプリケーションです。 Storage Explorer を使用すると、次のことができます。
- XEvent セッション データを保持する BLOB コンテナーを作成します。
- BLOB コンテナーの共有アクセス署名 (SAS) を取得します。
- 「拡張イベントを使用した Azure SQL Database のデッドロック グラフの収集」で説明したように、読み取り、書き込み、一覧へのアクセス許可必要です。
?から先頭のQuery string文字をすべて削除して、データベーススコープの資格情報を作成するときに値をシークレットとして使用します。
- BLOB コンテナーから拡張イベント ファイルを表示およびダウンロードます。
Azure Storage Explorer をダウンロードします。
次のステップ
Azure SQL Database のパフォーマンスの詳細については、次を参照してください。