SQL Server のトランザクションレプリケーションで、アーティクル (複製対象となるテーブル) を追加した後には、初期スナップショットの取得が必要となります。
初期スナップショットを取得する際には、パブリケーション内に含まれている全アーティクル (新規追加したアーティクル以外も含む) に対して瞬間的に「SCH_M」のロックが取得されています。
SCH_M のロックは強力なロックとなり、初期スナップショットを取得する際にパブリケーション内のアーティクルに対して実行時間が長いクエリが実行されている場合は、広範囲のブロッキングチェーンの発生の要因となる可能性があります。
本投稿では、初期スナップショットを取得する際の SCH_M のロックですがどのような処理により取得されているのかを確認していきたいと思います。
今回はレプリケーションの初期スナップショット取得を解析のターゲットとしていますが、この考え方については他のロック競合の情報を確認する際にも共通の内容となります。
ロック競合の状態を再現させる
まずは、どのようなロック競合が発生しているのかを確認するためにロック競合の状態を再現させます。
今回は、「初期スナップショットの取得時にパブリケーションに含まれるアーティクルに対して SCH_M の取得が発生する」ということが分かっていますので、再現の方法は簡単です。
次のように T1 ～ T4 というアーティクルが含まれるパブリケーションの初期スナップショットの取得を行うとします。
(T4 が新たに追加されたアーティクルとなります)
パブリッシャーのデータベースで次のようなクエリを実行しておきます。
BEGIN TRAN
SELECT TOP 1 * FROM T2 WITH(HOLDLOCK)
-- ROLLBACK TRAN
T2 はパブリケーションに含まれているアーティクルとなっており、このテーブルに対して XLOCK を保持した状態としています。
この状態でスナップショットエージェントを実行すると、スナップショットエージェントは、上記のクエリでロックが取得されているテーブル名の処理でロック競合が発生した状態となります。(統計情報更新のメッセージが出力されていますが、実際には統計情報の更新でロック競合が発生しているわけではないはずです)
今回、T4 というテーブルをアーティクルに追加し、このテーブルの初期同期を行うためにスナップショットの実行をしています。
しかし、初期スナップショットの実行時には、新規に追加したテーブル (アーティクル) だけでなく、既に複製が行われているテーブル (今回の例であれば T2) についても排他ロックの取得が行われ、ブロッキング発生した状態となりました。
既存のパブリケーションにアーティクルを追加することは、設定の共通化 / 再利用の観点で推奨されるものとなるかと思いますが、アーティクル数が多くなると、該当のパブリケーションにアーティクルを新規追加した際のロック競合の発生範囲が広くなる可能性があるということは意識しておく必要があります。
ロック競合の状態を確認する
それでは、実際に発生しているロック競合の情報を深堀して確認していきたいと思います。
今回の事象のロック競合の状態については blocked process threshold を設定し、Blocked Process Report イベントから取得するという、一般的な方法で確認することができます。
実際に取得した情報は次のような内容となります。
Blocking Process は先ほど実行したクエリとなり、Blocked Process は「Proc [Database Id = 32767 Object Id = -993696157]」となります。
このオブジェクト名を名前解決すると「sp_MSactivate_auto_sub」となります。
上記の「blocked-process」のリストの「frame」の最後のフレーム (0x0300ff7f6366c5c47dfe160174ae000001) がこのストアドプロシージャの呼び出しとなり、このストアドプロシージャでは複数のストアドがネストされコールされています。
各フレームのスタックを解決すると次のような構造となっています。
- sys.sp_MSsetfilteredstatus
- sys.sp_MSrepl_changesubstatus
- sys.sp_changesubstatus
- sys.sp_MSactivate_auto_sub
- exec sp_MSactivate_auto_sub @publication=N’ReplDB-repliation’,@article=N’%’,@status=N’initiated’ のようなフォーマットで実行されています。
今回の事象については、「sys.sp_MSsetfilteredstatus」のオフセット「1570～1798」のステートメントの実行で SCH_M の取得が発生し、ロック競合によるブロッキングが発生していたことになります。
オフセットのステートメントを抽出すると、次のステートメントであることが確認できます。
EXEC %%Object(MultiName = @qualified_name).LockMatchID(ID = @object_id, Exclusive = 1, BindInternal = 0)
LockMatchID については内部コマンド (メソッド) であるため、一般ユーザーは実行することができないと思いますが、SQL Server LockMatchID Command の情報が参考となります。
メソッド実行時の引数を確認すると「Exclusive = 1」となっていますので、このメソッドの実行により、パブリケーションに含まれているアーティクルに対して瞬間的に排他ロック (SCH_M) が取得されていると判断することができます。
初期スナップショットを取得するための、何らかのステートメントの副作用により SCH_M が取得されているのではなく、明示的に SCH_M の取得が行われることで瞬間的な排他利用が実施されているようです。
使用されているストアドプロシージャの実装については Gist に残しておきましたので、ストアドの内容が気になった方はこちらを参照してみてください。
スナップショットの取得をさらに深堀するためには
スナップショットを取得する際には、sp_startpublication_snapshot が実行されています。
exec sys.sp_startpublication_snapshot @publication = N'ReplDB-repliation'
このストアドプロシージャはスナップショットを取得するための SQL Server エージェントジョブが非同期で実行されるため、ステートメントの実行については、即時に完了しています。
スナップショットの取得は外部プログラムである「snapshot.exe」(C:\Program Files\Microsoft SQL Server\xxx\COM\snapshot.exe) により実行されており、スナップショットの取得をさらに深堀するには、この実行ファイルの解析が必要となります。
実際の実装については「Microsoft.SqlServer.Replication.dll」となっているため、このアセンブリの解析が必要となりますが、ここからスナップショットの実装を確認することはできます。
中心的な実装については「SqlServerSnapshotProvider.GenerateSnapshot() : void」となります。
先ほどのブロッキングのフレームで確認できた「sys.sp_MSactivate_auto_sub」が呼び出されていることも確認できます。
上述のスナップショットエージェントの状態の表示では、統計情報の更新のようなメッセージが表示されていましたが、実際に統計情報が更新されていることも実装から確認することができます。
統計情報の更新については、BCP によるデータ抽出を並列で実施するための分割単位を決定するために統計の最新化を実行していたようです。
詳細な実装の確認が必要となった場合にはアセンブリから追うことができることも覚えておくとよいのではないでしょうか。