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

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

ただいまの
回答率

87.58%

MySQL で ROLLBACK が効かず、一部のテーブルだけが更新されてしまう ( WordPress )

解決済

回答 1

投稿 編集

  • 評価
  • クリップ 2
  • VIEW 1,977

score 17

前提・実現したいこと

「親テーブルwp_mains」と「子テーブルwp_sub1s」があり、両方が同時に保存されなければならない仕様です。
そのため片方にエラーが出た際はROLLBACKを効かせたいです。

発生している問題

両方保存するためにトランザクションを使っています。

ソースコードは以下で、wp_sub1smains_IDは外部キーであり、存在しない999の保存はエラーになります。

そしてエラーになったらROLLBACKさせたい(wp_mainswp_sub1sも保存したくない)のですが、wp_mainsにだけ普通に値が入ってしまいます。

つまりROLLBACKが効かないのです。

該当のソースコード

// wp_mains と wp_sub1s にレコードを保存する
$result = test_insert_table();
var_dump($result);

function test_insert_table(){
    global $wpdb;

    // 戻り値
    $result = ['insert_id'=>null,'errors'=>[]]; // $result['errors'] にエラーがあれば ROLLBACK する    

    // トランザクションとロックするテーブル
    $wpdb->query("START TRANSACTION");
    $wpdb->query("LOCK TABLES wp_mains WRITE, wp_sub1s WRITE");

    try{
        // wp_mains テーブルを更新
        $update_info = [
             'users_ID' => (int)get_current_user_id()
            ,'content'  => '本文' 
        ];        
        $result_main = test_insert_sql_row( 'wp_mains', $update_info );
        // エラー
        if( !empty($result_main['errors']) ){
            $result['errors']['$result_main'] = $result_main;
        }

        // wp_mains テーブル が無事入ったら 
        else{
            $result['insert_id'] = $result_main['insert_id'];

            // wp_subs テーブルを更新
            $update_info = [
                 'mains_ID' => 999 // $result_main['insert_id'] を渡せばエラーにならないが、999 は外部キーとして存在しないのでエラーになる
                ,'title'    => 'タイトル' 
            ];
            $result_sub = test_insert_sql_row( 'wp_sub1s', $update_info );
            // エラー
            if( !empty($result_sub['errors']) ){
                $result['errors']['$result_sub'] = $result_sub;
            }            
        }

        // エラーなら ROLLBACK する
        if( !empty($result['errors']) ){
            $wpdb->query('ROLLBACK'); // ここで ROLLBACK が効かず、wp_mains にだけ値が入ってしまうのが問題です
            error_log( 'test_insert_table() - $result = '. json_encode($result, JSON_UNESCAPED_UNICODE) );
        }else{
            $wpdb->query('COMMIT');
        }
    }catch( Exception $e ){
        $wpdb->query('ROLLBACK');
    }finally{
        $wpdb->query('UNLOCK TABLES'); 
    }

    return $result;    
}        

function test_insert_sql_row( $table_name, $update_info ){ 
    global $wpdb;

    // 戻り値
    $result = ['insert_id'=>null,'errors'=>[]];

    // 値の型
    $format_arr = [];
    foreach ( $update_info as $v ) {
        $type = gettype( $v );
        if ( $type == 'string' ) {
            $format = '%s';
        } elseif ( $type == 'integer' ) {
            $format = '%d';
        }        
        $format_arr[] = $format;
    }    

    // 保存
    $rows = $wpdb->insert( $table_name, $update_info, $format_arr );    
    // エラーがあればその内容を入れる
    if( ! $rows ){     
        $result['errors'] = [
            '$rows'               => $rows
            ,'$wpdb->last_query'  => $wpdb->last_query
            ,'$wpdb->last_error'  => $wpdb->last_error
            ,'$wpdb->last_result' => $wpdb->last_result
            ,'$wpdb->col_info'    => $wpdb->col_info
            ,'$wpdb->insert_id'   => $wpdb->insert_id
        ];
    }
    // エラーがなければIDを入れる
    else{
        $insert_id = $wpdb->insert_id;
        $result['insert_id'] = $insert_id;
    }
    return $result;
}


テーブルのCREATEは以下です。

test_create_table();
function test_create_table(){
    global $wpdb;
    require_once(ABSPATH . 'wp-admin/includes/upgrade.php'); // dbDelta() のため必要

    // wp_mains テーブルを作成
    $sql = "CREATE TABLE wp_mains (
         `ID`         BIGINT(20)    UNSIGNED NOT NULL AUTO_INCREMENT
        ,`users_ID`   BIGINT(20)    UNSIGNED NOT NULL
        ,`content`    VARCHAR(1000) 
        ,PRIMARY KEY  (`ID`)
        ,FOREIGN KEY  (`users_ID`)  REFERENCES wp_users(`ID`)
    );";
    add_option($table_name."_version", '1.0');
    dbDelta($sql);

    // wp_sub1s テーブルを作成
    $sql = "CREATE TABLE wp_sub1s (
         `mains_ID`   BIGINT(20)    UNSIGNED NOT NULL 
        ,`title`      VARCHAR(50)   NOT NULL
        ,PRIMARY KEY  (`mains_ID`)
        ,FOREIGN KEY  (`mains_ID`)  REFERENCES wp_mains(`ID`)
    );";
    add_option($table_name."_version", '1.0');
    dbDelta($sql);
}

