SELECT FOR UPDATE の使い方があっているか知りたいです

Question

前提

下記の テーブル構造 において、「fruits_name が重複しないときだけ INSERT」をしたいと考えています。

実現したいこと

この前提において、下記の SELECT FOR UPDATE を使ったコード であっているのか？間違っていればそれは何か？また懸念点があればどの部分か？などアドバイスを頂きたいです。

尚、これは 以前の質問 で「SELECT FOR UPDATE を使ってはどうか」と教えて頂いたもののそのソースコードはわからず、自分で書いたものになります。

発生している問題

特にネットでは「➀SELECT FOR UPDATE はデッドロックが発生しがち」や「②INSERT なのに SELECT FOR UPDATE を使う必要はない」などのツッコミを拝見するのですが、その詳細はわからず、上記の前提においてどうなのかも想定できません。

尚、上記の前提について①はまったくわからず、②は「SELECT FOR UPDATE を使うことでネクストキーロックがかかり重複回避できる様子なので便利っぽい」と考えていますが…どうなのでしょうか…

テーブル構造

-- フルーツテーブル
CREATE TABLE my_fruits (
  `ID` int AUTO_INCREMENT, 
  `count_likes` int(100) DEFAULT 0, 
  `fruits_name` varchar(1000) not null, # 1000文字なのでユニーク制限ができない
  PRIMARY KEY(`ID`));

INSERT INTO my_fruits (`count_likes`, `fruits_name`)
VALUES
(10,  'フルーツ１'),
(56,  'フルーツ２'),
(36,  'フルーツ３');

-- タグテーブル
CREATE TABLE my_tags (
  `ID` int AUTO_INCREMENT, 
  `tag_kind_id` int default 1, 
  `tag_name` varchar(100),
   PRIMARY KEY(`ID`),
   UNIQUE u_my_tags_01 (`tag_kind_id`, `tag_name`));
  
INSERT INTO my_tags
(`tag_kind_id`, `tag_name`) 
VALUES
(1,  '赤'),
(1,  '青'),
(1,  '緑'),
(1,  '黄'),
(1,  '白');

-- リレーションテーブル
CREATE TABLE my_tag_holders (
  `fruits_ID` int,
  `tags_ID` int,
  PRIMARY KEY  (`fruits_ID`, `tags_ID`), #「フルーツとタグの同じ組み合わせはない」という制限
  INDEX idx_my_tag_holders_01 (`tags_ID`, `fruits_ID`), 
  CONSTRAINT fk_my_tag_holders_01 FOREIGN KEY (`fruits_ID`) REFERENCES my_fruits(`ID`),
  CONSTRAINT fk_my_tag_holders_02 FOREIGN KEY (`tags_ID`) REFERENCES my_tags(`ID`) );
  
INSERT INTO my_tag_holders
(`fruits_ID`, `tags_ID`)
VALUES
(1, 1),(1, 2),
(2, 2),(2, 4),(2, 5),
(3, 3);

SELECT FOR UPDATE を使ったコード

こちらがあっているか不安なコードです。

/*
	●処理の流れ
	1回目は $with_sleep = true、2回目は false にして
	1回目の読み込みでsleepしている途中に2回目の読み込みを実行し
	$friuts_name が重複しないことを確認します。

	●なぜ重複が防止できるか？
	SELECT FOR UPDATE によって
	既存のときはネクストキーロックがかかり
	2回目の読み込みではINSERTされず重複が防止できている
	というつもりです。
*/

// 実行
$with_sleep  = true; // １回目だけ sleep させて、その最中に２回目を実行する
$fruits_name = 'フルーツ4';
$result      = test_insert_on_select_for_update_ver_pdo( $fruits_name, $with_sleep );
var_dump($result);

