SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

SQL Server の分散トランザクション使用時のインダウトトランザクション (未解決のトランザクション) について

leave a comment

SQL Server は、MS DTC (Microsoft Distributed Transaction Cordinator) をトランザクションマネージャーとして使用して、分散トランザクションの実行を行うことができます。

分散トランザクションについては、トランザクションのロックおよび行のバージョン管理ガイド で解説が行われています。

フローについては、1.3.1.2.3.2 Transaction Enlistment and Completion になるかと思いますが、コミットを「準備フェーズ」と「コミットフェーズ」の 2 フェーズで管理を行う 2 フェーズコミット (2PC) により、トランザクションの管理が行われます。

image

SQL Server の場合、インスタンス内の複数のデータベースにまたがる処理については、管理ガイドでは次のように記載されています。

数のデータベースにまたがる 1 つのSQL Server データベース エンジン内のトランザクションは実質的には分散トランザクションです。 ただし、SQL Server インスタンスは分散トランザクションを内部で処理するため、ユーザーにはローカル トランザクションとして動作しているように見えます。

実質は分散トランザクションではあるのですが、ローカルトランザクションのように見える処理が行われるため、MS DTC は使用されずに、処理が行われていたかと思います。

リンクサーバー / トランザクションスコープ (Transaction Scope) / BEGIN DISTRIBUTED TRANSACTION 等を使用した場合、実行方法によっては、MS DTC を使用した分散トランザクションとして実行され、分散トランザクションで実行される場合には、インダウト (in-doubt) トランザクション (未解決 / 不確定 / 不定トランザクション) の発生についての考慮が必要なケースが出てきます。

コミット時のインダウトトランザクションとしては、上記の図の xa_commit の部分になるかと思います。

image

分散トランザクションに参加しているトランザクションは最終的には、トランザクションマネージャーからの commit 要求により、各トランザクションのコミットが行われますが、コミットフェーズにおいて、特定のインスタンスでは、コミットが完了しなかったようなケースが発生した場合には、インダウト トランザクションとして、不確定のトランザクションをどのように取り扱うかを決めなくてはいけません。

インダウト トランザクションは 2PC の特定のタイミングでトランザクションを未確定にしなくてはいけないのですが、意図的に発生させる方法について残しておきたいと思います。

なお、windbg を使用して無理やり発生させていますが、本投稿では windbg の使用方法などについては触れませんのでご了承ください。

SQL Server のインダウト トランザクションの解決方法

インダウトトランザクションが発生すると、SQL Server のログとして次のような内容が出力されます。

SQL Server detected a DTC/KTM in-doubt transaction with UOW  {D0793190-C63F-48BE-B4B5-D9F7DC52274E}.Please resolve it following the guideline for Troubleshooting DTC Transactions.

Error: 3414, Severity: 21, State: 2.

An error occurred during recovery, preventing the database ‘TESTDB01’ (14:0) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support.

このようなメッセージが出力されている場合、インダウトトランザクションをどのように処理するか (コミット / ロールバック) を判断しなくてはいけません。

DTC/KTM in-doubt transaction with UOW, user database goes in suspect mode で解説されている、コンポーネントサービスの MMC を使用する方法もありますが、SQL Server 側でもインダウト トランザクションの解決方法が提供されています。

一つ目の方法が 未解決のトランザクションを管理する で解説されているものです。
インダウトトランザクションが発生している場合 UOW (作業単位 : Unit of work) の GUID も併せて出力が行われています。

SQL Server の KILL コマンド は UOW の GUID を指定することができます。

KILL 'yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy' WITH COMMIT
KILL 'yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy' WITH ROLLBACK

これにより指定した UOW のトランザクションについて、コミットを行うかロールバックを行うかを指定することができます。

もう一つの方法が、in-doubt xact resolution サーバー構成オプション の設定です。

設定については、次の三種類から選択することができ、デフォルトは「0」になっています。

image

デフォルトの設定では、MS DTC で解決できないトランザクションが確認された場合、データベースを「未確認」(SUSPECT) の状態にし、データベースへのアクセスができない状態にします。

image

この状態にしてから、KILL で UOW を指定し、不確定となっているトランザクションをどのように扱うかを明示的に処理をし、インダウト トランザクションが一通り対応できたところで、

ALTER DATABASE [DB 名] SET ONLINE


でデータベースをオンラインにします。

これがデフォルトの動作となるのですが、「1」「2」を設定することで、「コミット」「ロールバック」のどちらかを自動的に実行するように設定することもできます。

この設定を行った場合は、自動的に解決がされますので、データベースが未確認の状態にらなず、データベースがロックされることを防ぐことができるのですが、一律で処理が行われますので、トランザクションの状態が正しいものなのかを判断する機会がなくなり、データの状態が正しくない可能性があり、一長一短はあるかと思います。

