SQL Server のレプリケーションで、パブリケーション内の最後のサブスクライバーを削除する場合、それまでのサブスクライバーを削除するときとは異なる挙動がありましたので、調査した内容や方法をまとめておきたいと思います。
Contents
作業を実施する際に影響の多いロックが取得されないかの確認
SQL Server で何らかの作業を実施する際に、影響の多いロックが取得されないかの確認を行うことが多々あります。
この際、一番気を付けておきたいのが「SCH-M」のロックが取得されるかどうかです。
SCH-M のロックはスキーマに対しての排他ロックが取得されるため、このロックが取得されているスキーマ (テーブル) にはアクセスができなくなります。
SCH-M のロックが取得されるかどうかについてですが、私は次のようなクエリを事前に実行し、作業を行うことで影響がないかを確認しています。
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ GO BEGIN TRAN SELECT TOP 10 * FROM LINEITEM SELECT * FROM sys.dm_tran_locks WHERE request_session_id = @@SPID --ROLLBACK TRAN
REPEATABLE READ でトランザクションを実行しているため、トランザクションが完了するまでアクセスしたテーブルに対して「IS」のロックが取得されます。
ロックの互換性 では、「SCH-M」と「IS」のロックは競合が発生します。
そのため上述のクエリのトランザクションを完了していない状態では、該当のテーブルに対して、SCH-M を取ろうとしたクエリがある場合はロック競合によるブロッキングが発生します。
これにより、作業を実施する裏で作業対象のテーブルに対して長時間アクセスを行っている処理が存在した場合に影響が出るかを確認することができます。
最後のサブスクライバーを削除する場合の取得されるロック
SQL Server では、一つのパブリケーションに対して複数のサブスクライバーを設定することができます。
最初のサブスクライバーを削除する際には、パブリケーションに含まれるアーティクルのテーブルに対して、他のセッションで IS のロックが取得されていても問題なく削除を行うことができます。
しかし、パブリケーション内の最後のサブスクライバーを削除する場合、他のセッションでアーティクルに含まれるテーブルに対して IS のロックが取得されていると、次のようにロック競合が発生します。
ロックの待機状態を確認すると SCH-M の取得で待機されていることが確認できます。
SCH-M の取得が待機状態になった場合、長いブロッキングチェーンが生成される可能性があり、該当テーブルへのアクセスをしているセッションに広く影響する可能性が考えられます。
どのような処理でロック競合が発生しているかを確認するには
どのような処理でロック競合が発生しているかを確認するには、いくつかの方法があります。
方法のひとつが拡張イベントの「blocked_process_report」を使用する方法となり、ブロッキングを確認する際にはこの方法が汎用的ではないでしょうか。
これにより、blocked-process-report を確認することができ、次のようなロック競合のレポートを取得できます。
単一のステートメントやネストされていないストアドプロシージャであればこのレポートから情報を確認することができます。
しかし、ネストされているストアドプロシージャの場合、最上位のストアドプロシージャの情報が出力されており、実際にブロッキングが発生したステートメントはこの情報では確認することはできません。
レポートの「executionStack」が、ストアドプロシージャ内の呼び出しのスタックとなりますので、sqlhandle と line を確認することでどのストアドプロシージャのどのステートメントなのかを確認することができます。
単純に sqlhandle を解決しようとすると次のようにオブジェクト名までは解決することができません。
レプリケーションのような内部のストアドプロシージャが使用されている場合、専用管理者接続 (DAC) を使用しないとオブジェクト名を解決することができません。
DAC であれば、内部のストアドプロシージャについても名前解決をすることができます。
このような内部オブジェクトは、sp_helptext 等でクエリのテキストを確認することはできないのですが、オブジェクト名が名前解決できれば、キャッシュからクエリのテキストを確認することができます。
ロック競合が発生している最中であれば、sys.dm_exec_requests をベースにして実行中のクエリを確認することで、ネストされているストアドプロシージャについても、現在実行中のストアドプロシージャの名前が分かりますので、この情報も活用することができます。
他にも、拡張イベントで「sp_statement_starting」をキャプチャすることで、どのステートメントで停止しているのかを見るという方法をとることもできます。
このような方法を組み合わせることで、ロック競合が発生した要因を確認することができます。
生成 AI を使用したクエリの違いの確認
上述の取得例であれば、「sp_statement_starting」の情報から、実行されたステートメントを確認することができます。
今回のケースであれば次の 2 パターンで実行されていたクエリが異なる可能性があります。
- 最後のサブスクライバーでないサーバーを削除
- 最後のサブスクライバーのサーバーを削除
それぞれのケースで取得したクエリを生成 AI に渡して、次のようなプロンプトで実行内容を比較することができます。
File01/File02 は、SQL Server のトレースの情報となっています。
含まれているクエリが大きく変わったのはFile02のどのステートメントからなのかを知りたいです。
実際に比較した結果が次の内容となります。
最後のサブスクリプションを削除した場合にクエリの変化が発生しているということが解析できていますね。
ロック競合が発生した場合、このような方法を使用することで解析を進めることができるのではないでしょうか。