適用対象:
Azure SQL DatabaseMicrosoft Fabric SQL Database
この記事では、デッドロックを特定し、デッドロック グラフとクエリ ストアを使用してデッドロック内のクエリを識別し、デッドロックの再発を防ぐための変更を計画してテストする方法について説明します。 この記事は、Azure SQL Database の多くの機能を共有する Fabric の Azure SQL Database と SQL データベースに適用されます。
この記事では、ロック競合によるデッドロックの特定と分析に焦点を当てます。 その他の種類のデッドロックの詳細については、「デッドロックが発生する可能性のあるリソース」を参照してください。
デッドロックが発生するしくみ
Azure SQL Database の各新しいデータベースでは、 既定で有効になっている読み取りコミット済みスナップショット分離 (RCSI) データベース設定があります。 行のバージョン管理を使用してコンカレンシーを高める RCSI のもとで、データを読み取るセッションとデータを書き込むセッション間のブロックが最小限に抑えられます。 ただし、次の理由により、Azure SQL Database のデータベースでブロックとデッドロックが引き続き発生する可能性があります。
データを変更するクエリが互いをブロックする可能性があります。
クエリは、ブロックが増加する分離レベルで実行される場合があります。 分離レベルは、Transact-SQL のクライアント ライブラリ メソッド、 クエリ ヒント、または SET TRANSACTION ISOLATION LEVEL を使用して指定できます。
RCSI が無効になっている可能性があるため、データベースは共有 (S) ロックを使用して、読み取りコミットされた分離レベルで実行
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でブロックされます。
セッション A はトランザクションを続行し、
SalesLT.ProductDescriptionテーブルに対して更新を実行するようになりました。 セッション A は、SalesLT.ProductDescriptionのセッション B によってブロックされます。
参加しているトランザクションのいずれかがロールバックされない限り、デッドロック内のすべてのトランザクションは無期限に待機します (たとえば、セッションが終了したため)。
データベース エンジンのデッドロック モニターは、デッドロック状態にあるタスクを定期的にチェックします。 デッドロック モニターは、循環依存関係を検出した場合、いずれかのタスクを対象として選択し、エラー 1205 でトランザクションを終了します。 Transaction (Process ID <N>) was deadlocked on lock resources with another process and is chosen as the deadlock victim. Rerun the transaction. この方法でデッドロックを解除すると、デッドロック内の他のタスクがトランザクションを完了できます。
注意
デッドロックの対象を選択するための基準詳細については、この記事の「デッドロック プロセスの一覧」セクションを参照してください。
デッドロックの対象として選択されたトランザクションを持つアプリケーションは、トランザクションを再試行する必要があります。トランザクションは、通常、デッドロックに関連する他のトランザクションが終了した後に完了します。
再試行の前にランダム化された短い遅延を導入して、同じデッドロックが再び発生しないようにすることをお勧めします。 詳細については、「一時的なエラーの再試行ロジック」を設計する方法を参照してください。
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 を受け取った後も自動的に続行されるように、 再試行ロジック を含むようにアプリケーションを記述する必要があります。 Transaction (Process ID <N>) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
ただし、デッドロックが繰り返し発生する可能性があるため、アラートを設定すると便利です。 デッドロック アラートを使用すると、データベースで繰り返しデッドロックのパターンが発生しているかどうかを調査できます。その場合は、デッドロックが繰り返されないようにアクションを実行できます。 アラートの詳細については、この記事の「監視とデッドロックに関するアラート」セクションを参照してください。
デッドロックを防ぐための上位のメソッド
デッドロックの再発を防ぐための最もリスクの低いアプローチは、通常、デッドロックに関連するクエリを最適化するために非クラスター化インデックスを調整することです。
非クラスター化インデックスのチューニングではクエリ コード自体を変更する必要がなく、Transact-SQL を書き換えるときにユーザー エラーが発生して誤ったデータがユーザーに返されるリスクが軽減されるため、このアプローチではリスクが低くなります。
効果的な非クラスター化インデックスのチューニングは、クエリが読み取りと変更を行うデータをより効率的に見つけるのに役立ちます。 クエリがアクセスする必要があるデータ量を減らすことで、ブロックの可能性が低くなり、デッドロックを防止できることがよくあります。
場合によっては、クラスター化インデックスを作成またはチューニングすることで、ブロックとデッドロックを減らすことができます。 クラスター化インデックスは、すべての非クラスター化インデックス定義に含まれているため、クラスター化インデックスの作成または変更は、既存の非クラスター化インデックスを持つ大規模なテーブルに対する IO の負荷が高く、時間のかかる操作になる可能性があります。 詳細については、「クラスター化インデックスの設計ガイドライン」を参照してください。
インデックスのチューニングがデッドロックの防止に成功しない場合は、他の方法を使用できます。
デッドロックに関連するクエリのいずれかに対して特定のプランが選択された場合にのみデッドロックが発生した場合、クエリ ストアを使用して クエリ プランを強制 すると、デッドロックが繰り返し発生しなくなる可能性があります。
デッドロックに関連する 1 つ以上のトランザクションに対して Transact-SQL を書き直すことも、デッドロックを防ぐのに役立ちます。 明示的なトランザクションを小さなトランザクションに分割するには、同時に変更が発生したときにデータの有効性を確認するために、慎重なコーディングとテストが必要です。
これらの各方法の詳細については、この記事の「デッドロックが再発しないようにする」セクションを参照してください。
デッドロックの監視とアラート
この記事では、 AdventureWorksLT サンプル データベースを使用して、デッドロックのアラートを設定し、デッドロックの例を発生させ、デッドロックのグラフを分析して、デッドロックが繰り返されないように変更をテストします。
この記事では SQL Server Management Studio (SSMS) クライアントを使用します。このクライアントには、対話型ビジュアル モードでデッドロック グラフを表示する機能が含まれています。 Visual Studio Code 用の MSSQL 拡張機能、sqlcmd、お気に入りの Transact-SQL クエリ ツールなどの他のクライアントを使用して、例に従うことができますが、デッドロック グラフは XML としてのみ表示できる場合があります。
AdventureWorksLT データベースを作成する
例に従って、Azure SQL Databaseに新しいデータベースを作成し、データ ソースとして [サンプル データ] を選択します。
Azure portal、Azure CLI、または PowerShell を使用して AdventureWorksLT を作成する方法の詳細については、「 クイック スタート: Azure SQL Database で単一データベースを作成する」で任意の方法を選択します。
Azure portal でデッドロック アラートを設定する
デッドロック イベントのアラートを設定するには、「 Azure portal を使用して Azure SQL Database と Azure Synapse Analytics のアラートを作成する」の記事の手順に従います。
アラートのシグナル名として [デッドロック] を選択します。 電子メール/SMS/プッシュ/音声アクションの種類など、選択した方法を使用して通知するようにアクショングループを構成します。
拡張イベントを使用して Azure SQL Database でデッドロック グラフを収集する
デッドロック グラフは、デッドロックに関連するプロセスとロックに関する豊富なソースです。 Azure SQL Database の拡張イベント (XEvents) と Fabric の SQL データベースでデッドロック グラフを収集するには、 sqlserver.database_xml_deadlock_report イベントをキャプチャします。
リング バッファー ターゲットまたはイベント ファイル ターゲットのいずれかを使用して、XEvent でデッドロック グラフを収集できます。 適切なターゲットの種類を選択するための考慮事項を次の表にまとめます。
| アプローチ | メリット | 考慮事項 | 使用シナリオ |
|---|---|---|---|
| リング バッファー ターゲット | - Transact-SQL のみを使用した簡単なセットアップ。 | - イベント データは、データベースをオフラインにしたり、データベースのフェールオーバーを実行したりするなど、何らかの理由で XEvents セッションが停止するとクリアされます。 - データベース リソースは、リング バッファー内のデータを維持し、セッション データのクエリを実行するために使用されます。 |
- テストと学習のためのサンプル トレース データを収集します。 - イベント ファイル ターゲットをすぐに使用してセッションを設定できない場合は、短期的なニーズに合わせて作成します。 - トレース データをテーブルに保持する自動化されたプロセスを設定した場合は、トレース データの ランディング パッド として使用します。 |
| イベント ファイル ターゲット | - セッションが停止した後でもデータを使用できるように、Azure Storage 内の BLOB にイベント データを保持します。 - イベント ファイルは Azure portal または Azure Storage Explorer からダウンロードしてローカルで分析できます。データベース リソースを使用してセッション データを照会する必要はありません。 |
- セットアップはより複雑であり、Azure Storage コンテナーとデータベース スコープ資格情報の構成が必要です。 | - イベント セッションが停止した後でもイベント データを保持する場合に一般的に使用します。 - メモリに保持するよりも大量のイベント データを生成するトレースを実行する必要があります。 |
使用するターゲット タイプを選択します。
リング バッファー ターゲットは便利で設定が簡単ですが、容量が限られているため、古いイベントが失われる可能性があります。 リング バッファーはストレージにイベントを保持せず、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
デッドロックの原因
最適化されたロックは、Azure SQL Database と Fabric の SQL データベースで常に有効になるため、デッドロックの可能性は低くなります。 詳細と、最適化されたロックで発生する可能性があるデッドロックの例については、「 最適化されたロックとデッドロック」を参照してください。
XEvents セッションからのデッドロック グラフ表示
デッドロックを収集するように XEvents セッションを設定し、セッションの開始後にデッドロックが発生した場合は、デッドロック グラフとデッドロック グラフの XML の対話型グラフィック表示を表示できます。
リング バッファー ターゲットおよびイベント ファイル ターゲットのデッドロック情報を取得するには、さまざまな方法を使用できます。 XEvents セッションに使用したターゲットを選択します。
リング バッファーへの書き込みを行う XEvents セッションを設定する場合は、次の Transact-SQL を使用してデッドロック情報を照会できます。 クエリを実行する前に、@tracename の値を XEvents セッションの名前に置き換えます。
DECLARE @tracename AS sysname = N'deadlocks';
WITH ring_buffer
AS (SELECT CAST (target_data AS XML) AS rb
FROM sys.dm_xe_database_sessions AS s
INNER 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 (SPID とも呼ばれます)。
セッションのデッドロック優先順位。 2 つのセッションのデッドロックの優先度が異なる場合、優先度の低いセッションがデッドロックの対象として選択されます。 この例では、両方のセッションのデッドロック優先順位が同じです。
セッションによって使用されるトランザクション ログの量 (バイト単位)。 両方のセッションのデッドロック優先順位が同じ場合、デッドロック モニターは、ロールバックに負担がかからないセッションをデッドロックの対象として選択します。 コストは、各トランザクションでその時点に書き込まれたログのバイト数を比較することによって決定されます。
この例のデッドロックでは、
session_id89 では使用されるトランザクション ログの量が少なくなり、デッドロックの対象として選択されています。
さらに、各プロセスの上にマウスポインターを置くと、デッドロックが発生する前に各セッションで最後に実行されたステートメントの 入力バッファー を表示できます。 入力バッファーがツールチップに表示されます。
デッドロック グラフの XML ビュープロセスについては、次のような追加情報を利用できます。
セッションの識別情報 (クライアント名、ホスト名、ログイン名など)。
デッドロックの前に各セッションが実行した最後のステートメントのクエリプランハッシュ。 クエリ プラン ハッシュは、クエリに関する詳細情報を クエリ ストア から取得する場合に役立ちます。
デッドロックの例では:
両方のセッションが、
chrisqpublicログインの下で SSMS クライアントを使用して実行されていることがわかります。デッドロックの被害者によるデッドロックの前に最後に実行されたステートメントのクエリ プラン ハッシュが
0x02b0f58d7730f798。 このステートメントのテキストは入力バッファーに表示されます。デッドロックにおいて、他のセッションが実行した最後のステートメントのクエリプランハッシュも
0x02b0f58d7730f798です。 このステートメントのテキストは入力バッファーに表示されます。 この場合、等値述語として使用されるリテラル値を除いて、クエリは同一であるため、両方のクエリは同じクエリ プラン ハッシュを持ちます。
この記事の後半でこれらの値を使用して、 クエリ ストアで追加情報を見つけます。
デッドロック プロセスの一覧内の入力バッファーの制限事項
デッドロック プロセスの一覧内の入力バッファー情報に関して注意すべきいくつかの制限事項があります。
入力バッファーでクエリ テキストが切り捨てられる可能性があります。 入力バッファーは、実行されるステートメントの最初の 4,000 文字に制限されます。
さらに、デッドロックに関係する一部のステートメントがデッドロック グラフに含まれていない可能性があります。 この例では、セッション A は 1 つのトランザクション内で 2 つの更新ステートメントを実行しました。 デッドロック グラフには、2 番目の update ステートメント (デッドロックの原因となった更新) のみが含まれます。
セッション A によって実行された最初の更新ステートメントは、セッション B をブロックすることによってデッドロックの一部を果たしました。query_hash によって実行される最初のステートメントの入力バッファー、、および関連情報は、デッドロック グラフには含まれません。
デッドロックに関係する複数ステートメント トランザクションで実行される完全な Transact-SQL を特定するには、クエリを実行したストアド プロシージャまたはアプリケーション コードで関連情報を見つけるか、 拡張イベント を使用してトレースを実行して、デッドロックが発生しているセッションによって実行される完全なステートメントをキャプチャする必要があります。 デッドロックに関係するステートメントが切り捨てられ、入力バッファーに部分的な Transact-SQL のみが表示される場合は、実行プランを使用して クエリ ストア内のステートメントのTransact-SQL を見つけることができます。
デッドロック リソースの一覧
デッドロック リソースの一覧には、デッドロック内のプロセスによって所有され、待機しているロック リソースが表示されます。
リソースは、デッドロックの視覚的表現では四角形で表されます。
注意
データベース名は、Azure SQL Database のデータベースと Fabric の SQL データベースのデッドロック グラフでは GUID (uniqueidentifier) として表されます。 これは、physical_database_name および sys.dm_user_db_resource_governance 動的管理ビューに一覧表示されているデータベースの です。
このデッドロックの例では:
デッドロックの対象は、セッション 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 AS 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
INNER JOIN sys.query_store_query_text AS qt
ON qs.query_text_id = qt.query_text_id
INNER JOIN sys.query_store_plan AS qp
ON qs.query_id = qp.query_id
INNER JOIN sys.query_store_runtime_stats AS qrs
ON qp.plan_id = qrs.plan_id
INNER JOIN sys.query_store_runtime_stats_interval AS 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テーブルのインデックスの概要を表示することができます。EXECUTE sp_helpindex 'SalesLT.Product'; GOindex_description列を確認します。 テーブルに含めることができるのは、1 つのクラスター化インデックスのみです。 テーブルに対してクラスター化インデックスが実装された場合、index_descriptionにはclusteredという単語が含まれます。クラスター化インデックスが存在しない場合、テーブルはヒープになります。 この場合は、特定のパフォーマンスの問題を解決するために、テーブルがヒープとして意図的に作成されているかどうかを確認します。 クラスター化インデックスの設計ガイドラインに基づいてクラスター化インデックスを実装することを検討してください。
場合によっては、クラスター化インデックスを作成またはチューニングすると、デッドロックのブロックが軽減または排除される場合があります。 それ以外の場合は、この一覧の他の手法など、追加の手法を使用できます。
非クラスター化インデックスを作成または変更します。 非クラスター化インデックスをチューニングすると、変更クエリで更新するデータをより迅速に見つけるのに役立ち、必要な更新ロックの数が減ります。
このデッドロックの例では、クエリ ストアで見つかったクエリ実行プランに、
PK_Product_ProductIDインデックスに対するクラスター化インデックス スキャンが含まれています。 デッドロック グラフは、このインデックスの共有 (S) ロック待機がデッドロックのコンポーネントであることを示しています。
このインデックス スキャンが実行されているのは、更新クエリで
vProductAndDescriptionという名前のインデックス付きビューを変更する必要があるためです。 この記事の「 ブロックを増やすパターンの検索 」セクションで説明したように、複数のテーブルを参照するインデックス付きビューによってブロックが増加し、デッドロックが発生する可能性があります。インデックス付きビューによって参照される
AdventureWorksLTからの列を「カバー」する次の非クラスター化インデックスをSalesLT.Productデータベースに作成すると、クエリはより効率的に行を見つけることができます。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 つ以上のセッションのデッドロック の優先順位を調整 できます。 これにより、デッドロックが繰り返し発生するのを防ぐわけではありませんが、将来のデッドロックの影響が軽減される可能性があります。
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 を使用すると、次のことができます。
BLOB コンテナーの共有アクセス署名 (SAS) を取得します。
「拡張イベントを使用した Azure SQL Database のデッドロック グラフの収集」で説明したように、読み取り、書き込み、一覧へのアクセス許可必要です。
?から先頭のQuery string文字をすべて削除して、データベーススコープの資格情報を作成するときに値をシークレットとして使用します。
Azure Storage Explorer をダウンロードします。