質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

90.10%

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

解決済

回答 4

投稿 編集

  • 評価
  • クリップ 4
  • VIEW 6,018

just

score 88

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

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

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

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

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

    // 事前処理は省略

    // 更新用データベース名の格納された配列
    $corp_list;
    // トランザクション開始
    pg_query("BEGIN");
    // クエリ実行の成否
    $db_ret = 0;
    // 企業数    
    $cnt = 0;
    
    foreach ($corp_list as $key => $val) {
        echo "処理開始 => DB名:{$val} \n";
        
        // PostgreSQLに接続
        $con = pg_connect("dbname=$val その他接続情報");
        if(!$con){
            $db_ret = 1;
            break;
        }
        
        // 更新用SQLの取得、実行
        $query = 更新用SQL;
        $result = pg_query($con, $query);
        if(!$result){
            echo "SQL実行ERROR\n";
            $db_ret = 1;
            break;
        }    
       
        echo "処理終了 => OK\n";
        // PostgreSQL接続クローズ
        pg_close($con);
        $cnt++;
    }

    // トランザクション終了 0:コミット、0以外:ロールバック
    if($db_ret == 0) {
        echo "COMMIT実行\n";
        pg_query("COMMIT");
    }else {
        echo "ROLLBACK実行\n";
        pg_query("ROLLBACK");
    }

    // DB切断
    $dbl->disconnect();
    echo "処理件数 => {$cnt} \n";

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

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • KiyoshiMotoki

    2015/10/20 14:22 編集

    コメント欄に情報が分散してまいりましたので、一度、ご質問の内容をまとめ直してはいかがでしょうか?

    その際、
    ・どのような制限の中で(テーブル定義の変更は難しい、DBは複数かつ可変(?)、など)
    ・何を目的としている、あるいはどんな機能を実装しようとしているのか
    (バックアップや不可分散、ではないんですよね。。)

    を、差し支えない範囲で詳しく書いていただけると、よりjust様がやりたいことに即した回答を得やすくなると思います。

    キャンセル

回答 4

checkベストアンサー

+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/29 18:16

    justさん

    > しかし、COMMIT状況を記憶しておく構造を作る、というのも結局いたちごっこになりそうな気配がありそうですので、
    COMMIT/ROLLBACKの箇所でプログラムが落ちるという事は、プログラム自体に不具合がない限り、ゼロに近い確率です。
    ですので、落ちた事(及び、開きっぱなしの2相トランザクション)を監視できる体制があれば、その通知を受け取って手作業でコンソールから対処する運用がよいと思いますよ。

    > 万が一全ROLLBACKの命令が失敗した場合(途中までCOMMITがされている場合はROLLBACKが失敗するはず)、更新前データからリストアしようと思います。
    これはありえない事をデータベースが保証しています。
    正しいトランザクションIDを指定したCOMMIT/ROLLBACKでエラーは返りませんので、データベース上で失敗するというケースはありません。

    キャンセル

  • 2015/10/29 20:04 編集

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

    キャンセル

  • 2015/10/30 01:28

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

    キャンセル

+2

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2015/10/20 13:45

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

    キャンセル

  • 2015/10/20 13: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

    キャンセル

  • 2015/10/21 15:24 編集

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

    キャンセル

+2

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

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2015/10/20 13:53

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

    キャンセル

-3

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

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

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2015/10/20 00:55

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

    キャンセル

  • 2015/10/20 05:14

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

    キャンセル

  • 2015/10/20 13:52

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

    キャンセル

  • 2015/10/21 21:44

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

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 90.10%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる