前提

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

実現したいこと

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

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

発生している問題

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

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

テーブル構造

SQL
1-- フルーツテーブル
2CREATE TABLE my_fruits (
3  `ID` int AUTO_INCREMENT, 
4  `count_likes` int(100) DEFAULT 0, 
5  `fruits_name` varchar(1000) not null, # 1000文字なのでユニーク制限ができない
6  PRIMARY KEY(`ID`));
7
8INSERT INTO my_fruits (`count_likes`, `fruits_name`)
9VALUES
10(10,  'フルーツ１'),
11(56,  'フルーツ２'),
12(36,  'フルーツ３');
13
14-- タグテーブル
15CREATE TABLE my_tags (
16  `ID` int AUTO_INCREMENT, 
17  `tag_kind_id` int default 1, 
18  `tag_name` varchar(100),
19   PRIMARY KEY(`ID`),
20   UNIQUE u_my_tags_01 (`tag_kind_id`, `tag_name`));
21  
22INSERT INTO my_tags
23(`tag_kind_id`, `tag_name`) 
24VALUES
25(1,  '赤'),
26(1,  '青'),
27(1,  '緑'),
28(1,  '黄'),
29(1,  '白');
30
31-- リレーションテーブル
32CREATE TABLE my_tag_holders (
33  `fruits_ID` int,
34  `tags_ID` int,
35  PRIMARY KEY  (`fruits_ID`, `tags_ID`), #「フルーツとタグの同じ組み合わせはない」という制限
36  INDEX idx_my_tag_holders_01 (`tags_ID`, `fruits_ID`), 
37  CONSTRAINT fk_my_tag_holders_01 FOREIGN KEY (`fruits_ID`) REFERENCES my_fruits(`ID`),
38  CONSTRAINT fk_my_tag_holders_02 FOREIGN KEY (`tags_ID`) REFERENCES my_tags(`ID`) );
39  
40INSERT INTO my_tag_holders
41(`fruits_ID`, `tags_ID`)
42VALUES
43(1, 1),(1, 2),
44(2, 2),(2, 4),(2, 5),
45(3, 3);

SELECT FOR UPDATE を使ったコード

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

php
1/*
2	●処理の流れ
3	1回目は $with_sleep = true、2回目は false にして
4	1回目の読み込みでsleepしている途中に2回目の読み込みを実行し
5	$friuts_name が重複しないことを確認します。
6
7	●なぜ重複が防止できるか？
8	SELECT FOR UPDATE によって
9	既存のときはネクストキーロックがかかり
10	2回目の読み込みではINSERTされず重複が防止できている
11	というつもりです。
12*/
13
14// 実行
15$with_sleep  = true; // １回目だけ sleep させて、その最中に２回目を実行する
16$fruits_name = 'フルーツ4';
17$result      = test_insert_on_select_for_update_ver_pdo( $fruits_name, $with_sleep );
18var_dump($result);
19
20// 関数
21function test_insert_on_select_for_update_ver_pdo( $fruits_name, $with_sleep ){
22
23	$result = ['status'=>'error'];
24	try {
25
26		// トランザクション
27		$dbh = db_open();
28		$dbh->beginTransaction();
29
30		// 既存確認
31		$sql = "
32			SELECT *
33			FROM   my_fruits
34			WHERE  fruits_name = :fruits_name
35			FOR UPDATE
36		;";
37		$stmt = $dbh->prepare( $sql );
38		$stmt->bindValue(':fruits_name', $fruits_name);
39		$flag = $stmt->execute();
40		if( $flag ) {
41
42			// あれば既存IDを取得
43			$data = $stmt->fetch();
44			$result = [
45				'status'    => 'exists',
46				'fruits_id' => $data['ID']
47			];		
48
49		} else {	
50			throw new Exception("既存確認で失敗しました");	
51		}
52
53		// １回目だけ sleep させて、その最中に２回目を実行する
54		if ( $with_sleep ) sleep(40);
55
56		// なければINSERT
57		if ( $data === false ) {
58			$sql = "
59				INSERT INTO my_fruits ( fruits_name )
60				VALUES( :fruits_name )
61			;";
62			$stmt = $dbh->prepare( $sql );
63			$stmt->bindValue(':fruits_name', $fruits_name);
64			$flag = $stmt->execute();
65			if( $flag ) {
66				$result = [
67					'status'    => 'inserted',
68					'fruits_id' => $dbh->lastInsertId()
69				];		
70			} else {
71				throw new Exception("INSERTで失敗しました");
72			}
73
74		}
75
76		$dbh->commit();
77
78	} catch (PDOException $e) {
79		$result = [
80			'status'    => 'pdoerror',
81			'message'   => $e->getMessage()
82		];
83	} catch (Exception $e) {
84		$result = [
85			'status'    => 'error',
86			'message'   => $e->getMessage()
87		];
88		$dbh->rollback();
89	} finally {
90		$dbh = null;
91	}
92	return $result; 
93}

ツールのバージョン

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

PHP 8.0
MySQL 5.7.2

