WordPressで自前のクエリを書きたい

Question

###前提・実現したいこと
WordPressでON DUPLICATE KEY UPDATEの結果、INSERT か UPDATE か判別したいです。
そこで$wpdbを使わず自前のクエリを書きたいのです。

###発生している問題
自前のクエリですと処理が異常に遅くなるので、これを解消したいです。
具体的には、jQueryはtimeoutしますし、実行から数分はphpMyAdminから当該テーブルwp_actions_testへもアクセスできなくなります。

###テーブルのCREATE

CREATE TABLE IF NOT EXISTS wp_actions_test (
 ID              BIGINT(20)     UNSIGNED NOT NULL AUTO_INCREMENT
,users_ID        BIGINT(20)     UNSIGNED NOT NULL
,action_name_id  TINYINT(3)     UNSIGNED NOT NULL
,content_id      BIGINT(20)     UNSIGNED NOT NULL					
,created_at      DATETIME(6)    NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
,updated_at      DATETIME(6)    NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)
,is_deleted      TINYINT(1)     UNSIGNED NOT NULL DEFAULT 0
,PRIMARY KEY  (ID)
,UNIQUE unique_wp_actions_test (users_ID,content_id,action_name_id)
);

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

function ajax_update_actions_test(){
	
	// wp_actions_testへINSERTする値
	const actions = {
		'users_ID'        : 1,   // 実行者
		'action_name_id'  : 10,  // アクション種別
		'content_id'      : 100, // 対象コンテンツ
	};
	
	$.ajax({
		url: "http://example.com/wp-admin/admin-ajax.php",
		type: "POST",
		dataType: "json",
		timeout: 10000,
		data: {
			action: "my_ajax_update_actions_test",
			actions: actions
		}
	}).done(function(response) {	
		console.log(response);
	}).fail(function() {
		console.log('error');
	});			
}

// ajax処理
add_action( 'wp_ajax_my_ajax_update_actions_test', 'my_ajax_update_actions_test' );
function my_ajax_update_actions_test() {
	global $wpdb;

	$status = 'unexecuted';
	$result = ['status'=>$status];
	try {
		$errors = [];

		// JSから値を受け取る
		$actions = $_POST['actions'] ? $_POST['actions'] : [];

		// テーブルロック
		$wpdb->query('SET autocommit=0');
		$sql = 'LOCK TABLES wp_actions_test WRITE';
		$locked = $wpdb->query($sql);
		if($locked === false){
			$errors['locked'] = [
				'reason' => 'テーブルロックでエラー',
				'wpdb_errors'=> my_get_wpdb_errors()
			];			
		}

		// テーブルロックが問題なければ
		else{

			// wp_actions_testの更新
			$inserted = insert_actions_test($actions);
			if(isset($inserted['errors'])){
				$errors['inserted'] = [
					'reason' => 'wp_actions_testの更新でエラー',
					'result' => $inserted
				];
			}
		}

		if( !empty($errors) ){
			throw $e;
		}else{
			$result = [
				'status' => $inserted['status'],
				'actions' => $inserted['actions'],
			];

			$wpdb->query('COMMIT');
		}		
	} catch (Exception $e) {
		$wpdb->query('ROLLBACK');
		$result = [
			'status' => 'error',
			'errors' => $errors
		];
	} finally {
		$wpdb->query('UNLOCK TABLES'); 
	}

	echo json_encode($result,JSON_UNESCAPED_UNICODE);
	die();
}

// アクションテーブル更新
function insert_actions_test($actions){
	global $wpdb;

	$status = 'unexecuted';
	$result = ['status'=>$status];
	try {
		$errors = [];

		/*
		自前バージョン
		-------------------------------------------*/
		// INSERTか、過去に実行済(is_deleted=1)なら is_deleted=0 に戻す
		$keys = array_keys($actions);
		$vals = array_values($actions);
		$keys = implode( ',', $keys );
		$vals = implode( ',', array_map( function($v){ return '?'; }, $vals ) ); 

		// SQL文生成
		$sql = "
		INSERT INTO wp_actions_test ( {$keys} )
		VALUES ( {$vals} )
		ON DUPLICATE KEY UPDATE is_deleted = 0;";	
		
		// クエリの実行
		$stmt = '';
		$mysqli = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
		if(mysqli_connect_errno()){
			$errors['mysqli'] = [
				'reason' => '接続でエラー',
				'mysqli_connect_error' => mysqli_connect_error()
			];	
		}else{
			if($stmt = $mysqli->prepare($sql)){
				$formats = implode('', array_map( function($v){ return is_numeric($v) ? 's' : 'i'; }, $vals ) );
				$stmt->bind_param($formats, ...$vals);
				$stmt->execute();
				$res = $stmt->affected_rows;						
				$stmt->free_result();
				$stmt->close();

				// INSERT か UPDATE か判別
				$status = 'undecided';
				if( $res === 0 ){
					$status = 'unchange';
				}elseif( $res === 1 ){
					$status = 'insert';
				}elseif( $res === 2 ){
					$status = 'update';
				}
			}
		}

		if( !empty($errors) ){
			throw $e;
		}else{
			$result = [
				'status' => $status,
				'actions' => $actions,
			];	
		}
	} catch (Exception $e) {
		$result = [
			'status' => 'error',
			'errors' => $errors
		];
	}

	return $result;    
}

###試したこと１
上記の自前バージョンを、次のように$wpdbバージョンとすると、処理はすぐに終わり問題解決しました。
しかしこれではINSERT か UPDATE か判別できないので困っています。