このような方法を使用することで未解決のトランザクションを解決することが可能です。

インダウトトランザクションを作り出す

上記のような未解決の UOW の処理の挙動を確認するためには、分散トランザクションでインダウトトランザクションを作り出す必要があります。

今回は、次のようなクエリの実行で考えてみます。

SET XACT_ABORT ON
DECLARE @newid varchar(36) = NEWID()
BEGIN DISTRIBUTED TRANSACTION
	INSERT INTO TESTDB01.dbo.T1(C2) VALUES (@newid)
	INSERT INTO [10.80.0.1].TESTDB01.dbo.T1 (C2) VALUES (@newid)
COMMIT TRAN

 

このクエリは、「ローカルのインスタンス」と「10.80.0.1」に対して分散トランザクションを使用してデータの投入を行うものとなります。

先ほどの xa_commit の図を再掲します。

image

赤枠で囲っている処理が、ローカルのインスタンスと 10.80.0.1 のそれぞれに対して実行が行われますが、どちらかのインスタンスについて、赤枠の処理が最後まで実行されないようにすることで、インダウトトランザクションを作り出せるのではないでしょうか。

今回は、10.80.0.1 だけコミットが失敗した状態を意図的に作り出してみます。

分散トランザクションの処理を途中で強制的に終了させるのを、バッチで仕込むのは中々大変そうなので、今回は windbg を使用し、ブレークポイントを設定して分散トランザクションのコミットの途中で意図的に停止させます。

どこでブレークポイントを設定すればよいかを小出しに確認していくのは大変なので、今回は、一度上記の分散トランザクションのクエリを実行している最中の stackwalk を事前に取得してみました。

分散トランザクションは dtc / xact などがキーワードとなりますが、これらのキーワードのメソッドが含まれるスタックを探してみると、それらしいものが見つかりましたので、これらに対して windbg でブレークポイントを設定してみます。

image

不要なものも一部混じっていると思いますが、「10.80.0.1」の「sqlserver.exe」に対して、windbg で、次のようなコマンドでブレークポイントを設定しておきます。

bp sqlmin!XactRM::CommitDTCXact;bp sqlmin!DtcXactRMOnly::CommitDTCXact;bp sqlmin!ISSResourceAsynch::DoTMWork;g

 

image

これで上述した分散トランザクションのクエリを実行すると、「sqlmin!ISSResourceAsynch::DoTMWork」でブレークされます。

image

この時に、各サーバーでコンポーネントサービスから MS DTC の作業ユニットの情報を見ると、「準備中」で停止された状態となります。

image

分散トランザクション内で処理を行っていますので、UOW については、同一の GUID が設定されていることが確認できますね。

DoTMWork でブレークさせていますので、準備フェーズに入っているかその直前で停止されているかと思います。

image

ローカルのインスタンスの SSMS でクエリを実行しているのですが、10.80.0.1 からの応答が待機されているため、処理が途中で止まっているような状態です。

image

この状態から、処理を再開させるため windbg で、実行を継続させます。

SSMS に関しては処理が完了し、MSDTC からもトランザクションが完了していることが確認できます。

image

それでは、windbg を実行している 10.80.0.1 はどうでしょうか?

再度、ブレークポイントで停止しているのですが、こちらの MSDTC については「コミット中」でトランザクションが残った状態となっています。

image

この状態は、分散トランザクションのコミットフェーズにおいて、複数のインスタンスのうち、特定のインスタンスのみコミットが最後まで完了していない状態が作り出せたのではないでしょうか。

MS DTC が起動している状態ですと、トランザクションの自動的な解決が行われてしまいますので、この状態で、 MSDTC を「停止して無効化」の状態にしておきます。

image

その後、ブレークポイントに該当したスレッドを一時的に凍結し、再度実行を再開して、トランザクションログの内容を確認してみると、「LOP_PREP_XACT」で処理が停止していることが確認できます。

image

ローカルインスタンスで同様の内容を確認してみるとこちらは「LOP_COMMIT_XACT」となっており、コミットまで完了している状態となっていることが確認できます。

image

これで分散トランザクション下でコミット状態の不整合を発生させることができましたので、windbg を終了して、SQL Server のプロセスを落とし、再度サービスを再起動します。

現在、10.80.0.1 上では、MSDTC のサービスが起動しないようにしているため、MSDTC によるトランザクションの解決はできず、先ほどの UOW がインダウトトランザクションとして認識されていることが確認できます。

image

 

本当は MSDTC を起動した状態でインダウトトランザクションを作り出したかったのですが、タイミングを制御するのが大変そうなので、ひとまず、 インダウトトランザクションを UOW で解決する検証ができるところまでで妥協してしまいました…。

Written by Masayuki.Ozawa

7月 14th, 2021 at 10:13 pm

Posted in SQL Server

Tagged with

Leave a Reply

Share via
Copy link
Powered by Social Snap