SQL Server は、MS DTC (Microsoft Distributed Transaction Cordinator) をトランザクションマネージャーとして使用して、分散トランザクションの実行を行うことができます。
分散トランザクションについては、トランザクションのロックおよび行のバージョン管理ガイド で解説が行われています。
フローについては、1.3.1.2.3.2 Transaction Enlistment and Completion になるかと思いますが、コミットを「準備フェーズ」と「コミットフェーズ」の 2 フェーズで管理を行う 2 フェーズコミット (2PC) により、トランザクションの管理が行われます。
SQL Server の場合、インスタンス内の複数のデータベースにまたがる処理については、管理ガイドでは次のように記載されています。
数のデータベースにまたがる 1 つのSQL Server データベース エンジン内のトランザクションは実質的には分散トランザクションです。 ただし、SQL Server インスタンスは分散トランザクションを内部で処理するため、ユーザーにはローカル トランザクションとして動作しているように見えます。
実質は分散トランザクションではあるのですが、ローカルトランザクションのように見える処理が行われるため、MS DTC は使用されずに、処理が行われていたかと思います。
リンクサーバー / トランザクションスコープ (Transaction Scope) / BEGIN DISTRIBUTED TRANSACTION 等を使用した場合、実行方法によっては、MS DTC を使用した分散トランザクションとして実行され、分散トランザクションで実行される場合には、インダウト (in-doubt) トランザクション (未解決 / 不確定 / 不定トランザクション) の発生についての考慮が必要なケースが出てきます。
コミット時のインダウトトランザクションとしては、上記の図の xa_commit の部分になるかと思います。
分散トランザクションに参加しているトランザクションは最終的には、トランザクションマネージャーからの 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」になっています。
デフォルトの設定では、MS DTC で解決できないトランザクションが確認された場合、データベースを「未確認」(SUSPECT) の状態にし、データベースへのアクセスができない状態にします。
この状態にしてから、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 の図を再掲します。
赤枠で囲っている処理が、ローカルのインスタンスと 10.80.0.1 のそれぞれに対して実行が行われますが、どちらかのインスタンスについて、赤枠の処理が最後まで実行されないようにすることで、インダウトトランザクションを作り出せるのではないでしょうか。
今回は、10.80.0.1 だけコミットが失敗した状態を意図的に作り出してみます。
分散トランザクションの処理を途中で強制的に終了させるのを、バッチで仕込むのは中々大変そうなので、今回は windbg を使用し、ブレークポイントを設定して分散トランザクションのコミットの途中で意図的に停止させます。
どこでブレークポイントを設定すればよいかを小出しに確認していくのは大変なので、今回は、一度上記の分散トランザクションのクエリを実行している最中の stackwalk を事前に取得してみました。
分散トランザクションは dtc / xact などがキーワードとなりますが、これらのキーワードのメソッドが含まれるスタックを探してみると、それらしいものが見つかりましたので、これらに対して windbg でブレークポイントを設定してみます。
不要なものも一部混じっていると思いますが、「10.80.0.1」の「sqlserver.exe」に対して、windbg で、次のようなコマンドでブレークポイントを設定しておきます。
bp sqlmin!XactRM::CommitDTCXact;bp sqlmin!DtcXactRMOnly::CommitDTCXact;bp sqlmin!ISSResourceAsynch::DoTMWork;g
これで上述した分散トランザクションのクエリを実行すると、「sqlmin!ISSResourceAsynch::DoTMWork」でブレークされます。
この時に、各サーバーでコンポーネントサービスから MS DTC の作業ユニットの情報を見ると、「準備中」で停止された状態となります。
分散トランザクション内で処理を行っていますので、UOW については、同一の GUID が設定されていることが確認できますね。
DoTMWork でブレークさせていますので、準備フェーズに入っているかその直前で停止されているかと思います。
ローカルのインスタンスの SSMS でクエリを実行しているのですが、10.80.0.1 からの応答が待機されているため、処理が途中で止まっているような状態です。
この状態から、処理を再開させるため windbg で、実行を継続させます。
SSMS に関しては処理が完了し、MSDTC からもトランザクションが完了していることが確認できます。
それでは、windbg を実行している 10.80.0.1 はどうでしょうか？
再度、ブレークポイントで停止しているのですが、こちらの MSDTC については「コミット中」でトランザクションが残った状態となっています。
この状態は、分散トランザクションのコミットフェーズにおいて、複数のインスタンスのうち、特定のインスタンスのみコミットが最後まで完了していない状態が作り出せたのではないでしょうか。
MS DTC が起動している状態ですと、トランザクションの自動的な解決が行われてしまいますので、この状態で、 MSDTC を「停止して無効化」の状態にしておきます。
その後、ブレークポイントに該当したスレッドを一時的に凍結し、再度実行を再開して、トランザクションログの内容を確認してみると、「LOP_PREP_XACT」で処理が停止していることが確認できます。
ローカルインスタンスで同様の内容を確認してみるとこちらは「LOP_COMMIT_XACT」となっており、コミットまで完了している状態となっていることが確認できます。
これで分散トランザクション下でコミット状態の不整合を発生させることができましたので、windbg を終了して、SQL Server のプロセスを落とし、再度サービスを再起動します。
現在、10.80.0.1 上では、MSDTC のサービスが起動しないようにしているため、MSDTC によるトランザクションの解決はできず、先ほどの UOW がインダウトトランザクションとして認識されていることが確認できます。
本当は MSDTC を起動した状態でインダウトトランザクションを作り出したかったのですが、タイミングを制御するのが大変そうなので、ひとまず、 インダウトトランザクションを UOW で解決する検証ができるところまでで妥協してしまいました…。