// 関数
function test_insert_on_select_for_update_ver_pdo( $fruits_name, $with_sleep ){

	$result = ['status'=>'error'];
	try {

		// トランザクション
		$dbh = db_open();
		$dbh->beginTransaction();

		// 既存確認
		$sql = "
			SELECT *
			FROM   my_fruits
			WHERE  fruits_name = :fruits_name
			FOR UPDATE
		;";
		$stmt = $dbh->prepare( $sql );
		$stmt->bindValue(':fruits_name', $fruits_name);
		$flag = $stmt->execute();
		if( $flag ) {

			// あれば既存IDを取得
			$data = $stmt->fetch();
			$result = [
				'status'    => 'exists',
				'fruits_id' => $data['ID']
			];		

		} else {	
			throw new Exception("既存確認で失敗しました");	
		}

		// １回目だけ sleep させて、その最中に２回目を実行する
		if ( $with_sleep ) sleep(40);

		// なければINSERT
		if ( $data === false ) {
			$sql = "
				INSERT INTO my_fruits ( fruits_name )
				VALUES( :fruits_name )
			;";
			$stmt = $dbh->prepare( $sql );
			$stmt->bindValue(':fruits_name', $fruits_name);
			$flag = $stmt->execute();
			if( $flag ) {
				$result = [
					'status'    => 'inserted',
					'fruits_id' => $dbh->lastInsertId()
				];		
			} else {
				throw new Exception("INSERTで失敗しました");
			}

		}

		$dbh->commit();

	} catch (PDOException $e) {
		$result = [
			'status'    => 'pdoerror',
			'message'   => $e->getMessage()
		];
	} catch (Exception $e) {
		$result = [
			'status'    => 'error',
			'message'   => $e->getMessage()
		];
		$dbh->rollback();
	} finally {
		$dbh = null;
	}
	return $result; 
}

ツールのバージョン

それぞれ次のバージョンです。

PHP 8.0
MySQL 5.7.2

宜しくお願い致します。

Answer 1

前回の例で続けるとして

//元テーブル

CREATE TABLE my_fruits (
  ID int primary key auto_increment,
  count_likes int(100) DEFAULT 0, 
  fruits_name varchar(1000) not null
);

INSERT INTO my_fruits (`count_likes`, `fruits_name`)
VALUES
(10,  'フルーツ１'),
(56,  'フルーツ２'),
(36,  'フルーツ３');

上記に対して２つのinsert文を発行するとして
先に発行したSQL文に負荷をかけます（5秒間停止）
あとのSQL文は先のSQLがおわるまで待って実行されます。

//先

start transaction;
create temporary table tmp (
  `count_likes` int(100) DEFAULT 0, 
  `fruits_name` varchar(1000) not null
);
insert into tmp values
(90,  'フルーツ２'),
(91,  'フルーツ３'),
(92,  'フルーツ４'),
(93,  'フルーツ５');
select * from my_fruits for update ;
select sleep(5); /* 5秒停止 */
insert my_fruits(count_likes,fruits_name)
select * from tmp as t1 where not exists 
(select 1 from my_fruits where fruits_name=t1.fruits_name );
commit;

//後

start transaction;
create temporary table tmp (
  `count_likes` int(100) DEFAULT 0, 
  `fruits_name` varchar(1000) not null
);
select * from my_fruits limit 999 for update ;
insert into tmp values
(90,  'フルーツ２'),
(91,  'フルーツ３'),
(94,  'フルーツ５'),
(95,  'フルーツ６');
select * from my_fruits limit 999 for update ;
insert my_fruits(count_likes,fruits_name)
select * from tmp as t1 where not exists 
(select 1 from my_fruits where fruits_name=t1.fruits_name );
commit;

上記のうち

select * from my_fruits for update ;
select sleep(5); /* 5秒停止 */
↓↓↓
select sleep(5); /* 5秒停止 */
select * from my_fruits for update ;

とすると、後のSQLが先に実行されるのがわかります

長い文書をユニークに

create table tbl(id int primary key auto_increment,val varchar(1000),unique(val));

