データベースをまたがったトランザクションについて

お世話になります。
現在、複数のデータベース定義を一度に変更するパッチをPHPで書いています。

たとえばtest1,test2,test3というデータベース(テーブル定義は同じ)があった場合、
ひとつずつデータベースに接続してSQLを実行して変更していくのですが、
test3で障害が発生したときtest1,test2の変更をロールバックしたいです。

test1,test2,test3はあくまで例で、データベースは顧客数分あります(顧客毎にDBを分ける設計)。
当然、保存されているデータはデータベース毎に異なりますが、新機能追加にあたり、
データベース定義が変更されることになりました。
手動で変更をかけると人的ミスが起こりうるのでパッチを書くことになった、という経緯があります。

調べたところ、データベースをまたがったトランザクションの例はなかなか
出てこないのですがPHPで実装可能でしょうか？

最初に書いたコードを記載します。

PHP
1	// 事前処理は省略
2
3	// 更新用データベース名の格納された配列
4	$corp_list;
5	// トランザクション開始
6	pg_query("BEGIN");
7	// クエリ実行の成否
8	$db_ret = 0;
9	// 企業数	
10	$cnt = 0;
11	
12	foreach ($corp_list as $key => $val) {
13		echo "処理開始 => DB名:{$val} \n";
14		
15		// PostgreSQLに接続
16		$con = pg_connect("dbname=$val その他接続情報");
17		if(!$con){
18			$db_ret = 1;
19			break;
20		}
21		
22		// 更新用SQLの取得、実行
23		$query = 更新用SQL;
24		$result = pg_query($con, $query);
25		if(!$result){
26			echo "SQL実行ERROR\n";
27			$db_ret = 1;
28			break;
29		}	
30	   
31		echo "処理終了 => OK\n";
32		// PostgreSQL接続クローズ
33		pg_close($con);
34		$cnt++;
35	}
36
37	// トランザクション終了 0:コミット、0以外:ロールバック
38	if($db_ret == 0) {
39		echo "COMMIT実行\n";
40		pg_query("COMMIT");
41	}else {
42		echo "ROLLBACK実行\n";
43		pg_query("ROLLBACK");
44	}
45
46	// DB切断
47	$dbl->disconnect();
48	echo "処理件数 => {$cnt} \n";

これを実行すると、途中でエラーになってROLLBACKを実行しても変更済みのデータベースが
ロールバックされません。
ご意見・ご指摘いただけますでしょうか。

KiyoshiMotoki

2015/10/20 05:24 編集

コメント欄に情報が分散してまいりましたので、一度、ご質問の内容をまとめ直してはいかがでしょうか？その際、・どのような制限の中で（テーブル定義の変更は難しい、DBは複数かつ可変(?)、など）・何を目的としている、あるいはどんな機能を実装しようとしているのか（バックアップや不可分散、ではないんですよね。。）を、差し支えない範囲で詳しく書いていただけると、よりjust様がやりたいことに即した回答を得やすくなると思います。

行動規範の内容に同意します

回答4件

ベストアンサー

他の方も書かれているようにPHPプログラム側では複数DBに対する同一性を保証する事ができません。
しかしPostgreSQLの「2相コミット（2フェーズコミット）」機能を使えばこれを保証する事ができます。
（KatsumiTanakaさんも書かれている機能です）

やり方は以下の通りです。
(1) beginメソッドによってトランザクションを開始する
(2) insert,update,delete等でデータベースに対する更新を実行する
[2015/10/21 17:32追記]
または、alter等でテーブル定義の更新を実行する
※PostgreSQLはalter等のDDL文にもトランザクションが利用できます
(3) prepare transactionコマンドによって2相コミットを開始する
例）prepare transaction 'transaction_id_00000001';
(4) 対象となるすべてのDBに対して(1)-(3)を実行する
(5) コミット/ロールバックを確定する
・コミットの場合：commit preparedコマンド
例） commit prepared 'transaction_id_00000001';
・ロールバックの場合：rollback preparedコマンド
例) commit prepared 'transaction_id_00000001';
※通常のcommit/rollbackは必要ありません
(6) 対象となるすべてのDBに対して(5)を実行する
※"prepared transaction"を発行されたDBに対しては、
必ずcommit/rollback処理を行うようPG上で保証してください。
以上で2相コミットの処理は終了です。

以下、commitまでの簡単なサンプルです。

