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

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

###発生している問題
両方保存するためにトランザクションを使っています。

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

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

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

###該当のソースコード

php
1// wp_mains と wp_sub1s にレコードを保存する
2$result = test_insert_table();
3var_dump($result);
4
5function test_insert_table(){
6	global $wpdb;
7	
8	// 戻り値
9	$result = ['insert_id'=>null,'errors'=>[]]; // $result['errors'] にエラーがあれば ROLLBACK する	
10
11	// トランザクションとロックするテーブル
12	$wpdb->query("START TRANSACTION");
13	$wpdb->query("LOCK TABLES wp_mains WRITE, wp_sub1s WRITE");
14		
15	try{
16		// wp_mains テーブルを更新
17		$update_info = [
18			 'users_ID' => (int)get_current_user_id()
19			,'content'  => '本文' 
20		];		
21		$result_main = test_insert_sql_row( 'wp_mains', $update_info );
22		// エラー
23		if( !empty($result_main['errors']) ){
24			$result['errors']['$result_main'] = $result_main;
25		}
26		
27		// wp_mains テーブル が無事入ったら 
28		else{
29			$result['insert_id'] = $result_main['insert_id'];
30
31			// wp_subs テーブルを更新
32			$update_info = [
33				 'mains_ID' => 999 // $result_main['insert_id'] を渡せばエラーにならないが、999 は外部キーとして存在しないのでエラーになる
34				,'title'	=> 'タイトル' 
35			];
36			$result_sub = test_insert_sql_row( 'wp_sub1s', $update_info );
37			// エラー
38			if( !empty($result_sub['errors']) ){
39				$result['errors']['$result_sub'] = $result_sub;
40			}			
41		}
42
43		// エラーなら ROLLBACK する
44		if( !empty($result['errors']) ){
45			$wpdb->query('ROLLBACK'); // ここで ROLLBACK が効かず、wp_mains にだけ値が入ってしまうのが問題です
46			error_log( 'test_insert_table() - $result = '. json_encode($result, JSON_UNESCAPED_UNICODE) );
47		}else{
48			$wpdb->query('COMMIT');
49		}
50	}catch( Exception $e ){
51		$wpdb->query('ROLLBACK');
52	}finally{
53		$wpdb->query('UNLOCK TABLES'); 
54	}
55	
56	return $result;	
57}		
58
59function test_insert_sql_row( $table_name, $update_info ){ 
60	global $wpdb;
61	
62	// 戻り値
63	$result = ['insert_id'=>null,'errors'=>[]];
64	
65	// 値の型
66	$format_arr = [];
67	foreach ( $update_info as $v ) {
68		$type = gettype( $v );
69		if ( $type == 'string' ) {
70			$format = '%s';
71		} elseif ( $type == 'integer' ) {
72			$format = '%d';
73		}		
74		$format_arr[] = $format;
75	}	
76	
77	// 保存
78	$rows = $wpdb->insert( $table_name, $update_info, $format_arr );	
79	// エラーがあればその内容を入れる
80	if( ! $rows ){ 	
81		$result['errors'] = [
82			'$rows'               => $rows
83			,'$wpdb->last_query'  => $wpdb->last_query
84			,'$wpdb->last_error'  => $wpdb->last_error
85			,'$wpdb->last_result' => $wpdb->last_result
86			,'$wpdb->col_info'    => $wpdb->col_info
87			,'$wpdb->insert_id'   => $wpdb->insert_id
88		];
89	}
90	// エラーがなければIDを入れる
91	else{
92		$insert_id = $wpdb->insert_id;
93		$result['insert_id'] = $insert_id;
94	}
95	return $result;
96}
97

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

PHP
1test_create_table();
2function test_create_table(){
3	global $wpdb;
4	require_once(ABSPATH . 'wp-admin/includes/upgrade.php'); // dbDelta() のため必要
5	
6	// wp_mains テーブルを作成
7	$sql = "CREATE TABLE wp_mains (
8		 `ID`		 BIGINT(20)	UNSIGNED NOT NULL AUTO_INCREMENT
9		,`users_ID`   BIGINT(20)	UNSIGNED NOT NULL
10		,`content`	VARCHAR(1000) 
11		,PRIMARY KEY  (`ID`)
12		,FOREIGN KEY  (`users_ID`)  REFERENCES wp_users(`ID`)
13	);";
14	add_option($table_name."_version", '1.0');
15	dbDelta($sql);
16	
17	// wp_sub1s テーブルを作成
18	$sql = "CREATE TABLE wp_sub1s (
19		 `mains_ID`   BIGINT(20)	UNSIGNED NOT NULL 
20		,`title`	  VARCHAR(50)   NOT NULL
21		,PRIMARY KEY  (`mains_ID`)
22		,FOREIGN KEY  (`mains_ID`)  REFERENCES wp_mains(`ID`)
23	);";
24	add_option($table_name."_version", '1.0');
25	dbDelta($sql);
26}

###試したこと
まず上記ソースコードにありますが、999でなく$result_main['insert_id']を渡せばエラーにならず、wp_mainsもwp_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でした。

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

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

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

回答1件

自己解決

###問題の原因
原因は以下のコード

php
1// トランザクションとロックするテーブル
2$wpdb->query("START TRANSACTION");
3$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）の最後の「正しい方法」に則って以下のようにしなければならない。

php
1// 「正しい方法」に則って以下のようにしなければならない
2$wpdb->query('SET autocommit=0'); // これで0を指定
3// $wpdb->query('START TRANSACTION'); // これは不要
4$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。

php
1// START TRANSACTION だけでOK
2$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になってるという点も疑問…

php
1// 解決方法
2$wpdb->query('SET autocommit=0');
3$wpdb->query("LOCK TABLES wp_mains WRITE, wp_sub1s WRITE");
4	
5// autocommit を確認
6$result['check_autocommit'] = $wpdb->query("SELECT @@autocommit"); // なぜか1になってる