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

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

ただいまの
回答率

88.81%

トランザクションとテーブルロックはどちらが先ですか?

解決済

回答 1

投稿

  • 評価
  • クリップ 1
  • VIEW 418

yakan

score 17

問題が発生する状況

以下test_insert_table2()という関数は、wp_mainswp_sub1sという2つのテーブルを更新し、片方でもエラーになれば両方共ROLLBACKするという処理です。

この書き方はQiitaのこちらのページ(https://qiita.com/atomo/items/b96ad8808af7e5c8760b)を参考にしました。

ですが、wp_sub1sでエラーを起こした場合、なぜかROLLBACKが処理されずwp_mainsだけが更新されてしまうという問題が発生しました。
$main_id=999の場合は文末の「テーブル構造」によって外部キーエラーが起こります。)

$result2 = test_insert_table2();
var_dump($result2);

function test_insert_table2(){
    global $wpdb;
    $result = ['insert_id'=>null,'errors'=>[]];    
    $user_id = get_current_user_id();

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

    // 保存
    $result_mains = $wpdb->insert( 'wp_mains', ['users_ID'=>$user_id,'content' =>'本文'], ['%d','%s'] );    
    $main_id = 999; // 999 は存在しないのでエラーになる
    //$main_id = $wpdb->insert_id; // これならエラーにならない
    $result_sub1s = $wpdb->insert( 'wp_sub1s', ['mains_ID'=>$main_id,'title'=>'タイトル'], ['%d','%s'] );    

    if($result_mains && $result_sub1s) {
        $result['insert_id'] = $main_id;
        $wpdb->query('COMMIT');
        $wpdb->query('UNLOCK TABLES'); 
    }
    else {
        $result['errors'] = ['$result_mains'=>$result_mains,'$result_sub1s'=>$result_sub1s];
        $wpdb->query('ROLLBACK'); 
        $wpdb->query('UNLOCK TABLES'); 
    }    
    return $result;
}

解決した方法

一晩格闘し、ふと何の根拠もなくトランザクションとテーブルロックの順番を下記のように逆にしたところ、無事ROLLBACKが起こりました。

    // $wpdb->query('START TRANSACTION');
    // $wpdb->query('LOCK TABLES wp_mains WRITE, wp_sub1s WRITE');
    // ↓
    $wpdb->query('LOCK TABLES wp_mains WRITE, wp_sub1s WRITE');
    $wpdb->query('START TRANSACTION');

質問事項

そこで質問なのですが、トランザクションとテーブルロックの順番は、
1つ目のコードのように「トランザクション→テーブルロック」ではなくて
2つ目のコードのように「テーブルロック→トランザクション」であっているのでしょうか?

MySQLのドキュメンテーションは読みましたが、この点についての言及がなく(あるのに読解できないせいか)、よく理解できません。

そして1つ目のコードでは、なぜwp_mainsだけが更新されROLLBACKが処理されないのか、原因がわかる方がいらっしゃいましたら教えて頂けませんでしょうか。

テーブル構造

尚、上記wp_mainswp_sub1sのテーブル構造は次のようになっているので、$main_id=999の場合は外部キーエラーが起こるといった仕組みです。

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);
}
  • 気になる質問をクリップする

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

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

    クリップを取り消します

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

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

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

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

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

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

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

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

    質問の評価を下げる

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

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

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

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

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

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

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

    詳細な説明はこちら

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

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

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

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

  • hope_mucci

    2020/05/31 12:48

    データ挿入後の判定処理はcommit側に進んでいるのかrollback側に進んでいるのかどちらですか?autocommitの設定は0,1のどちらでしょうか?

    キャンセル

  • yakan

    2020/05/31 13:41

    返信は頂戴したご回答へのコメント欄に記載させて頂きました。宜しくお願い致します。

    キャンセル

回答 1

checkベストアンサー

+1

1番目の質問だけ回答します。
テーブルロックの方法は「トランザクション→テーブルロック」が正解です。
なぜならSTART TRANSACTIONを実施すると既存のロックは全て解消されてしまうからです。つまり、テーブルロックを先に行っても意味なし。
質問者がリンクを張ったMySQLのドキュメントにしっかり明記されていますのでもう一度読んでみてください。

根本原因は追記依頼にも書きましたが、きちんとロールバック処理に進んでいればautocommitが怪しく、コミット処理側に進んでいればsub1へinsertした際の戻り値が怪しいです。

追記

コメントより、以下の方法でうまく動作したそうです。autocommitの値を変更する方法。

$wpdb->query('SET autocommit=0'); // これで0を指定
// $wpdb->query('START TRANSACTION'); // これは不要
$wpdb->query('LOCK TABLES wp_mains WRITE, wp_sub1s WRITE');

投稿

編集

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2020/05/31 14:21

    なるほど、重ねがさねありがとうございます。

    そして誠に僭越ながら、先のリンクの文末に次のように書かれてありました。

    >autocommit = 1 を指定すると、LOCK TABLES の呼び出しの直後に InnoDB によって内部のテーブルロックが解放され、デッドロックが非常に発生しやすくなる場合があるため、この指定は行わないようにしてください。

    これに従い1にはしない方がいいのか、それとも仰るように「>$wpdbは接続しっぱなし」に鑑みて1にした方がいいのか、判断がつきません。

    そこでお尋ねしたいのですが、「>別ページでの影響」とは例えばどのようなものでしょうか。

    キャンセル

  • 2020/05/31 15:46

    autocommit=1の状態でLOCKをすると悪影響が出かねない、ということなので次のLOCKが発生する前に0に戻してやればよい、ということになります。
    別ページでの影響というのは、複数のページに同時にアクセスした場合にロックやautocommitの競合が発生することがあるかもしれないという懸念ですが、あまりphpの挙動に詳しくはないのですがphpはシングルスレッドで動く(同時にリクエストが発生しても処理自体は並行で動かない、順番待ちをする)ということなのでphpでは影響がないのでしょう。javaなど他の言語で開発している身としては恐ろしい話なのですが。

    キャンセル

  • 2020/05/31 16:13

    なるほど。最後まで丁寧に教えて下さいまして誠にありがとうございます。大変勉強になりました。

    キャンセル

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

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

関連した質問

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