begin;
insert into employee values (1, 'ariaki', true);
insert into employee values (2, 'foobar', true);
-- [2015/10/21 17:32追記]
-- トランザクション内で以下のようにDDL文が利用できます
alter table employee add column joined date not null;
prepare transaction 'tran20151021164600';
commit prepared 'tran20151021164600';

DBのトランザクション中に"prepare transaction"をすることで、
そのトランザクションに名前をつけて「永続化」させる事ができます。
永続化された状態では、例えばプログラムを終了してもトランザクションは
残り続けますので、後からidを元にcommit/rollbackを行う事ができます。
※逆に言うと永続化されたデータは必ず後からcommit/rollbackしないと
保持（行ロック）され続けるので注意してください。
※現在有効なトランザクションid一覧は"pg_prepared_xacts"テーブル内を
selectすることで確認できます。

2相コミットは上述の通りトランザクションロックが掛かりますので、
定期的にトランザクション残を確認する等、運用は十分に注意してください。

それぞれのコマンドはPostgreSQLのマニュアルを参照してください。

投稿2015/10/21 07:48

編集2015/10/21 08:33

退会済みユーザー

総合スコア0

KiyoshiMotoki

2015/10/21 08:14

ariaki様、横から失礼いたします。 just様のご質問は > 複数の【データベース定義を】一度に変更したい、というものですが、PostgreSQLの2相コミットはDDLに対しても有効なのでしょうか？私はPostgreSQLに疎いのですが、MySQLではALTER文にトランザクションをかけることができないため、その点が気になりました。

退会済みユーザー

2015/10/21 08:26

KiyosiMotoki様、こんにちは。 PostgreSQLはcreate/alter等のDDL文についてもトランザクションが使えます。以下、手元の環境(postgresql9.3.10)にて検証した結果になります。 ---------- testdb=# create table test01(data text); CREATE TABLE testdb=# begin; BEGIN testdb=# alter table test01 rename data to datax; ALTER TABLE testdb=# prepare transaction 'tran01'; PREPARE TRANSACTION testdb=# \d test01 Table "public.test01" Column | Type | Modifiers --------+------+----------- data | text | testdb=# commit prepared 'tran01'; COMMIT PREPARED testdb=# \d test01 Table "public.test01" Column | Type | Modifiers --------+------+----------- datax | text |

KiyoshiMotoki

2015/10/21 09:51

ariaki様検証までしていただき、恐縮です。 PostgreSQLではトランザクションを使用できる旨、理解いたしました。ありがとうございます。

just

2015/10/22 04:34 編集

詳細な対応方法を教えていただきありがとうございます。 PHPプログラムでは複数DBに対する同一性を保証できないのであれば、commitまでの一連の手順をシェルで書くべきでしょうか？人的ミスを無くすために手順を自動化したいのですが、他に良い方法はありますでしょうか。

退会済みユーザー

2015/10/22 04:52

just様特に何も考えなくてもたいていの場合は正しく動きます。しかし、例えばPHPプログラムが途中でStackOverFlowで停止したり、何らかのPHP側のバグによってSegfaultで落ちる可能性もあります。こういった感じでプログラムには常に万が一の可能性があり100%保証できないです。これはPHPもC言語もシェルスクリプトも同様です。ですのでそういった一貫性を保証しなければいけない重要な操作はできる限りDBに委ねるべきで、PostgreSQLの機能を使ったらできるよっていう説明でした。手順の自動化は2相コミットを使えば可能だと思うので是非検討してみてください。

just

2015/10/22 12:06

解決しました。最終的にPHPを使って以下のような実装になりました。 (1)更新用データベース名の格納された配列をループで回して全てのデータベースに接続する。同時にトランザクションを開始し、トランザクションの開始に成功したデータベースのコネクションを別の配列に格納しておく(コネクション配列)。 (2)コネクション配列をループで回してコネクションを取り出しクエリを実行する。クエリの実行に成功したコネクションに「PREPARE TRANSACTION 'データベース名'」を実行し、また別の配列に格納しておく(完了コネクション配列)。途中でエラーとなった場合はフラグをたて、ループを抜ける。 (3)フラグを見て、完了コネクション配列に格納されているコネクションに「COMMIT PREPARED 'データベース名'」または「ROLLBACK PREPARED 'データベース名'」を発行し、処理を終了する。ありがとうございました。ベストアンサーにさせていただきます。

