【ORACLE】分散トランザクションについて

前に書いた記事の詳細を補足する（見返したら自分で良くわからなかったので）。

舞台となるのはDB LINKでつながっている２つのDBである。（↓のようなイメージ）



●LOCAL DBには「TABLE_A」というテーブルと、「SYNONYM_B」というシノニムがある。
　SYNONYM_BはDB LINKを経由してREMOTE DBの「TABLE_B」に繋がっており、
　LOCAL DB側に実体はない。
●REMOTE DBには「TABLE_B」というテーブルがある。
　このDBはそれ単体で閉じており、DB LINKを経由してLOCAL DBを参照することはない。
●このとき、LOCAL DBに接続してSYNONYM_Bに対してSELECTをかけた直後、
　そのトランザクションは「分散トランザクション」になる。

---

TABLE_A、TABLE_B（SYNONYM_B）はそれぞれ以下のようなデータだったと仮定する。

■TABLE_A

IDEXECDATEVALUEFLG

01	2014/10/01	100	1
01	2014/10/02	105	1
01	2014/10/03	97	0
02	2014/10/04	230	1
02	2014/10/05	215	0
02	2014/10/06	198	0

■TABLE_B(SYNONYM_B)

IDVALUE

01	2
02	19

このテーブル設定は適当である（各項目やテーブルそのものの役割等は何も考えていない）
強いて言うならTABLE_Aが「日別実績」でTABLE_B(SYNONYM_B)は「後追い差分データ」なるイメージか。
通常自システム（LOCAL DB）内で発生するデータを処理して「日別実績」をためこんでいるが
業務の都合上、別システム（REMOTE DB）内で発生するデータを後追いで取り込む必要が出てきた…
とか。
開発時にはLOCAL DBだけ見ていればいいと思ったけど
後々の仕様変更等でREMOTE DBを覗く必要が出てきたといったケースをなんとなくイメージしてみた。
（実体験からきたものではない）

---

どっちかが先でもいいんだが、
ここではTABLE_Bからデータを取得した後、TABLE_Aに更新をかける処理というのを想定してみる。

// ①LOCAL DBへの接続情報を設定
String DB_URL = "jdbc:oracle:thin:@192.168.0.100:1521:LOCALDB";
String DB_USER = "TESTUSER";
String DB_PASS ="TESTPASS";

// ②LOCAL DBへの接続を確立
OracleConnectionPoolDataSource opds = new OracleConnectionPoolDataSource();
opds.setURL(DB_URL);
opds.setUser(DB_USER);
opds.setPassword(DB_PASS);

Connection con = opds.getConnection();

// ③SYNONYM_Bにアクセス
Statement st = con.createStatement();
ResultSet rs = st.executeQuery("SELECT * FROM SYNONYM_B"); // ←この時点で分散トランザクションになる
// （※２）
// ④TABLE_Aを更新
while(rs.next()) {
	String id = rs.getString(0);
	String value = rs.getString(1);
	Statement st2 = con.createStatement();
	st2.executeUpdate("UPDATE TABLE_A SET VALUE = VALUE + " + value + " WHERE FLG = '0' AND ID = '" + id + "'"); // ←（※１）
	st2.close();
}

con.commit();

st.close();
rs.close();

上にあるように、③でSYNONYM_Bにアクセスした時点（ResultSetがかえってきた段階）で、
このトランザクションは「分散トランザクション」になっている。
厄介なのが”参照（SELECT）しかしていない”にも関わらずトランザクションの性質が変化するということだ。

分散トランザクションは「ORA-02049：分散トランザクションがロックを待機しています」という問題を抱えることになる。
（※１）ではLOCAL DB側を更新（UPDATE）しているが、このとき、更新対象のTABLE_Aがロックされていると、
分散トランザクション特有の仕様で、デフォルトでは60秒待機してもロックが解放されない場合、上記のORA-02049が発生して異常終了となる。
この値はORACLEのパラメータ「DISTRIBUTED_LOCK_TIMEOUT」に保持していて、
下記のALTER文で変更できる（SYSDBAで実施）がORACLEの再起動を要する。

alter system set DISTRIBUTED_LOCK_TIMEOUT = 120 scope=spfile;

この例は120秒（２分）に変更している。

分散トランザクションは、とりあえず途中でCOMMITすれば解除されるので、
上の例でいうと（※２）の箇所でCOMMITすればその後はふつうのトランザクションになり、
後続処理における（※１）に挙げたORA-02049発生の懸念もなくなる。
ただ（※２）時点ではまだSELECTしかしてないので、なんでここでCOMMITする必要あるんだ？という疑問は当然湧いてくるし、
処理内容によっては安易にCOMMIT出来ないケースもあるだろう。
この仕様はOracle7の頃からの仕様らしくて割と昔から存在するもののようだが、
こういった動きをすることはなんだか納得がいかない。

余談だが、（※１）のUPDATEではSYNONYM_B.VALUE(=TABLE_B.VALUE)をString（文字列）で受け取り、
かつ数値のチェックをすることなくそのままUPDATEに入っているので、
万が一数値以外が来たとしたらORA-02049以前にUPDATEに失敗して落ちる。
ただ、ここではサンプルという意味で、あと面倒だし特にその辺を書いていない。
テーブル定義的に必ず成功すると思っていただければ。