lastInsertId()によるDB登録時のidの競合について

Question

DB登録時のuser_idの競合について教えて頂きたいです。

$stmt = $pdo->prepare("INSERT INTO user_data(・・・・) VALUES (・・・・)");
$stmt->execute([・・・・]);

$user_id = $pdo->lastInsertId();

$programs1 = $_SESSION['join2']['programs1_'];
$year1 = $_SESSION['join2']['programs_year1_'];

for($i=0;$i<count($programs1);$i++) {
	$stmt = $pdo->prepare("INSERT INTO user_skill(・・・) VALUES (・・・)");
	$stmt->execute([
		$user_id,
		$programs1[$i],
		$year1[$i]
		]);
}

プログラムの処理の流れはこうですが、

1. user_dataへ登録(ユーザーAによる登録)
2. user_skill を作成(ユーザーAによる登録)

例えば銀行のようにミリ秒でＤＢへのアクセスが頻繁なサービスがあったとします。

1. user_dataへ登録(ユーザーAによる登録)
2. user_dataへ登録(ユーザーBによる登録)
3. user_skill を作成(ユーザーAによる登録)
4. user_skillへ登録(ユーザーBによる登録)

lastInsertId()は最後の追加された行のＩＤを返すとのことですが、この場合、

1. user_skill を作成(ユーザーAによる登録)

のuser_idは
2. user_dataへ登録(ユーザーBによる登録)
のＩＤにならない理由を教えてください。

順序的には
最後に追加された行（lastInsertId()の直近に記述しているテーブル）のＩＤを返すはずなので
競合しないのは何故かと不思議に思っています。

Comments

[KiyoshiMotoki]

lastInsertId メソッド「だけ」を議論することは無意味です。
マニュアルに説明があるように、このメソッドの挙動は「構成しているドライバに依存」しているからです。
http://php.net/manual/ja/pdo.lastinsertid.php

お使いのDB製品の名称を追記すれば、具体的な回答を得やすくなると思います。

Answer 1

Aさん「俺のデータ追加して」(INSERT INTO user_data)
管理者「分かりました(Aさんのデータはuser_id1で登録…と)」
Bさん「俺のデータ追加して」(INSERT INTO user_data)
管理者「分かりました(Bさんのデータはuser_id2で登録…と)」
Aさん「ちょっとスキルのデータ追加するから最後にユーザ登録した番号教えて。俺さっき追加したばっかだから最後の番号のはずだよね」(lastInsertId)
管理者「えーと、user_dataの最後の番号は2番ですね」
Aさん「じゃあこのスキルのデータ2番で登録しといて」
管理者「了解しました」
Bさん「ちょっとスキルのデータ追加するから最後にユーザ登録した番号教えて。俺さっき追加したばっかだから最後の番号のはずだよね」(lastInsertId)
管理者「えーと、user_dataの最後の番号は2番ですね」
Bさん「じゃあこのスキルのデータ2番で登録しといて」
管理者「了解しました」

以上が「もしミリ秒レベルで処理が重なってしまって問題が起きたパターン」です。

Aさんは自分のスキルを登録したつもりがBさんの番号である2番に登録してしまいます。
Webページを閲覧する処理は排他ではなく並列で動いているため、Aさんが登録し終わるまでBさんが待ってくれているわけではありません。こういう行き違いが発生してしまうので、「自分の番号は自分の情報で参照すべき」と何度も伝えております。

Aさん「ちょっとスキルのデータ追加するから最後にユーザ登録した番号教えて。俺さっき追加したばっかだから最後の番号のはずだよね」(lastInsertId)

ではなく

Aさん「俺の番号が知りたい。俺の登録情報は…そうだな、メールアドレスは[e-mail]だ」
管理人「そのデータの番号でしたら1番ですね」

と聞くべきなのです。

Comments

[earnest_gay]

回答ありがとうございます。
内容はとても分かりやすいです。

回答内容からは
$user_id = $pdo->lastInsertId();
を
重複しないユニークな内容を基にidを取得するように変更する必要があるようにうかがえます。

$sql = "SELECT id FROM user_data WHERE email =Ａさんのアドレス ";
$stmt = $pdo->query($sql);
foreach($stmt as $row){
$user_id = $row['id'];
}