// ajax処理
add_action( 'wp_ajax_my_ajax_update_actions_test', 'my_ajax_update_actions_test' );
function my_ajax_update_actions_test() {
	// 該当のソースコードと同じ
}

// アクションテーブル更新
function insert_actions_test($actions){
	global $wpdb;

	$status = 'unexecuted';
	$result = ['status'=>$status];
	try {
		$errors = [];

		/*
		$wpdbバージョン
		-------------------------------------------*/
		// INSERTか、過去に実行済(is_deleted=1)なら is_deleted=0 に戻す
		$keys = array_keys($actions);
		$vals = array_values($actions);
		$keys = implode( ',', $keys );
		$vals = implode( ',', array_map( function($v){
			$type = gettype($v);
			if($type == 'string'){
				return '%s';
			}elseif($type == 'integer'){
				return '%d';
			}
		}, $vals ) ); 

		// SQL文生成
		$sql = "
		INSERT INTO wp_actions_test ( {$keys} )
		VALUES ( {$vals} )
		ON DUPLICATE KEY UPDATE is_deleted = 0;";	
		
		// クエリの実行
		$prepares = $vals;
		$sql = $wpdb->prepare( $sql, ...$prepares );	
		$rows = $wpdb->query( $sql, ARRAY_A );
		if($wpdb->last_error){
			$errors['rows'] = [
				'reason' => 'クエリの実行でエラー',
				'wpdb_errors' => my_get_wpdb_errors()
			];				
		}else{
			// INSERT か UPDATE か判別できない
			$status = 'insert';
		}

		if( !empty($errors) ){
			throw $e;
		}else{
			$result = [
				'status' => $status,
				'actions' => $actions,
			];	
		}
	} catch (Exception $e) {
		$result = [
			'status' => 'error',
			'errors' => $errors
		];
	}

	return $result;    
}

###試したこと２
今度は次のようにテーブルロックをやめたところ、処理はすぐに終わり問題解決しました。
しかし質問のためにミニマムなコードにしましたが、実際テーブルロックは必須です。

// ajax処理
add_action( 'wp_ajax_my_ajax_update_actions_test', 'my_ajax_update_actions_test' );
function my_ajax_update_actions_test() {
	global $wpdb;

	$status = 'unexecuted';
	$result = ['status'=>$status];
	try {
		$errors = [];

		// JSから値を受け取る
		$action_info = $_POST['action_info'] ? $_POST['action_info'] : [];

		// wp_actions_testの更新
		$inserted = insert_actions_test($action_info);
		if(isset($inserted['errors'])){
			$errors['inserted'] = [
				'reason' => 'wp_actions_testの更新でエラー',
				'result' => $inserted
			];
		}

		if( !empty($errors) ){
			throw $e;
		}else{
			$result = [
				'status'      => $inserted['status'],
				'action_info' => $inserted['action_info'],
			];
		}		
	} catch (Exception $e) {
		$result = [
			'status' => 'error',
			'errors' => $errors
		];
	}

	echo json_encode($result,JSON_UNESCAPED_UNICODE);
	die();
}

}

// アクションテーブル更新
function insert_actions_test($actions){
	// 該当のソースコードと同じ
}

###質問まとめ
なぜ自前バージョンで問題が生じるのか、そしてどのように解決させるのか、お分かりになる方がいらっしゃいましたら、ご回答どうぞ宜しくお願い致します。

###環境
WordPress 5.3.3
PHP 7.2.13

Answer 1

おそらくですが、テーブルロックを掛けているコネクションと、Insertするコネクションが異なるため、
テーブルロックが解除されるまで待ちになっているのではないでしょうか。

Comments

[tabuu]

ちなみにですが、actionsにIDが無いのでupdateは発生しないと思います。

[hana_hana]

> おそらくですが

もしかして、次の部分でロックのコネクションが発生して、
```PHP
// テーブルロック
$wpdb->query('SET autocommit=0');
$sql = 'LOCK TABLES wp_actions_test WRITE';
$locked = $wpdb->query($sql);
```

さらに次の部分で新たにコネクションが発生しているということですか？
```PHP
// クエリの実行
$stmt = '';
$mysqli = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
```

なるほど、ありえそうな予感がいたします。
しかしもしそうであれば、どのような解決が考えられますでしょうか？

$wpdbによるロックを引きついだままで、独自クエリを実行しなければならないとなりますと…方法が思いつけません。

[hana_hana]

> ちなみにですが
失礼しました。CREATEにユニーク制約を追加しておきました。

[tabuu]

そもそもですが、(提示されている内容だけ見ると)テーブルロックの必要性がありません。
users_ID,content_id,action_name_idをキーにした複数のリクエストが同時に発生するのでしょうか？
仮に同時に発生してもis_deletedを0にするだけなので被っても問題ないのではないでしょうか？

どうしてもロックしたいということであれば、wpdbでロックでずに
mysqliで生成したコネクションでロックとINSERTを実行すればよいかと思います。

Answer 2

throw $eの$eってちゃんとExceptionなんですか？
そうでないならばthrowしてもcatchされないためにCOMMITもROLLBACKもされず
autocommitも切ってるわけですからTRANSACTIONが終了されないまま
UNLOCK TABLESしようとして失敗してるんじゃないでしょうか？

あるいはトランザクションが開始してない状態で
COMMITやROLLBACKしようとして失敗してるか

Comments

[hana_hana]

ありがとうございます。質問のためにコードは一部変更していますが、$eはExcetionにいきます。
もしご指摘のようにエラーの場合にcatchされないと仮定しても、そもそも該当のソースコードではエラーが起こりませんから、エラーの場合という仮定の上での推測は少し違うのかなと思われます。