snowfaller

2015/10/27 16:31

(2)の最初のデータベースに対してプリペアを行ってから(3)の最後のデータベースに対してコミットorロールバックが完了するまでの間に、何らかの致命的な障害(phpやphpを実行しているOS・ハードウェア、もしくはデータベースサーバが落ちるなど)が発生した場合のリカバリ方法を検討しておく必要があります。さもなくば、未決着のトランザクション(プリペア状態のままで、コミットもロールバックもされていないトランザクション)をコミットに倒してよいのかロールバックに倒して良いのかが分からず、2フェーズコミットを行う意味がない<(2)でクエリーの発行のみを行い、(3)で通常のコミットorロールバックを行っているのと同じ>からです。以降、色々書いたのですが、ログインし忘れたまま「コメントする」ボタンを押したら、ログイン画面がでて書いた内容が消えてしまったのでここまでとさせていただきます。

just

2015/10/28 01:30

コメントありがとうございます。確かに、物理的な障害や予期せぬエラーに対する考慮が足りていませんでした。途中で予期せぬエラーによって処理が中断してしまった場合に備えて、プリペア状態のトランザクションを全てロールバックするパッチも作成しておこうと思います。幸いprepare transactionで付与するIDがデータベース名と一致していることから、ロールバック処理のみを実行することは可能です。少し補足をしますと今回更新するデータベースが私が入社する前からあるというなかなかに年季モノのシステムのものでして、物理障害よりも論理的な障害の方が怖かったりします。顧客要望で一部データベースを変更していた・・・まず無いとは思いますが、そうなったらデータベースの変更はうまくいきません。本来であれば本番運用しているデータベースからデータを全て抜いてパッチのテストを行うべきところなのですが、諸々の事情でそれが却下されたことから、今回の質問をさせていただいた、という経緯があります。

snowfaller

2015/10/28 15:27

長文です(大変です)。 > プリペア状態のトランザクションを全てロールバックするこれはいけません。たとえば、3つのデータベースを更新・プリペア状態にしたあと、1つ目のデータベースのコミットが完了すると、あとは、コミットに倒す必要があるためです。・プリペア状態のデータベースなし → なにもしなくてよい。・全てのデータベースがプリペア状態 → コミットに倒す。(ロールバックに倒しても良い) は簡単なのですが、一部のデータベースのみがプリペア状態となった時には、・一つでもコミットが完了している → コミットに倒す・一つもコミットが完了していない → ロールバックに倒すと言った判定と処理が必要になります。更新対象データベースの数が決まっており、かつ、コミットの順番も決まっている場合は、・プリペア状態のデータベースなし → なにもしなくてよい。・最初のデータベースがプリペア状態でない→コミットに倒す。・最後のデータベースがプリペア状態でない→ロールバックに倒す。・全てのデータベースがプリペア状態 → コミットに倒す。(ロールバックに倒しても良い) で対応可能です。(といいますか、そうするべきです。さもなければ長い戦いが始まります。以下参照) そうでない場合、・トランザクションIDとそのトランザクション状態を管理するデータベースを作成・処理開始時→トランザクションIDとその状態(コミット開始前)レコード挿入・最初のテーブルのコミット開始前→状態を「コミット中」に更新・全てのテーブルのコミット完了→状態を「コミット完了」に更新という処理を追加する必要があり(もちろん当該データベースは更新処理を行う度にコミット)、障害が発生した際には、・しかかり中のトランザクションIDをキーに当該データベースのレコードを確認・コミット開始前のトランザクション → ロールバックに倒す・コミット中のトランザクション→コミットに倒す・コミット完了のトランザクション→※あり得ないと言うリカバリ処理が必要になります。信頼性の観点から、当該データベースは本来の処理対象のデータベースと別PCに配置するのがベターです。しかしながら、このトランザクション状態を管理するデータベースにも障害が発生する可能性があり、これでも完璧ではないのです。

退会済みユーザー

2015/10/28 15:31

snowfallerさん >> プリペア状態のトランザクションを全てロールバックする > これはいけません。どこが問題でしょうか？ 2相コミットは複数DBを跨がるトランザクションを管理する機構です。全てのDBがprepareできた時点で、それらにCOMMIT可能な事が保証されています。ですので一律同じ動作をさせるのであれば問題ない事と認識しています。上記は通常のトランザクションを想定された回答ではないでしょうか？

