前提
下記の テーブル構造 において、「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, 'フルーツ1'), 11(56, 'フルーツ2'), 12(36, 'フルーツ3'); 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; // 1回目だけ sleep させて、その最中に2回目を実行する 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 // 1回目だけ sleep させて、その最中に2回目を実行する 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件
あなたの回答
tips
プレビュー
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
2022/08/08 12:59
2022/08/08 14:12
2022/08/09 00:15
2022/08/09 01:15 編集
2022/08/09 01:17
2022/08/09 01:29
2022/08/09 02:08 編集
2022/08/09 02:09 編集
2022/08/09 02:28