宜しくお願い致します。

回答1件

ベストアンサー

前回の例で続けるとして

//元テーブル

SQL
1CREATE TABLE my_fruits (
2  ID int primary key auto_increment,
3  count_likes int(100) DEFAULT 0, 
4  fruits_name varchar(1000) not null
5);
6
7INSERT INTO my_fruits (`count_likes`, `fruits_name`)
8VALUES
9(10,  'フルーツ１'),
10(56,  'フルーツ２'),
11(36,  'フルーツ３');

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

//先

SQL
1start transaction;
2create temporary table tmp (
3  `count_likes` int(100) DEFAULT 0, 
4  `fruits_name` varchar(1000) not null
5);
6insert into tmp values
7(90,  'フルーツ２'),
8(91,  'フルーツ３'),
9(92,  'フルーツ４'),
10(93,  'フルーツ５');
11select * from my_fruits for update ;
12select sleep(5); /* 5秒停止 */
13insert my_fruits(count_likes,fruits_name)
14select * from tmp as t1 where not exists 
15(select 1 from my_fruits where fruits_name=t1.fruits_name );
16commit;

//後

SQL
1start transaction;
2create temporary table tmp (
3  `count_likes` int(100) DEFAULT 0, 
4  `fruits_name` varchar(1000) not null
5);
6select * from my_fruits limit 999 for update ;
7insert into tmp values
8(90,  'フルーツ２'),
9(91,  'フルーツ３'),
10(94,  'フルーツ５'),
11(95,  'フルーツ６');
12select * from my_fruits limit 999 for update ;
13insert my_fruits(count_likes,fruits_name)
14select * from tmp as t1 where not exists 
15(select 1 from my_fruits where fruits_name=t1.fruits_name );
16commit;

上記のうち

SQL
1select * from my_fruits for update ;
2select sleep(5); /* 5秒停止 */
3↓↓↓
4select sleep(5); /* 5秒停止 */
5select * from my_fruits for update ;

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

長い文書をユニークに

SQL
1create table tbl(id int primary key auto_increment,val varchar(1000),unique(val));
2
3insert ignore into tbl(val) values
4('
51234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
61234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
71234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
81234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
91234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
101234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
111234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
121234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
131234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
141234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
15'),
16('
171234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
181234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
191234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
201234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
211234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
221234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
231234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
241234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
251234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
261234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
27');

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

投稿2022/08/08 01:14

編集2022/08/09 02:10

yambejp

総合スコア109894

回答へのコメント

nikuatsu

2022/08/08 12:59

どうもありがとうございます。ちょっとご回答の意図を掴み損ねてしまったのでお尋ねさせてください。前回（ https://teratail.com/questions/0avu51fjjjiuf4 ）yambejp様は select for update は用ず、テンポラリーテーブルを作るという方法でご回答頂いたかと思います。そしてその方法を私がphpに落とし込んだコード（ https://onlinephp.io/c/75e85 ）があるのですが、これはこれで問題なく動作したという認識です。しかしなぜ、今回のご回答ではテンポラリーテーブルを作りつつ、さらに select for update を用いる。という方法を採用されているのでしょうか？

nikuatsu

2022/08/08 14:12

あと改めて気づいたのですが、テンポラリーテーブルを作る方法ならば２回目の実行がすぐに完了しますよね。１回目の完了を待つことなく、すぐにINSERTが完了します。ですが select for update を用いる方法ですと、今回のご回答にあるように「あとのSQL文は先のSQLがおわるまで待って実行」されるという点で、やや無駄な時間がかかるようにも思えます。ので、やはりなぜ今回 select for update を加えられたのかが謎です。

yambejp

2022/08/09 00:15

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

nikuatsu

2022/08/09 01:15 編集

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

nikuatsu

2022/08/09 01:17

> そうではないなら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

2022/08/09 01:29

ちょっと再現がむずかしいのですが my_fruitsのnot existsをチェックしながらinsertする処理は検索中にピンポイントで更新（追加）をされた場合は重複は防げません。その競合をさけるためにトランザクションを利用するわけです。そもそもが今回の案件はSQLの競合を弾くユニーク処理をあえて使わないというおかしな要望から出ていることなので正直多少のリスクは見込まないといけないのでしょうけどね。場合によってはデータ投入用の専用テーブルをもう一つ用意してトリガーで処理するなどいくつか代替案はありますが、オーバーヘッドがおおきく無駄なので本質的にはユニーク属性で解決してくださいというのが回答になります

nikuatsu

2022/08/09 02:08 編集

> 検索中にピンポイントで更新（追加）をされた場合は重複は防げません。なんと、そうでしたか。私が上記 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

2022/08/09 02:09 編集

> なんらかの方法でユニーク制約が使えて varchar(1000)をやめてtextにしてみては？本文に追記

nikuatsu

2022/08/09 02:28

まさかすぎます…。誠にありがとうございます。一週間以上これに取り組んでいたのがあっけなくでした。色々な方面での度々のご指導に深く感謝申し上げます。大変勉強になりました。