親関数の LOCK TABLES を、子関数のエラーで ROLLBACK させたい

Question

###前提・実現したいこと
親関数の中で実行される子関数でエラーがおきたとき、親関数のLOCK TABLESをROLLBACKさせたいです。

###発生している問題
親関数のLOCK TABLESをROLLBACKさせることができません。

###該当のソースコード
親関数my_insert_wp_fruitsの中で、子関数my_insert_wp_vegetablesが実行されます。
（それぞれ独自に実行するケースがあるため独立させています。）

さて以下5行目の$valは、子関数の最後に更新されるwp_vegetables2テーブルを更新する値ですが、ここをCREATEで設定したVARCHAR(3)以上の文字にしてエラーを起こしてみました。

しかしROLLBACKが効くのは子関数でLOCK TABLESをかけたwp_vegetables1とwp_vegetables2だけで、wp_fruits1とwp_fruits2はINSERTが成功してしまうのです。

これを親関数のLOCK TABLESにまでさかのぼって、wp_fruits1とwp_fruits2も含めてROLLBACKさせられませんでしょうか？

// ４つのテーブル作成
create_tables();

// 実行
$val = 'val'; // ここを VARCHAR(3) 以上の文字にしてエラーを起こしても、wp_fruits1 と wp_fruits2 は ROLLBACK が効かない
$fruits_datas = [ 'fruits1'=>['col'=>'val'], 'fruits2'=>['col'=>'val'] ];
$vegetable_datas = [ 'vegetables1'=>['col'=>'val'], 'vegetables2'=>['col'=>$val] ];
$result = my_insert_wp_fruits($fruits_datas,$vegetable_datas);
var_dump( $result );

// 親関数 ( wp_fruits1 と wp_fruits2 を INSERT )
function my_insert_wp_fruits($fruits_datas,$vegetable_datas=[]){
	global $wpdb;
	$wpdb->query('SET autocommit=0');
	$sql = "LOCK TABLES wp_fruits1 WRITE, wp_fruits2 WRITE";
	$wpdb->query($sql);

    $result = null; 
    try {

		// wp_fruits1 を保存
		$fruits1 = $wpdb->insert( 'wp_fruits1', $fruits_datas['fruits1'], ['%s'] );	
		if( ! $fruits1 ){
			throw new Exception("fruits1 の INSERT でエラー");
		}else{
			$fruits1_id = $wpdb->insert_id;
			
			// wp_fruits2 を保存
			$fruits2 = $wpdb->insert( 'wp_fruits2', $fruits_datas['fruits2'], ['%s'] );
			if( ! $fruits2 ){
				throw new Exception("fruits2 の INSERT でエラー");
			}else{
				$fruits2_id = $wpdb->insert_id;
				
				// 以下の子関数でエラーが出たとき、親関数を ROLLBACK させたい
				$vegetable_ids = my_insert_wp_vegetables($vegetable_datas);
				if( isset($vegetable_ids['error']) ){
					throw new Exception("my_insert_wp_vegetables でエラー");
				}
			}
		}
		
		// 問題なければ
		$result = ['fruits1_id'=>$fruits1_id,'fruits2_id'=>$fruits2_id];
		$result = array_merge( $result, $vegetable_ids );
		$wpdb->query('COMMIT');
	
	} catch (Exception $e) {
		$wpdb->query('ROLLBACK');
		$result['error'] = $e->getMessage();
	} finally {
		$wpdb->query('UNLOCK TABLES'); 
	}

	return $result; 
}

// 子関数 ( wp_vegetables1 と wp_vegetables2 を INSERT )
function my_insert_wp_vegetables($vegetable_datas){
	global $wpdb;
	$wpdb->query('SET autocommit=0');
	$sql = "LOCK TABLES wp_vegetables1 WRITE, wp_vegetables2 WRITE";
	$wpdb->query($sql);

	$result = null; 
	try {

		// wp_vegetables1 を保存
		$vegetables1 = $wpdb->insert( 'wp_vegetables1', $vegetable_datas['vegetables1'], ['%s'] );
		if( ! $vegetables1 ){
			throw new Exception("vegetables1 の INSERT でエラー");
		}else{
			$vegetables1_id = $wpdb->insert_id;
			
			// wp_vegetables2 を保存
			$vegetables2 = $wpdb->insert( 'wp_vegetables2', $vegetable_datas['wp_vegetables2'], ['%s'] );
			if( ! $vegetables2 ){
				throw new Exception("vegetables2 の INSERT でエラー");
			}else{
				$vegetables2_id = $wpdb->insert_id;
			}
		}
        
		// 問題なければ
		$result = ['vegetables1_id'=>$vegetables1_id,'vegetables2_id'=>$vegetables2_id];
		$wpdb->query('COMMIT');
		
	} catch (Exception $e) {
		$wpdb->query('ROLLBACK');
		$result['error'] = $e->getMessage();
	} finally {
		$wpdb->query('UNLOCK TABLES'); 
	}

	return $result; 
}

