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

Question

###問題が発生する状況
以下test_insert_table2()という関数は、wp_mainsとwp_sub1sという２つのテーブルを更新し、片方でもエラーになれば両方共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');

質問事項

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

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

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

テーブル構造

尚、上記wp_mainsとwp_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);
}

Comments

[hope_mucci]

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

[yakan]

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

Answer 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');

Comments

[yakan]

>1番目の質問だけ回答します。

なるほど順番について理解できました。どうもありがとうございます。

[yakan]

>根本原因は...
>データ挿入後の判定処理はcommit側に進んでいるのかrollback側に進んでいるのかどちらですか？

エラーの際はきちんとロールバック処理に進んでいることを、下記のerror_logで確認しました。

else {
error_log('ロールバック処理に進んでいる');
$result['errors'] = ['$result_mains'=>$result_mains,'$result_sub1s'=>$result_sub1s];
$wpdb->query('ROLLBACK');
$wpdb->query('UNLOCK TABLES');
}

ということは「>autocommitが怪しく」になるのかと思いますが、そうなると

>autocommitの設定は0,1のどちらでしょうか？

という設定がよくわかっておりません。
調べたところ、1だと自動でコミットされてしまうためROLLBACKが効かないということになるのでしょうか？

phpMyAdminの画面で
SELECT @@autocommit;
を実行すると「1」と表示されましたが、この確認方法であっていますか？
しかし、質問の処理についてのautocommitを確認すべきですから、ここで「1」と表示されても意味ないのでしょうか？

[hope_mucci]

autocommitの確認方法はあっています。
1だとコマンドを発行するたびにcommitが発生します。トランザクション開始中はその限りではないですが。
MySQLのドキュメントにも明記されていますが、一番下に書かれている「正しい方法」でもう一度やってみてはどうでしょうか（すなわち START TRANSACTIONするのではなくSET autocommit=0から始める方法で）

[yakan]

ご返信どうもありがとうございます。
「正しい方法」を見ていませんでした。早速やってみます。

[yakan]

仰る通り「正しい方法」にある以下のコードでできました。誠にありがとうございます！本当に助かりました…。

$wpdb->query('SET autocommit=0'); // これで0を指定
// $wpdb->query('START TRANSACTION'); // これは不要(0にした場合のトランザクションは、SQL文の実行によって暗黙的に開始するため)
$wpdb->query('LOCK TABLES wp_mains WRITE, wp_sub1s WRITE');

一つよろしいでしょうか。上記ではautocommit=0と指定しているわけですが、指定しなければできないということは通常の処理では1になっているということかと思います。

なので質問のコードが済んだ時点で1に戻すべきかと思うのですが、戻すならば以下のようにUNLOCK TABLESの後に戻すような流れであっているでしょうか？

$wpdb->query('UNLOCK TABLES');
$wpdb->query('SET autocommit=1'); // これで1に戻す

それともこれは自動的に1に戻るような仕組みなのでしょうか？
といいますのは、今改めてphpMyAdminの画面で
SELECT @@autocommit;
を実行すると、やはり「1」と表示されまして、
ということは先ほどの
$wpdb->query('SET autocommit=0'); // これで0を指定
は自動的に1に戻るような仕組みなのかと考えたためです。

[hope_mucci]

autocommitはデフォルト1で、DBとの接続が切れるまで有効になり、また別の接続には影響しません。
しかし、おそらく$wpdbは接続しっぱなしなのでcommit/rollbackした後はautocommitを1に戻しておくことをお勧めします。0のままだと別のページで影響が出るかもしれませんので。

[yakan]

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

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

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

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

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

[hope_mucci]

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

[yakan]

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