試したこと

まず上記ソースコードにありますが、999でなく$result_main['insert_id']を渡せばエラーにならず、wp_mainswp_sub1sも問題なく入りました。

次にROLLBACKすべき条件分岐に差し掛かっているかどうかを確認するため、error_log( '$result = ' . json_encode($result, JSON_UNESCAPED_UNICODE) );を書いており、これは出力されました。よって// エラーなら ROLLBACK するというのは分岐できているようです。

なのにこの一行前の$wpdb->query('ROLLBACK'); // ここで ROLLBACK が効かず、wp_mains にだけ値が入ってしまうのが問題ですのROLLBACKが効かないということです。

続いてこちらの記事(http://wxy.crap.jp/program/wordpress/transaction/)を見つけ、ストレージエンジンがInnoDBでないとROLLBACKが効かないのだという情報を得まして確認したのですが、すべてのテーブルはInnoDBで間違いございませんでした。

バージョン情報等

WordPressは5.4です。
MySQLはselect version();10.0.33-MariaDBでした。

上記ソースコードで間違っている点、あやしい点、なおすべき点などお心あたりのある方がいらっしゃいましたらご回答いただけましたら幸いでございます。

初心者のコードにつき冗長であったり見にくかったり等あるかと思いますが、どうぞよろしくお願い致します。

  • 気になる質問をクリップする

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

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

    クリップを取り消します

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

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

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

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

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

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

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

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

    質問の評価を下げる

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

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

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

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

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

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

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

    詳細な説明はこちら

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

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

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

回答 1

check解決した方法

0

問題の原因

原因は以下のコード

// トランザクションとロックするテーブル
$wpdb->query("START TRANSACTION");
$wpdb->query("LOCK TABLES wp_mains WRITE, wp_sub1s WRITE");

解決方法

LOCK TABLESする場合、ドキュメント(https://dev.mysql.com/doc/refman/5.6/ja/lock-tables-and-transactions.html)の最後の「正しい方法」に則って以下のようにしなければならない。

// 「正しい方法」に則って以下のようにしなければならない
$wpdb->query('SET autocommit=0'); // これで0を指定
// $wpdb->query('START TRANSACTION'); // これは不要
$wpdb->query('LOCK TABLES wp_mains WRITE, wp_sub1s WRITE');

なぜならデフォルトではautocommit=1となっており、するとSQLは実行されるたびにコミットされ、ROLLBACKが効かなくなるため。

尚、なぜSTART TRANSACTIONが不要かというと、このページ(https://open-groove.net/mysql/autocommit/)によれば、0にした場合

>この場合のトランザクションは、SQL文の実行によって暗黙的に開始する

とのこと。

補足

ちなみに、LOCK TABLESしない場合ならば、以下のようにSTART TRANSACTIONだけでOK。

// START TRANSACTION だけでOK
$wpdb->query("START TRANSACTION");

なぜなら、同ページ(https://open-groove.net/mysql/autocommit/)によれば次の通りで、

>オートコミットを有効にした場合(デフォルトで有効)
>1.START TRANSACTION文を実行した場合
>単一のSQL文が実行されただけではコミットせず、COMMIT文を実行した時点で初めてコミットされる。ROLLBACK文を実行すればロールバックする。

つまりautocommit=1であってもSTART TRANSACTIONだけ実行してやればCOMMIT文の実行までコミットされずROLLBACKが効くため。

残った疑問1

補足にある「>START TRANSACTIONだけ実行してやればCOMMIT文の実行までコミットされず」が正しいならば、問題の原因にあるコードでもコミットされないはずで、問題は発生しないはずで、解決方法のようにautocommit=0を指定する必要はないはず。

それがなぜLOCK TABLESする場合にはautocommit=0を指定しなければならなくなるのかが疑問。

残った疑問2

さらに解決方法でautocommit=0とした直後に、autocommitを確認するとなぜか1になってるという点も疑問…

// 解決方法
$wpdb->query('SET autocommit=0');
$wpdb->query("LOCK TABLES wp_mains WRITE, wp_sub1s WRITE");

// autocommit を確認
$result['check_autocommit'] = $wpdb->query("SELECT @@autocommit"); // なぜか1になってる

投稿

編集

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

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

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

関連した質問

同じタグがついた質問を見る