PHPとMySQLのトランザクション処理、整合性について

###前提・実現したいこと
トランザクション処理が負荷の面から以下の処理で最適かどうかが知りたいです。

レンタル掲示板のようなサービスだと考えて下さい。
ユーザーの掲示板への投稿と、掲示板管理人の掲示板削除処理が同時に行われた場合（可能性は少ないですが）、掲示板削除が先に行われたら、存在しないはずの掲示板IDで記事がデータベースに保存されてしまうかと思います。
外部キーをつけろ等は今回は一切なしでお願いします。

上部の「$bbs_data = fetchBbs($bbs_id)」で一度掲示板が存在するか確認＆設定値を取得する処理を入れていますが、整合性のために記事投稿前に再度掲示板が存在するかのチェックをSELECT FOR UPDATEで入れないとダメでしょうか。
2回掲示板の存在を確認するとすごく無駄なように感じてしまいます。
相当なアクセス数がある場合は、1クエリでも減らしたいものです。

他に良いチェック方法等がありましたら、教えて頂けますと幸いです。

よろしくお願いします。

※サンプルソースコードはかなり簡略化しセキュリティ等は一切考慮していません。あくまでもイメージです

###該当のソースコード

PHP
1<?php
2// 掲示板ID取得
3$bbs_id = isset($_GET['bbs_id']) ? $_GET['bbs_id'] : '';
4
5// 掲示板が存在するか確認
6if ( !($bbs_data = fetchBbs($bbs_id)) ) {
7    404エラーページ表示();
8}
9
10/**
11 * $bbs_dataには掲示板名や設定値が入っているとします
12 */
13
14// 何かしらの処理
15// 何かしらの処理
16// 何かしらの処理
17// 何かしらの処理
18
19
20// 掲示板への投稿ボタンが押されたとします
21if ($_SERVER['REQUEST_METHOD'] === 'POST') {
22    try {
23    	
24    	トランザクション開始();
25    	
26    	
27    	/**
28    	 * !ここで再度、掲示板が存在するかをSELECT FOR UPDATEでデータベースに問合せないといけないかどうか？
29    	 */
30    	
31    	
32    	// 掲示板へ投稿
33    	post($bbs_id, $name, $message);
34    	
35    	コミット();
36    	
37    } catch (PDOException $e) {
38    	
39    	ロールバック();
40    	
41    }
42}

行動規範の内容に同意します

回答3件

id を primary_key インデックスをつけて、autoincrement 属性をつけておけば、そんな心配は無用です。

投稿2016/09/07 16:16

退会済みユーザー

総合スコア0

manto

2016/09/07 16:21

テーブルが、 tbl_bbs - 掲示板テーブル tbl_log - 記事テーブルとした場合、掲示板を削除するときはtbl_bbsとtbl_logの両方から$user_idに格納されている掲示板IDのものを削除しますよね？でも、質問の通り同時に行われた場合、tbl_bbsには$user_idのデータはないが、tbl_logには$user_idのデータが書き込まれて整合性が保てないことにはなりませんか？

退会済みユーザー

2016/09/07 16:23

接合先のテーブルにそのIDが残っても中に浮くだけで問題は起きません。

manto

2016/09/07 16:31

例えば、$user_id = 100 だとします。 ID：100のデータはtbl_bbsから削除され、tbl_logにはID:100のデータが残った状態でも特に問題ないということでしょうか？その後、別のユーザーがID：100の掲示板を作成したら前の人の記事データが表示されてしまうと思うのですが。。。掲示板作成時に一旦ID：100のデータをDELETEしてから、掲示板作成のINSERTをしたら済むはなしかもしれませんが、削除したはずのデータが残っているというのは気持ちのいいものではない気がします。となるとやはり、投稿直前にSELECT FOR UPDATEで掲示板の存在確認をもう一度するしかないのでしょうか？

manto

2016/09/07 16:32

ちなみに掲示板ID（$user_idに格納されるもの）はオートインクリメントの数字ではなく、ユーザーが任意に決められる仕様です。

退会済みユーザー

2016/09/07 16:51 編集

> 他に良いチェック方法等がありましたら、教えて頂けますと幸いです。これへの回答が、autoincrement を使うという回答です。自分は autoincrement を使って設計します。せっかく用意された便利機能を捨てて、そのような設計にしたのであれば、そこはデメリットも受け入れるしかないでしょう。

manto

2016/09/08 00:40 編集

> 接合先のテーブルにそのIDが残っても中に浮くだけで問題は起きません。 1点気になったのですが、親データが削除され、子データが残ってしまう（整合性がとれていない）状態は、普通なのですか？データは理論削除ではなく、物理削除で行う仕様です。

退会済みユーザー

2016/09/08 02:02

自分は論理削除を使いますから。

行動規範の内容に同意します

INSERT ... SELECT 構文を使用してはいかがでしょうか？
https://dev.mysql.com/doc/refman/5.6/ja/insert-select.html

これなら、user_id の存在チェックと記事テーブルへの INSERT 処理を1クエリで実施できます。

PDO を使用する場合、例えば以下のようなコードになります。