insert ignore into tbl(val) values
('
1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
'),
('
1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
');

２個めのデータは弾かれる

Comments

[nikuatsu]

どうもありがとうございます。ちょっとご回答の意図を掴み損ねてしまったのでお尋ねさせてください。

前回（ https://teratail.com/questions/0avu51fjjjiuf4 ）yambejp様は select for update は用ず、テンポラリーテーブルを作るという方法でご回答頂いたかと思います。

そしてその方法を私がphpに落とし込んだコード（ https://onlinephp.io/c/75e85 ）があるのですが、これはこれで問題なく動作したという認識です。

しかしなぜ、今回のご回答ではテンポラリーテーブルを作りつつ、さらに select for update を用いる。という方法を採用されているのでしょうか？

[nikuatsu]

あと改めて気づいたのですが、テンポラリーテーブルを作る方法ならば２回目の実行がすぐに完了しますよね。１回目の完了を待つことなく、すぐにINSERTが完了します。

ですが select for update を用いる方法ですと、今回のご回答にあるように「あとのSQL文は先のSQLがおわるまで待って実行」されるという点で、やや無駄な時間がかかるようにも思えます。

ので、やはりなぜ今回 select for update を加えられたのかが謎です。

[yambejp]

トランザクションの目的と機能を理解していますか？
ほぼ同時に要求されたクエリの順番を正しく処理するための処理ですから
処理をまって実行する必要があればそうすることができるというだけです
今回の「SELECT FOR UPDATE」の使い方の質問は、まさに
順番を守った処理がしたいということですよね？
そうではないならfor updateをなんのためにしたいのでしょうか？

[nikuatsu]

> トランザクションの目的と機能を理解していますか？
ロールバックしたい範囲の冒頭に宣言するもの、と理解しておりました。

[nikuatsu]

> そうではないならfor updateをなんのためにしたいのでしょうか？
select for update は順番というか、重複を防止するためと考えておりました。

前回の質問へのmaisumakun様のご回答とコメント（ https://teratail.com/questions/0avu51fjjjiuf4#reply-0muqqicjgv9ri4 ）によれば、「select for update がネクストキーロックを起こし、他者の処理介入を防止でき、重複を防止できるだろう」とのことです。（私の理解ですが。）

つまり次の流れを考えますと...

(1) 存在確認
(2) 存在しない
(3) INSERT

...もし (1) が select だけの場合、(2) と (3) の間に他者の INSERT が介入すれば重複していまいます。
しかしもし (1) が select for update の場合、ネクストキーロックが発生し、(2) と (3) の間に他者の INSERT が介入しようとしても、ロックされているために成功しないように防止できる。という狙いです。

以上のように重複を防止するという狙いが念頭にございましたので、

「yambejp様が前回ご回答くださったテンポラリーテーブルの方法ですでにそれは実現できているのに、今回どうして selec tfor update を加えたのか」

という疑問が生じた次第です。

そして恐らくこの疑問へのyambejp様のお答えとしては、

「テンポラリーテーブルだけでも重複は防止できるが、１回目の処理が 'status'=>'exists' で２回目の処理が 'status'=>'inserted' となる。そこでテンポラリーテーブルだけでなく select for update も加えれば、１回目の処理が 'status'=>'inserted' で２回目の処理が 'status'=>'exists' となり、重複の防止だけにとどまらず、順番の維持も可能になるよ」

といった感じでしょうか？

[yambejp]

ちょっと再現がむずかしいのですが
my_fruitsのnot existsをチェックしながらinsertする処理は
検索中にピンポイントで更新（追加）をされた場合は重複は防げません。
その競合をさけるためにトランザクションを利用するわけです。

そもそもが今回の案件はSQLの競合を弾くユニーク処理をあえて使わないという
おかしな要望から出ていることなので正直多少のリスクは見込まないといけないのでしょうけどね。

場合によってはデータ投入用の専用テーブルをもう一つ用意してトリガーで処理するなど
いくつか代替案はありますが、オーバーヘッドがおおきく無駄なので
本質的にはユニーク属性で解決してくださいというのが回答になります

[nikuatsu]

> 検索中にピンポイントで更新（追加）をされた場合は重複は防げません。
なんと、そうでしたか。私が上記 2022/08/08 21:59 に貼った OnlinePHP コードでは、51行目の sleep でそのピンポイントを作ってみたつもりだったのですが、これよりもっとピンポイントなタイミングがあるのですね…残念です。

> その競合をさけるためにトランザクションを利用するわけです。
え？でしたら 書かれているように start transaction したり、私の OnlinePHP のように beginTransaction すればいいということでは？ start transaction や beginTransaction でトランザクションを利用して、not exists をチェックするなら、ピンポイントの懸念も平気。ということではないのでしょうか？

（いや、「トランザクションを利用する」とは「start transaction や beginTransaction だけでなく、その後にさらに select for update する」という意味でしょうか。）

> そもそもが今回の案件はSQLの競合を弾くユニーク処理をあえて使わないというおかしな要望
おかしいですか？長めのテキストにタグを持たせる、今回のようなテーブル構造なんてよくありそうだと思っていたのですが…。
尚ユニークは「あえて使わない」のでなく、varchar(1000)なので「使いたいけど使えない」という認識です。
もしシンプルに、なんらかの方法でユニーク制約が使えて、重複の防止が実現できるなら一番嬉しいです。（でも無理ですよね？）

[yambejp]

> なんらかの方法でユニーク制約が使えて

varchar(1000)をやめてtextにしてみては？
本文に追記

[nikuatsu]

まさかすぎます…。誠にありがとうございます。
一週間以上これに取り組んでいたのがあっけなくでした。

色々な方面での度々のご指導に深く感謝申し上げます。
大変勉強になりました。