INSERT IGNORE を使うタイミングと正しい記述

Question

前提

該当のソースコードのように３つのテーブルがあります。

my_fruits : フルーツテーブル
my_tags : タグテーブル
my_tag_holders : リレーションテーブル

そして my_tag_holders の主キー（34行目）によって 「フルーツとタグの同じ組み合わせはない」 という制限をしています。

発生している問題

「フルーツとタグの同じ組み合わせはない」 という制限のために INSERT IGNORE を使うつもりですが、そのタイミングと正しい記述がわかりません。

該当のソースコード

-- フルーツテーブル
CREATE TABLE my_fruits (
  `ID` int AUTO_INCREMENT, 
  `count_likes` int(100) DEFAULT 0, 
  `content` varchar(100) not null,
  PRIMARY KEY(`ID`));

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

-- タグテーブル
CREATE TABLE my_tags (
  `ID` int AUTO_INCREMENT, 
  `tag_kind_id` int, 
  `tag_name` varchar(100),
   PRIMARY KEY(`ID`));
  
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  (`tags_ID`, `fruits_ID`), #「フルーツとタグの同じ組み合わせはない」という制限
  CONSTRAINT fk_01 FOREIGN KEY (`fruits_ID`) REFERENCES my_fruits(`ID`),
  CONSTRAINT fk_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);

-- タグテーブルにユニーク制約を追加（ sazi様からのご指摘 2022/07/31 20:54 を受けての追加 ）
ALTER TABLE my_tags ADD CONSTRAINT UNIQUE u_01 (`tag_kind_id`, `tag_name`);

具体例とご質問

例えばあるユーザーが次の値をPOSTするという例で考えますと、下の方は 先述の制限 によってエラーとなります。（既にフルーツ３の緑はINSERT済み）

[
	// これは投稿できる
	[ 'content'=>'フルーツ３', 'tag_name'=>'赤' ],
	// これは「フルーツとタグの同じ組み合わせはない」という制限によりエラー
	[ 'content'=>'フルーツ３', 'tag_name'=>'緑' ]
]

しかし当然エラーによって同じ組み合わせを防ぐのではなく「既存確認」の処理を挟むべきだろうと考えているのですが、では「既存確認」はどのように書いたらよろしいでしょうか？

INSERT IGNORE を使うのかと想像はできるのですが、テーブルが３つにまたがっているせいで書き方がわかりません。

もしテーブルが１つならば次のようにすればいいと思いますが…

INSERT IGNORE INTO my_fruits
(`content`, `tag_name` )
VALUES
('フルーツ３', '赤'),
('フルーツ３', '緑');

…３つの場合はどうすればいいのでしょうか？

自分で考えられる方法１

自分で考えられるのは２つの方法です。

まず３つのテーブルの INSERT より前に、SELECT で「既存確認」を入れるという方法です。