$programs1 = $_SESSION['join2']['programs1_'];
$year1 = $_SESSION['join2']['programs_year1_'];
for($i=0;$i<count($programs1);$i++) {
$stmt = $pdo->prepare("INSERT INTO user_skill(・・・) VALUES (・・・)");
$stmt->execute([
$user_id,
$programs1[$i],
$year1[$i]
]);
}

↑ohashiさんの回答内容からは多分、こういう風に変える必要があるように伺えます。
まだ勉強中なのでforeachの部分がかみ合わないと思いますが...

email =Ａさんのアドレスの、
Ａさんのアドレス、の部分を
をどうやってそれぞれに対応できるようにしたらいいかが分からないですが...

lastInsertId()を使うと競合しないという内容で質問しましたが、ohashiさんの回答を見ると、lastInsertId()を使うと競合してしまうと読み取れますが、他の方の回答などを見ていると競合しないと受け取れるのですが、その点どうなのでしょうか？

[masaya_ohashi]

・メールアドレスについて
まさにそのような処理の流れに変えたほうがよいです。ログインしているユーザの情報、例えばログインに使ったユーザを一意に示す何かをセッションに残していませんか？表示名とかはセッションにすでに残しているので、それと同じ方法でセッションにメールアドレスなりなんなりを保持しておけば使えるはずです。

・競合について
http://php.net/manual/ja/pdo.lastinsertid.php
ここにlastInsertIdについて書かれています。
「もし name パラメータにシーケンス名が指定されなかった場合、 PDO::lastInsertId() はデータベースに挿入された最後の行の行IDに相当する文字列を返します。」
そして、これはMySQLでいうとテーブルのAUTO_INCREMENTの現在の値になります。
shi_ueさんのいうように接続内での値であるか、ttyp03さんの言うようにトランザクション処理がある場合、ない場合どうなるかは検証したことがないのではっきり申し上げることは出来ません。しかし、lastInsertIdに「そのへんの動作についてどう動く仕様である」という明言が無いため、何かしら意図しない動作になる可能性が生まれます。
しかし、ユーザをユニークに識別可能な情報によって得たuser_idは、意図しない動作になる可能性は(コード的なミスでもないかぎり)ありません。ですので、確実性を求めるのであればこのやり方のほうがよい、という話になります。

[earnest_gay]

なるほどです！
ありがとうございます！

ということはこうですね！
$email = $_SESSION['join1']['email'];

$sql = "SELECT id FROM user_data WHERE email = $email ";
$stmt = $pdo->query($sql);
foreach($stmt as $row){
$user_id = $row['id'];
}

これでできました。
ありがとうございます。
$email = $_SESSION['join1']['email'];
$sql = "SELECT id FROM user_data WHERE email = '$email' ";
$stmt = $pdo->query($sql);
$row = $stmt->fetch();
$user_id = $row['id'];

Answer 2

lastInsertId()は大概のDBの場合、その接続中での最後のIdを返します。
すべての接続ではありません。

すべての接続の場合MAX(id)などで最大のIDをとる必要があります。

Comments

[earnest_gay]

ありがとうございます。

Answer 3

たとえば

create table hoge(id int not null auto_increment primary key,val1 varchar(10));
insert into hoge(val1) values('a'),('b'),('c');
create table fuga(id int,val2 varchar(10));

として、

insert into hoge(val1) values('d');
select sleep(5);
insert into fuga(id,val2) values(last_insert_id(),'x');

を実行後、並行して5秒以内に

insert into hoge(val1) values('e');
insert into fuga(id,val2) values(last_insert_id(),'y');

を実行すれば
dを挿入したhogeのid=4を引き継いでfugaのid=4の値はx、
同様にeを挿入したhogeのid=5を引き継いでfugaのid=5の値はyとなり
同じセッション内での最終挿入idを引き継いでいることがわかると思います

Answer 4

たぶんですが、トランザクション内で最後に追加したIDを返す仕組みだからではないでしょうか。
ここの記事なんかを読むと、トランザクション外では正しく取得できないようですので。
現状のソースだとトランザクションを意識した作りにはなってないと思います。
（自分でbeginTransactionなどをしていない）
意図的にトランザクションをしない場合、プログラムの実行から終了までがトランザクションの範囲になります。

Comments

[earnest_gay]

ありがとうございます。
リンク先拝見させて頂きます。