SQL Serverは拡張イベントを使ってデッドロック問題を追跡します

クズども · 掲載地 2024/03/02 19:13:04

要件:SQL Serverのデータベースがデッドロックを引き起こす理由は多岐にわたります。本番環境では誰もがデッドロック問題に直面し、アプリケーションの異常な情報だけで具体的な原因が特定できない場合があるため、SQL Serverの拡張イベントを使ってデッドロック問題を追跡し、デッドロックが発生する具体的な詳細を記録しる必要があります。

拡張イベント概要

Extended Eventsは軽量なパフォーマンス監視機能で、ユーザーが監視や問題解決に必要なデータを収集できます。この機能により、データエンジンの内部動作の詳細を閲覧し、パフォーマンス監視や問題解決関連の目的で利用できます。

拡張イベント(XEvents)アーキテクチャでは、ユーザーはSQL Server、Azure SQL Database、Azure SQL管理インスタンスのパフォーマンスを監視、特定、トラブルシューティングに必要なだけ多くのデータを使用できます。拡張イベントは非常に設定可能で軽量、そしてスケーラブル性が高いです。

参考：
ハイパーリンクのログインが見えます。
ハイパーリンクのログインが見えます。

行き詰まりの原因

デッドロックは、通常は多段階のトランザクションでデータベース内の競合する並行ロックによって引き起こされます。各ユーザーセッションは、それぞれのタスクが1つ以上実行されていることがあり、それぞれのタスクがさまざまなリソースを取得するために取得または待機することができます。以下の種類のリソースはブロッキングを引き起こし、最終的にはデッドロックを引き起こすことがあります。

ロック:オブジェクト、ページ、行、メタデータ、アプリケーションなどのリソースを取得しようと待っているロックは、デッドロックを引き起こすことがあります。例えば、トランザクションT1はr1行に共有ロック(Sロック)を持ち、r2行に対して排他ロック(Xロック)を取得するのを待っています。トランザクションT2はr2行に共有ロック(Sロック)を持ち、r1行の排他ロック(Xロック)を待っています。これにより、T1とT2の両方が互いにロックされたリソースを解放するのを待つロックループが生じます。
ワーカースレッド:利用可能なワーカースレッドのキューイングがタスクの遅延を引き起こすことがあります。キューに入ったタスクにすべてのワーカースレッドをブロックするリソースがある場合、デッドロックが発生します。例えば、セッションS1がトランザクションを開始し、r1行の共有ロック(S-ロック)を取得した後、スリープ状態に入ります。利用可能なすべてのワーカースレッドで稼働しているアクティブなセッションは、r1行に対して排他的ロック(Xロック)を取得しようとしています。セッションS1はワーカースレッドを取得できないため、トランザクションをコミットしてr1ラインのロックを解除できません。これにより膠着状態になります。
メモリ:同時要求がメモリを獲得するのを待っている場合、現在利用可能なメモリがそのニーズに十分でない場合にデッドロックが発生することがあります。例えば、2つの同時クエリ(Q1とQ2)はユーザー定義関数として実行され、それぞれ10MBと20MBのメモリを取得します。各クエリが30MBを必要とし、利用可能なメモリの総容量が20MBの場合、Q1とQ2は互いにメモリを空けるのを待たなければならず、これがデッドロックを引き起こします。
並列クエリおよび実行関連リソース:スイッチドポートに関連付けられた処理コーディネーター、ジェネレーター、またはコンシューマスレッドは、並列クエリに含まれていないプロセスが少なくとも1つ含まれている場合、互いにブロックし合い、デッドロックが発生することがあります。さらに、並列クエリが実行を開始すると、SQL Serverは現在のワークロードに基づいて並列度やワーカースレッド数を決定します。デッドロックは、例えばサーバーで新しいクエリが実行され始めたり、ワーカースレッドが不足したりするなど、システムのワークロードに予期せぬ変化が生じた場合に発生します。
マルチプルアクティビティアウトカムセット(MARS)リソース:これらのリソースは、MARS上で複数アクティビティリクエストのクロス実行を制御するために使用されます。

参考：
ハイパーリンクのログインが見えます。

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

次のコマンドでデッドロック情報を記録する拡張イベントを作成します:

デッドロックイベントセッションを開始する

イベントセッションを停止する

イベントセッションを削除してください

イベントセッションデータのクエリ

テストのデッドロック

テスト用の新しいTab1テーブルを作成し、2つの新しい実行ウィンドウを作成し、それぞれ以下のコマンドを実行します。

ログインが見えます。

デッドロックは以下のように起こります:

別のプロセスとのトランザクション(プロセスID 68)がロックされたリソース上でデッドロックされており、デッドロックの被害者として選ばれています。取引を再実行してください。
トランザクション(プロセスID 68)は別のプロセスとロックリソースでデッドロックされ、デッドロックの被害者として選ばれました。トランザクションをやり直してください。

以下の図に示されたイベントセッションデータをご覧いただけます:

詳細なXMLデータは以下の通りです。

ログインが見えます。

(終わり)

クズども · 掲載地 2024/03/02 19:14:35

イベントファイルのデフォルトパスを拡張する:

C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Log\deadlock_monitor_0_133538477770860000.xel

クズども · 掲載地 2024/03/02 19:16:54

SQLスクリプトをテストするために新しいtab1テーブルを作成します

ログインが見えます。

[ヒント] SQL Serverは拡張イベントを使ってデッドロック問題を追跡します

関連記事

閲覧したセクション