snowfaller

2015/10/28 22:40

2フェーズを意識して回答しております。長文過ぎてポイントが分かりづらくなったかも知れません、すみません。質問者さんの処理フローは 1. すべてのデータベースを更新・プリペア状態にする。 2. 1.のあとに、すべてのデータベースをコミットorロールバックする。となっています(これはあっています)。 2つのデータベースを処理対象した場合で、2.で1つ目のデータベースのコミットが完了したあとで、クライアントがダウンしたとします。すると、2つ目のデータベース(だけ)がプリペア状態として残ることになります。そこに、質問者さんの、 > 途中で予期せぬエラーによって処理が中断してしまった場合に備えて、プリペア状態のトランザクションを全てロールバックするパッチを流すと、2つめのデータベースがロールバックされてしまいます(1つ目のデータベースがコミットされいるにもかかわらず)。

退会済みユーザー

2015/10/29 02:24

snowfallerさんご回答ありがとうございます。なるほど、そういった観点の回答だったんですね。 2相コミットにおける最終COMMIT/ROLLBACKのフェーズにおいて、全DBに対して統一の処理をしなければいけない。バッチ処理が途中で落ちたとしても途中までCOMMITが進んでいれば他のDBMもROLLBACKではなくてCOMMITすべきである。それ以外の場合は全DBに対してROLLBACKとする。ということですよね。

snowfaller

2015/10/29 03:41

その通りです。(簡潔で分かり易い!) その「途中までコミットが進んでいるか」の判断を行う為には、手当が必要なのですと言う事をお伝えしたかったのです。

just

2015/10/29 08:40

コメントありがとうございます。たしかに、途中までCOMMITが進んだ状態で落ちた場合は全ROLLBACKでは問題がありますね。しかし、COMMIT状況を記憶しておく構造を作る、というのも結局いたちごっこになりそうな気配がありそうですので、万が一全ROLLBACKの命令が失敗した場合(途中までCOMMITがされている場合はROLLBACKが失敗するはず)、更新前データからリストアしようと思います。丁寧に問題点をご指摘いただき、ありがとうございましたm(_ _)m

退会済みユーザー

2015/10/29 09:16

justさん > しかし、COMMIT状況を記憶しておく構造を作る、というのも結局いたちごっこになりそうな気配がありそうですので、 COMMIT/ROLLBACKの箇所でプログラムが落ちるという事は、プログラム自体に不具合がない限り、ゼロに近い確率です。ですので、落ちた事（及び、開きっぱなしの2相トランザクション）を監視できる体制があれば、その通知を受け取って手作業でコンソールから対処する運用がよいと思いますよ。 > 万が一全ROLLBACKの命令が失敗した場合(途中までCOMMITがされている場合はROLLBACKが失敗するはず)、更新前データからリストアしようと思います。これはありえない事をデータベースが保証しています。正しいトランザクションIDを指定したCOMMIT/ROLLBACKでエラーは返りませんので、データベース上で失敗するというケースはありません。

snowfaller

2015/10/29 12:33 編集

プリペア状態のデータベースをコミットしてしまうと、プリペア状態ではなくなります。コミットしてしまったデータベースを特定することができないため、 > 万が一全ROLLBACKの命令が失敗した場合(途中までCOMMITがされている場合はROLLBACKが失敗するはず) を行うことが出来ません(どうやってこの処理を行う対象データベースとトランザクションIDを特定しますか?)。バックアップからリストアする運用が適用できるのであれば、2フェーズコミットは不要です。既にお気づきだたと思うのですが、最初のコーディングでロールバックされないのは、データベースのコネクションをクローズしてからロールバックしているからです。改良後のコード > (2)コネクション配列をループで回してコネクションを取り出しクエリを実行する。クエリの実行に成功したコネクションに「PREPARE TRANSACTION 'データベース名'」を実行し、また別の配列に格納しておく(完了コネクション配列)。途中でエラーとなった場合はフラグをたて、ループを抜ける。から >「PREPARE TRANSACTION 'データベース名'」を実行を削除し、 > (3)フラグを見て、完了コネクション配列に格納されているコネクションに「COMMIT PREPARED 'データベース名'」または「ROLLBACK PREPARED 'データベース名'」を発行、処理を終了する。のコミットおよびロールバックを通常のものに変更(戻す)すれば良いです。 (3)の処理結果(正常or異常)をメッセージ出力し、問題が発生した(エラーメッセージが出力されている、もしくは正常メッセージが出力されていない)ことが確認された場合には、リストアする運用を発動すれば良いのです。