// ４つのテーブル作成
function create_tables(){
	global $wpdb;
	$tables = ['fruits1','fruits2','vegetables2','vegetables1'];
	foreach( $tables as $table ){
		$prefixed_table = $wpdb->prefix . $table;
		$sql = "CREATE TABLE IF NOT EXISTS $prefixed_table (
			ID  INT(1)     UNSIGNED NOT NULL AUTO_INCREMENT,
			col VARCHAR(3) NOT NULL,
			PRIMARY KEY  (ID)
		);";
		add_option($table."_version", '1.0');
		$wpdb->query($sql);
	}
}	

###試したこと
自分なりの解決策としては、親関数の方で、「もし子関数を更新するならLOCK TABLESを追加」というコードを下記のように加える方法ですが…

こういった方法よりも、親関数にさかのぼってROLLBACKさせたいと思っています。

ご指導宜しくお願い申し上げます。

function my_insert_wp_fruits($fruits_datas,$vegetable_datas=[]){
	global $wpdb;
	$wpdb->query('SET autocommit=0');
	$sql = "LOCK TABLES wp_fruits1 WRITE, wp_fruits2 WRITE";

	// もし子関数を更新するならLOCK TABLESを追加
	if( !empty($vegetable_datas) ){
		$sql .= ",wp_vegetables1 WRITE, wp_vegetables2 WRITE"
	}

	$wpdb->query($sql);

	// 以下同じ
}

Answer 1

MySQL のバージョンがわかりませんが、5.6以降なら大きな差はなかったと思うので...

親関数のLOCK TABLESをROLLBACKさせることができません。

しかしROLLBACKが効くのは子関数でLOCK TABLESをかけたwp_vegetables1とwp_vegetables2だけで、wp_fruits1とwp_fruits2はINSERTが成功してしまうのです。

MySQL ドキュメント

• 13.3.5 LOCK TABLES および UNLOCK TABLES 構文 に

セッションがすでにロックを保持している間にロックを取得するために LOCK TABLES ステートメントを発行した場合は、新しいロックが付与される前に、その既存のロックが暗黙的に解放されます。

と書かれている部分に該当しているので、子関数の LOCK TABLE を実行した時点で、親関数で実行した LOCK TABLE は、暗黙的に解除されて、子関数の LOCK TABLE でロックされます。

つまり、MySQL の仕様で、LOCK TABLE の実行中に LOCK TABLE を呼び出したり、 トランザクション中に LOCK TABLE することはできません。

---

親関数my_insert_wp_fruitsの中で、子関数my_insert_wp_vegetablesが実行されます。

（それぞれ独自に実行するケースがあるため独立させています。）

の使い分けがわかりませんが、トランザクションを使うのであれば、13.3.1 START TRANSACTION、COMMIT、および ROLLBACK 構文 にあるように

START TRANSACTION; または BEGIN;

SQL 文実行

COMMIT; または ROLLBACK;

で、実行可能です。

WordPress の $wpdb を使って記載するなら、こんな感じでしょうか。

$wpdb->query("START TRANSACTION");

try{
  $result = $wpdb->insert( $table, $data, $format );

  if( is_wp_error( $result ) ){
    $wpdb->query('ROLLBACK');
    // エラー処理が必要なら書く
  } else {
    $wpdb->query('COMMIT');
  }

}catch( Exception $e ){
  $wpdb->query('ROLLBACK');
  // エラー処理が必要なら書く
}

上記の SQL 処理の部分($wpdb->insert())は、外部関数でも問題無いと思います。
（外部関数の中では、START TRANSACTION や LOCK TABLE は使えません。　使うと今回と同じ問題が発生します。）

• 13.3.5.1「テーブルロックとトランザクションの通信
• 13.3.5 LOCK TABLES および UNLOCK TABLES 構文

Comments

[yakan]

＞つまり、MySQL の仕様で、LOCK TABLE の実行中に LOCK TABLE を呼び出したり、 トランザクション中に LOCK TABLE することはできません。

ありがとうございます。納得いたしました。

[yakan]

＞で、実行可能です。

この部分だけお間違いなようなので恐れながら進言させていただきます。
実は、ご呈示の「$wpdb->query("START TRANSACTION");」ではできないみたいなんです。
そのことはこちらの質問（https://teratail.com/questions/266192）で知りましたので、よろしければお試しになってください。

[CHERRY]

あれ？

試したのは数年前なので、記憶違いの可能性もあるので、時間のあるときにテスト環境を用意して確認してみます。