// SELECT で「既存確認」を入れる
$sth = $dbh->("
SELECT *
FROM my_fruits F
LEFT JOIN my_tag_holders TH ON TH.fruits_ID = F.ID
LEFT JOIN my_tags T ON T.ID = TH.tags_ID
WHERE F.content = $content
AND T.tag_name = $tag_name
;");
$count = $sth->rowCount();

// フルーツとタグの同じ組み合わせがあればdieで中断（質問のための簡易表現）
if ( $count > 0 )
die( 'フルーツとタグの同じ組み合わせが既にあります' );

// my_fruits を INSERT して、$fruits_id を得る
$dbh->query("
INSERT INTO my_fruits (`content`)
VALUES ('$content')
";);
$fruits_id = $dbh->lastInsertId();

// my_tags を INSERT して、$tag_id を得る
$dbh->query("
INSERT INTO my_tags (`tag_name`)
VALUES ('$tag_name')
";);
$tag_id = $dbh->lastInsertId();

// my_tag_holders を INSERT する
$dbh->query("
INSERT INTO my_tag_holders (`tags_ID`, `fruits_ID`)
VALUES ( $tag_id, $fruits_id )
";);

しかしこの方法は、整合性を保つために「既存確認」の前にLOCK TABLES が必要となり負荷が懸念されます。

自分で考えられる方法２

続いて考えれる方法としては、上記のような SELECT での「既存確認」はせず、最後の my_tag_holders への INSERT の際に INSERT IGNORE を使って「既存確認」をするというものです。 最後に ROW_COUNT() して0ならロールバックし整合性を保つつもりです。

// my_fruits を INSERT して、$fruits_id を得る
$dbh->query("
INSERT INTO my_fruits (`content`)
VALUES ('$content')
";);
$fruits_id = $dbh->lastInsertId();

// my_tags を INSERT して、$tag_id を得る
$dbh->query("
INSERT INTO my_tags (`tag_name`)
VALUES ('$tag_name')
";);
$tag_id = $dbh->lastInsertId();

// my_tag_holders を INSERT IGNORE を使って「既存確認」してから INSERT する
$dbh->query("
INSERT IGNORE INTO my_tag_holders (`tags_ID`, `fruits_ID`)
VALUES ( $tag_id, $fruits_id )
";);

しかしこの方法は、失敗を前提としているような印象で違和感を覚えます。私がテーブルが１つの場合の INSERT IGNORE しか知らないためにこの方法に至ったのですが、もし３つのテーブルについて INSERT IGNORE ができれば、my_fruits を INSERT するより以前に「既存確認」ができるので、この違和感もなくなるのではと思いました。

改めまして、INSERT IGNORE のタイミングと正しい記述についてご教示頂けませんでしょうか。

補足情報（FW/ツールのバージョンなど）

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

PHP 8.0
MySQL 5.7.2

宜しくお願い致します。

（尚インジェクションリスクがある点はスルーしてください。）

Comments

[sazi]

my_fruits もmy_tagsPRIMARY KEYはAUTO_INCREMENTですけど更新はあるのでしょうか？
あるとしたら更新とする際の条件は何ですか？

[nikuatsu]

コメントありがとうございます。PRIMARY KEY 以外のカラムは更新の可能性があります。

ただ「条件」というのがイメ―ジできないのですが…

「状況」でよろしければ、例えば「いいねがクリックされたとき」に my_fruits.count_likes が更新されたりします。他にも例えば「やっぱり "赤" の tag_kind_id を 2 に変更しようというとき」に my_tags.tag_kind_id が更新されたりもします。

仰る「条件」がわからず「状況」のご説明になってしまいましたが、これで返信になっているでしょうか…

[sazi]

IDを指定して更新するんですね。
では、my_tag_holdersはIDの中身に関係なくIDの値の組合せという事で良いんでしょうか？
※例えば色を変更して内容的には重複している状況が生まれるわけですけど。

[nikuatsu]

ありがとうございます。まったく仰る通りでした。

その点は次のユニーク制約を付与することで解決できるでしょうか。
ALTER TABLE my_tags ADD CONSTRAINT UNIQUE u_01 ( tag_kind_id, tag_name );

この制約によって、tags.ID=1 の "赤" を "青" に更新しようとしても、すでに存在するので更新できない。これにより「> 色を変更して内容的には重複している状況」を生じなくさせることができる。という目論見です。

[sazi]

UNIQUE制約で、IDとその内容に変更が無い事の完全な保証にはならないですが、そこまで求めるという感じでは無いのでそれ良いかと思います。

Answer 1

my_fruits、my_tags の中間テーブルがmy_tag_holdersという関係ですから、重複が無い時のみ追加というのは、my_tag_holdersにのみ行えば良いと思います。

ただ方法として、INSERT IGNOREは「重複は処理しない」のではなく「エラーは返却せずやれることはやってあげるよ」なので、意図しない結果になるかもしれません。

ですので、select、 insert で安全に行った方が良いと思います。
なかったら insert あったら何もしない

Comments

[nikuatsu]

ありがとうございます。「自分で考えられる方法１」ということですか。その場合、整合性を保つために SELECT の前に LOCK TABLES が必要となると思います。（ロックしないと、Aさんが「フルーツ３・赤」を INSERT する際の SELECT で既存じゃなくとも、その SELECT の直後にBさんがニアミスで「フルーツ３・赤」を INSERT してしまい、「フルーツ３・赤」が重複してしまう恐れがあるかと思います。ロックすればAさんの SELECT と INSERT が完了するまで、Bさんは INSERT ができないためです。）

しかし、わざわざ LOCK TABLES をかければ無駄な待ちができてしまうのではと懸念しているのですが…この懸念点はいかがでしょうか。

[sazi]

であれば、リンク先にあるON DUPLICATE KEY UPDATEでキーを更新する方法もあります。

[nikuatsu]

ありがとうございます。すると「自分で考えられる方法２」のタイミングで、しかし INSERT IGNORE ではなく ON DUPLICATE KEY UPDATE を用いる。ということですね。

ただ先の私のコメントはしょせん素人の懸念なので、やはりsazi様のような方のご意見を伺いたいです。sazi様でしたらリンク先のどちらの方法を採用されますか？

リンク先のご質問は単一テーブルについてであって LOCK TABLES による整合性は不要なので、maisumakun様は「SELECTしてから」を正統だとご回答されているのだと思います。

他方で当質問は複数テーブルについてなので、その整合性を何かで保たねばなりません。この場合 LOCK TABLES でなく ON DUPLICATE KEY UPDATE を用いる。というご意見になりますでしょうか？それとも LOCK TABLES を用いるようなケースもございますか？

[sazi]

> sazi様でしたらリンク先のどちらの方法を採用されますか？
select insertの方ですね。insert into selectにしそうですけど。

> リンク先のご質問は単一テーブルについてであって LOCK TABLES による整合性は不要なので
この質問もmy_tag_holdersについてのみ整合性を取ればいいという認識なのは、回答の最初で述べています。

ただ、競合回避のロックは単一の場合でも考慮は必要です。

> わざわざ LOCK TABLES をかければ無駄な待ちができてしまうのではと懸念しているのですが…この懸念点はいかがでしょうか。

この程度の処理なら待ちの時間は一瞬です。
もし、画面操作を開始して登録するまでを一連のトランザクションと考えているなら、別だし止めた方が良いと思います。

テーブル単位でロックしないと駄目なケースを設計する事は個人的にはありません。
具体的には楽観的排他を採用します。

[nikuatsu]

> この質問もmy_tag_holdersについてのみ整合性を取ればいい
失礼致しました。そうでしたね。

> テーブル単位でロックしないと駄目なケースを設計する事は個人的にはありません。
なるほど、私は「> LOCK TABLES でなく ON DUPLICATE KEY UPDATE を用いる」という二択でしたが、「LOCK TABLES は用いないけど、ON DUPLICATE KEY UPDATE でなく SELECT で整合性を確認する」のですね。

> 具体的には楽観的排他を採用します。
この用語を知らなかったため検索したのですが大変勉強になりました。私の懸念は悲観的排他しか発想になかったせいでした。楽観的排他の方向で考えを進めてみたいと思います。

丁寧なご返信をありがとうございます。助かりました。

[nikuatsu]

すみません、やはり改めて考えてみたものの、理解が及びませんでした。

かなり調べているのですが、本質問の INSERT で整合性を保つためには楽観的排他は使えず、LOCK TABLES が必須ではないでしょうか？

楽観的排他というのは「既存レコードのロックカラムの値で更新可否を判定する」という方法であり UPDATE にのみ使えるものだと理解したためです。

つまり

> ですので、select、 insert で安全に行った方が良いと思います。

と仰ったのは、「LOCK TABLES → select → insert → LOCK TABLES 解除」のことですか？

もしそうでなければ、LOCK TABLES なくして、そして楽観的排他も使えない INSERT 処理において、いかにして整合性を保つのか？（my_tag_holders において既存でないことを判定するのか？排他的処理が可能なのか？）、といった辺りについてもう少しご教示頂けませんでしょうか。

もしかして、質問の CREATE TABLE の設計からおかしいですか？（ネットで調べたTOXI法というものを採用したつもりなのですが…）

[sazi]

>> ですので、select、 insert で安全に行った方が良いと思います。
>と仰ったのは、「LOCK TABLES → select → insert → LOCK TABLES 解除」のことですか？
いいえ、「INSERT IGNORE」を使わずにという事です。
当然、select で存在確認を行った後の追加で重複が発生する場合はありますので、重複エラー検知しての後処理は必要ですが、テーブルのロックは不要です。

更新は行わないこの質問には関係の無い話である楽観的排他の話をコメントで答えた事で、紛らわしくしてしまったようです、すみません。

[nikuatsu]

>すみません。
いえいえいえ、とんでもないです。
楽観的排他はその用語を知れたことで大収穫でしたので感謝しております。

> 重複エラー検知しての後処理は必要ですが、テーブルのロックは不要です。
重複エラー検知に任せるならば、そもそも select による存在確認も不要ではないでしょうか？

つまり
「select → 存在しない → INSERT → ニアミスで存在したため重複エラー」という処理と
「INSERT → 存在すれば重複エラー」 という処理ならば
select せずども重複エラー検知で拾えるため、その必要性がわからないという疑問です。

もしかして、エラーによる検知はなるべく避けるべきだという思想に基づいての select でしょうか？

[nikuatsu]

もしかして、ここで仰っている select とは、select for update のことでしたか！？

[sazi]

いえ、違います。
追加が目的だからselect for updateに意味はありませんので。

> select せずども重複エラー検知で拾えるため、その必要性がわからないという疑問です。
例外処理は、実装的にも性能的にもコストが高いので、通常の処理で例外を使用する事はあまり選択しないので。

[nikuatsu]

> 例外処理は、実装的にも性能的にもコストが高いので、通常の処理で例外を使用する事はあまり選択しないので。
なるほど。select してもしなくてもエラー検知で拾えるが、select しておけばエラー検知の回数を大きく減らせるためにそのコスト減が見込めるのですね。

[nikuatsu]

> 追加が目的だからselect for updateに意味はありませんので。
私も当初そう思ったのですが、あちらの質問（ https://teratail.com/questions/0avu51fjjjiuf4 ）でmaisumakun様が「ネクストキーロック」という方法を教えてくださいまして、 select for update でどうやら実現できるみたいなのです。

実際に（あちらの質問の話ですが） select for update で fruits_name が重複しないことを確認できるコードがこちらです。（これはmaisumakun様のコードでなく select for update という言葉をmaisumakun様に教えて頂き私が考えたコードです。）
https://onlinephp.io/c/f2af6

[sazi]

処理するテーブルに対してのテーブルロックも行ロックもデッドロックが生じやすいので、殆どのところ私は使用しませんので、的確なアドバイスは出来そうにないですね。
ロックはトランザクションをロックするように制御してます。

[nikuatsu]

いえいえいえ、十分なアドバイスを頂戴しております。誠にありがとうございます。
ロックをあまり使用しないというのもあるのですね。私は最近知ったのでなんでもかんでもロックしている状況なので、必要性の判断能力がほしいです。