just

2015/10/29 16:28

なるほど・・・せっかくだからと思い2フェーズコミットを試したかったのですが、リストアの運用が出来るのであれば無理に行う必要はなさそうですね。コードはsnowfaller 様の指摘通りに修正致します。若輩者のためデータベースの更新作業などは初めてなのですが、本番までにしっかりテスト、準備をして臨みたいと思います。ありがとうございます。

行動規範の内容に同意します

テーブル定義は同じ

ということは、test1～3のDBはバックアップか負荷分散の目的で、全く同じデータを格納しているのでしょうか?

であれば、PHP側で各DBのデータを変更するのではなく、DBのレプリケーション機能を使用してはいかがでしょうか?
http://www.postgresql.jp/document/9.4/html/high-availability.html

投稿2015/10/19 16:05

KiyoshiMotoki

総合スコア4791

just

2015/10/20 04:53

テーブル定義は同じですが、データは異なっております。その中でテーブル定義のみを変更することは可能でしょうか？

行動規範の内容に同意します

やりたいと思われることは、2フェーズコミットというキーワードで検索すれば、
必要な情報が取れると思います

投稿2015/10/19 08:25

KatsumiTanaka

総合スコア924

just

2015/10/20 04:45

2フェーズコミットとPHPというキーワードで検索するとInterBase関数というものがヒットしましたが、使い方があまり書いてありません。こちらで要件を満たせるのでしょうか？

KatsumiTanaka

2015/10/20 04:57

InterBase関数は、Firebird/InterBaseに対応したベンダー特有の機能のため、一般的ではありません以下のURLの内容を理解したうえで、開発対象としているDBの関数を使用するのがよいと思います http://www.ogis-ri.co.jp/otc/hiroba/technical/DTP/step2/ PostgreSQLには、PostgreSQL用の関数があります http://php.net/manual/ja/ref.pgsql.php

just

2015/10/21 06:25 編集

ご教示いただきありがとうございます。 PostgreSQL用の関数を使用していますが、どうも上手くいきません。コードを記載しましたので、確認いただけますでしょうか。

行動規範の内容に同意します

try-catchのcatchの中で、test1～test3のロールバック処理を入れれば、
整合性が保たれるかと思います。

ご指摘を受けて以下但し書き追記：
※上記は、コミット時の障害を回避できません。ただし複数DBに対し、コミット時のあらゆる障害に対応できるトランザクションはありませんので、これを許容’（妥協）できない場合は、単一トランザクションで管理できるようなDB設計に修正することオススメします。

投稿2015/10/19 14:44

編集2015/10/19 20:24

TetsujiMiwa

総合スコア1124

KiyoshiMotoki

2015/10/19 15:55

その方法では、データの整合性を保つことはできません。 test1, test2をコミットした後にtest3で障害が発生した場合、test1, test2をコミット前の状態に戻すことができないからです。 http://www.ogis-ri.co.jp/otc/hiroba/technical/DTP/step1/

TetsujiMiwa

2015/10/19 20:14

コミット時の障害は考えてなかったです。とはいえ、これについて、リンク先（また、更にその先のリンク先）を勉強させていただきましたが、「確実な解決方法はない」って結論でした。

just

2015/10/20 04:52

現行、動いているシステムですので、テーブル定義を変更することは難しいと思います。それと、言葉足らずで申し訳ないのですが、test1～3はあくまでも例で、実際は複数の(可変の)データベースをまたぐトランザクションとしたいのです。よってtest1～3は決め打ちでなく、Postgresに登録されているデータベースをループで回すような仕組みにしたいと考えております。

TetsujiMiwa

2015/10/21 12:44

では対案になりますが、トランザクションではなく、DBのコピーを作って、そこにデータ更新をかける。すべての更新が正常に行われたら、元のDBを削除して更新したDBを元のDB名に変更する、一つでも障害が発生すれば更新DBを削除する。というのはいかがでしょうか。・作業中は、他からの接続をさせない・コピー出来るだけのDB領域が必要というのが条件になりますので、サービスを止められない状況下では厳しいとは思いますが。

行動規範の内容に同意します

あなたの回答