SQLServer2005 リンクサーバーのトランザクションについて

[OSのVER]:Windows Server 2003 SP2 (x64)
[SQLServerのVER]:2005 Standard Edition SP3

お世話になります。トーシロです。

以下の現象で解決方法がわからず悩んでいます。
解決方法がございましたご教授頂けますでしょうか。
よろしくお願い致します。

状況：

・インスタンスAとインスタンスBがあります。
・インスタンスAにリンクサーバーとしてインスタンスBを登録してあります。
・インスタンスA.テーブルaにJDBC経由でデータをインサートします。
・インスタンスA.テーブルaインサートトリガーXにて、
インスタンスB.テーブルbにデータをインサートします。
・トリガーXはT-SQLで記述しています。
・トリガーX内にBEGIN TRANSACTION、COMMIT TRANSACTIONを記述しています。
（記述しないとBEGINとCOMMITの対応数が違うという内容のエラーとなりました。）
・JDBC経由でのデータのインサートは一連のトランザクション内となります。

JDBC実装内容：

1.setAutoCommit(false)
↓
2.executeUpdate()で複数回のインサート
↓
3.commit()
↓
4.例外発生時はrollback()

・ユーザはインスタンスA、インスタンスB共に「sa」を使用しています。
・MSDTCは「開始」となっています。

上記状況で以下の操作をします。
1件目をインスタンスA.テーブルaにインサート→正常終了するデータをトリガーXにてインスタンスB.テーブルbにインサートします。
2件目をインスタンスA.テーブルaにインサート→制約違反が起きるデータをトリガーXにてインスタンスB.テーブルbにインサートします。
この際にエラーが発生しますが、インスタンスB.テーブルbの1件目のデータがロールバックされず、コミットされてしまいます。
インスタンスB.テーブルbの1件目のデータがコミットされないようにするには、どのような実装が必要になるのでしょうか。
（同一インスタンス内のテーブルではロールバックされます。）

以下実行時エラーです。
2パターンがランダムに出力されました。

パターン1：

Error Code：1206
SQL State：S000118
com.microsoft.sqlserver.jdbc.SQLServerException: Microsoft 分散トランザクション コーディネータ (MS DTC) により、この分散トランザクションがキャンセルされました。
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source)
at com.microsoft.sqlserver.jdbc.IOBuffer.processPackets(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.sendExecute(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteUpdate(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(Unknown Source)
at TestInsert.write(TestInsert.java:86)
at TestInsert.main(TestInsert.java:43)

パターン2：

Error Code：3971
SQL State：S0001
com.microsoft.sqlserver.jdbc.SQLServerException: サーバーはトランザクションを再開できませんでした。説明: 3400000002。
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source)
at com.microsoft.sqlserver.jdbc.IOBuffer.processPackets(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectionCommand(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.sendRollback(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.rollback(Unknown Source)
at TestInsert.write(TestInsert.java:96)
at TestInsert.main(TestInsert.java:43)

以上、よろしくお願い致します。

No.1 ベストアンサー 回答者： jamshid6 回答日時： 2009/03/17 21:25

Javaは使っていないのですが、リンクサーバに関する限り同様の環境で試してみても、そのような事象は確認できませんでした。

したがってあくまで推測の域ですが、ポイントはリンクサーバ側というよりもJDBC側ではないでしょうか？

とりあえず、以下のあたりをお勧めします。
・SQL Server単体で実行して、本当にロールバックされないか試してみる。
　BEGIN TRY
　BEGIN TRAN
　INSERT INTO テーブルa values (...) -- 正常終了するもの
　INSERT INTO テーブルa values (...) -- 制約違反が生じるもの　
　COMMIT TRAN
　END TRY
　BEGIN CATCH
　ROLLBACK TRAN
　END CATCH
・以下のページをチェックして、JDBC経由でのMSDTCが正しく機能しているか確認する。
　http://msdn.microsoft.com/ja-jp/library/ms378931 …

この回答への補足

申し訳ありません。再度検証したところ、動作結果が違いました。
以下のとおりです。

インサートするデータの中身：
1件目：正常データ
2件目：制約違反が起きるデータ
3件目：正常データ

このデータのパターンでJDBC経由とT-SQL単体での確認を行いました。

JDBC経由での結果：
3件目が登録されてしまう
2件目でエラーとなり2件目までロールバックされるが、
3件目が別トランザクションとして処理されている？

T-SQLでの結果：
3件目が登録されない
2件目でエラーとなりロールバックされ、
処理が終了していると考えられる

（T-SQLの記述内容）
　BEGIN TRY
　BEGIN TRAN
　INSERT INTO テーブルa values (...) -- 正常終了するもの
　INSERT INTO テーブルa values (...) -- 制約違反が生じるもの　
　INSERT INTO テーブルa values (...) -- 正常終了するもの
　COMMIT TRAN
　END TRY
　BEGIN CATCH
　ROLLBACK TRAN
　END CATCH

ですのでjamshid6さんのご指摘のとおり、
JDBCの処理方法の問題ということがわかりました。
ありがとうございます＆失礼いたしました。

補足日時：2009/03/18 22:48

この回答へのお礼

ご回答大変ありがとうございます。

・SQL Server単体で実行して、本当にロールバックされないか試してみる。
試してみたところ、同様にロールバックされませんでした。

・以下のページをチェックして、JDBC経由でのMSDTCが正しく機能しているか確認する。
MSDTCの「XA トランザクションを有効にする」のチェックが入っていませんでしたので、チェックを入れMSDTCを再起動した後、インスタンスAとB共にサービスを再起動しました。
そして確認として、JDBCを経由せずに直接インサート文を発行し、試してみましたところ、依然とロールバックされない状況です。

残念ながらJAVA側のソースを書き換えるという手段が取れない状況でして、諦めてSQL CLRのTransactionScopeを使用した実装に書き直そうかと思っています。
※SQL CLRのTransactionScopeを使用した実装ではロールバックされることを確認しました。

大変ありがとうございました。

お礼日時：2009/03/18 12:54