SQL Server における分散トランザクション 3
神谷 雅紀
Escalation Engineer
以下の投稿で、分散トランザクションの開始から終了までの動作を説明しました。
SQL Server における分散トランザクション 1 SQL Server における分散トランザクション 2
本投稿では、分散トランザクションに関連するその他の一般的なトピックについて説明します。
MSDTCを動作させるために必要な設定
MSDTCを動作させるために必要な設定について
https://blogs.technet.microsoft.com/jpiis/2018/02/05/msdtc-settings/
ロック所有者 session_id (SPID) –2
MS DTC トランザクションにエンリストしているセッションがない場合、sys.dm_tran_locks 動的管理ビューでは、そのトランザクションが所有しているロックの所有者 session_id が –2 と表示されます。実際に –2 というセッションが存在する訳ではなく、そのロックに紐づくセッションがないために、便宜的に –2 という値を使用しているだけです。
以下の例では、4) と 5) の間、7) から 8) の完了までの間、-2 がこの分散トランザクションによって獲得されたロックの所有者として表示されます。
1) トランザクション開始
2) セッション 51 が SQL Server との接続を確立し、トランザクションにエンリスト
3) セッション 51 がデータを更新
4) セッション 51 が SQL Server との接続を切断
5) セッション 52 が SQL Server との接続を確立し、トランザクションにエンリスト
6) セッション 52 がデータを更新
7) セッション 52 が SQL Server との接続を切断
8) トランザクションをコミット
SPID –2 が所有しているロックの強制解放 (分散トランザクションの強制終了)
クライアントアプリケーションやネットワークの問題などにより、トランザクションがコミットもロールバックもされず、SPID –2 がロックを保持し続けるという状況が発生する可能性があります。このような場合には、管理者がトランザクションを強制終了してロックを解放する必要があります。
以下はその具体的な手順です。通常、KILL ステートメントには session_id を指定しますが、-2 は便宜的な session_id であるため、KILL –2 としても、ロックは解放されません。分散トランザクションが所有しているロックを解放するためには、 KILL ステートメントに UOW (Unit Of Work) を指定する必要があります。
1) 以下のクエリを実行し、ロックを所有しているトランザクションの UOW である request_owner_guid 列の値を確認します。
select request_session_id, request_owner_guid from sys.dm_tran_locks where spid=-2
実行結果例
| session_id | request_owner_guid |
| -2 | 9D99300B-AD20-49C7-BC4B-0F19D20E2E0E |
2) 確認した UOW (request_owner_guid 列の値) を指定して、KILL ステートメントを実行します。
KILL '1) で確認した UOW'
実行例
| KILL '9D99300B-AD20-49C7-BC4B-0F19D20E2E0E' |
SQL Server が MS DTC からのコミット、ロールバック要求を処理する方法
MS DTC からのトランザクションに関する要求は、クライアントセッションとは別に実行されます。そのため、以下の例のように、クライアントが T-SQL を実行しているセッションが SPID (session_id) 59 と 60 であったとしても、トランザクションのコミットやロールバックは、SPID 49 などクライアントセッションとは別のセッション (システムタスク) によって実行されます。
|
MS DTC の起動
SQL Server 2016 からは、MS DTC が起動していない場合、MS DTC が必要になった時点で SQL Server が MS DTC を起動できるようになっています。そのため、MS DTC サービスを明示的に起動する必要はなくなっています。
SQL Server 2014 以前は、SQL Server は MS DTC を起動できないため、分散トランザクションの実行時に MS DTC が起動されていないと、エラーとなり分散トランザクションは開始できません。
MS DTC に関連する待機
sys.dm_exec_requests 動的管理ビューの wait_type 列で “DTC” という文字列から始まる種類の待機や PREEMPTIVE_TRANSIMPORT, PREEMPTIVE_DTC_ENLIST, PREEMPTIVE_DTC_REENLIST などの待機は、MS DTC での処理待ちや MS DTC からの応答待ちを示します。
SQL Server 開発部門に属する SQL Server Customer Advisory Team (SQLCAT) のテストでは、アプリケーションと SQL Server がそれぞれのローカル MS DTC を使用する構成ではなく、両方が単一の MS DTC を使用するように構成することで、これらの待機を緩和できています。すべての環境において必ずしもそのような構成が有効であるとは限りませんが、MS DTC に関連する待機の時間が長い場合には、そのような構成を試してみる価値はあるかと思います。詳細は以下を参照して下さい。
Resolving DTC Related Waits and Tuning Scalability of DTC
https://blogs.msdn.microsoft.com/sqlcat/2010/05/11/resolving-dtc-related-waits-and-tuning-scalability-of-dtc/
クラスター環境での MS DTC 構成
以下の記事に Cluster 上の SQL Server が利用する上で MSDTC の構成について、それぞれのメリット、デメリットが記載されていますので、クラスター構成を検討する場合の参考になるかと思います。
MSDTC Recommendations on SQL Failover Cluster
https://blogs.msdn.microsoft.com/alwaysonpro/2014/01/15/msdtc-recommendations-on-sql-failover-cluster/
この記事では、特に SQL Server のリソースグループごとに MSDTC のリソースを作成・参加させることで、常に同じ物理ノード上で MS DTC と SQL Server が動作するため、MS DTC と SQL Server 間でのコミュニケーションにおけるオーバーヘッドが小さく、高パフォーマンスを発揮できると記載しています。(この構成の場合は、SQL Server を先に構築し、その後に MSDTC のリソースを追加する必要があります。SQL Server をグループに追加する際には、必ず未使用の共有ディスクが必要なため、先に MSDTC のリソースをグループに追加すると、同じディスクを共有することが出来ません。)
その他にも、各 MS DTC インスタンスごとに専用クラスターグループとする場合、 単一のクラスター化 MS DTC インスタンスを共有する場合、ノードのローカル MS DTC インスタンスを使用する場合についても言及しています。