php
1$pdo = new PDO($dsn, $user, $password);
2$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
3
4$stmt = $pdo->prepare('INSERT INTO tbl_log (user_id, ...) SELECT user_id, ... FROM tbl_bbs WHERE user_id = ?');
5$stmt->bindValue(1, $user_id, PDO::PARAM_INT);
6
7$stmt->execute();
8
9/**
10 * INSERT された件数を確認。
11 * 0 の場合、掲示板テーブルの user_id が削除されている。
12 */
13$rowCount = $stmt->rowCount();
14if ($rowCount === 1) {
15    echo '投稿しました。';
16} elseif ($rowCount === 0) {
17    echo '掲示板が削除されています。';
18} else {
19    throw new LogicException('何かおかしい');
20}
21

PDO 以外のライブラリで「INSERT された件数を確認」する方法は、
mysqli なら mysqli_stmt::$affected_rows (※1)、
wpdb なら $wpdb->query() メソッドの戻り値 (※2) のはずです（未検証）。

※1 http://php.net/manual/ja/mysqli-stmt.affected-rows.php

直近に実行されたステートメントで変更・削除・あるいは追加された行の総数を返す

※2 https://wpdocs.osdn.jp/%E9%96%A2%E6%95%B0%E3%83%AA%E3%83%95%E3%82%A1%E3%83%AC%E3%83%B3%E3%82%B9/wpdb_Class#.E4.B8.80.E8.88.AC.E3.82.AF.E3.82.A8.E3.83.AA.E3.81.AE.E5.AE.9F.E8.A1.8C

This function returns an integer value indicating the number of rows affected/selected for SELECT, INSERT, DELETE, UPDATE, etc.

ちなみに、あえて言わせていただくと、整合性チェックの最も相応しい手段は外部キー制約を使用することです(w)。
そうすれば、このような小手先のテクニックなど使う必要すらないからです。

投稿2016/09/08 04:26

KiyoshiMotoki

総合スコア4791

基本的にShibuyaさんの意見に賛成派です。（削除は論理、autoincrementの利用で解決）
ですが、諸事情でできないのでしょうね。

そのまま行くとすると、自分だったら気にしている2個目の存在確認も入れます。
（無いと同じ掲示板IDで作成されたときにゴミだった子データが現れますからね。
ってわかってますよね？（笑）
理由
・最初だけ → データの不整合の恐れ
・２個目だけ → 画面表示時に問題あり
・最初でトランザクション開始 → ロックが長くなるので

投稿2016/09/08 03:04

koutan1976

総合スコア142

manto

2016/09/08 03:45 編集

ご回答有難う御座います。やはり整合性を保たせるためには２個目の掲示板存在チェックが必要になるんですね。別件なのですが、削除は理論派とのことですが、理論削除のメリットがあまり見いだせないのですが、なぜ理論削除にするかの理由をお聞かせいただいてもよろしいでしょうか。万が一削除した、や、前のデータがどうしても見たい等の場合は、バックアップから見ればいいと思ってしまいます。削除フラグを入れたデータってほぼ戻すことってないですよね？理論削除にしたら、PRIMARY KEYやユニークキーの扱いが面倒だったり、SELECT時に毎回削除フラグをWHEREに入れないといけないなどデメリットのほうが大きく感じてしまいます。レコード数が増えていくと、パフォーマンスにもかなり影響してくると思います。よろしくお願いします。

koutan1976

2016/09/29 03:17

かなりの時間が空いてしまいました、ごめんなさい。 ■削除フラグを入れたデータを戻すことについてシステムの性質にもよりますが、私が関わったことのあるシステムでは極まれにでも復帰させることがあります。この際に、物理削除したデータと論理削除したデータを復帰させる際のコストが大きく違います。（論理が楽）そもそもなのですが、物理削除させたくない場合が多いです。例えば集計する場合等です。・ユーザーAがいた。・ユーザーAは部門Bに所属していた。（部門所属テーブルに期間ごとに紐付け情報が存在）・売上情報がありユーザーに緋づいている。・日が経過してユーザーが削除された。この場合、過去の部門Bの売上情報等を集計する場合にAが（部門所属テーブルレコードも）物理削除されていると単純には集計できなくなります。もちろん売上テーブルにそれを想定したFKを登録しておくという手もあると思いますがそれをするよりも論理削除できれいに解決させるほうが断然多いです。 ■PRIMARY KEYやユニークキーの扱いが面倒だったりよほどのことがない限りautoincrement を使います。よって面倒ではありません。 ■SELECT時に毎回削除フラグをWHEREに入れないといけないそのとおりですが、集計の労力と比較した場合たいしたコストにはならないと思っています。あとは慣れですね（笑 ■レコード数が増えていくと、パフォーマンスにもかなり影響してくると思います。削除が論理か物理かでパフォーマンスについてはそれほど影響がないものが多いと思います。 ■おまけ論理派ですが、物理削除をまったく採用しないわけではありません。ほんとに要らないデータがバンバン登録されるようであれば物理削除でよいと思いますよ。

行動規範の内容に同意します

あなたの回答