mysqli に関するトランザクションをお教えください。

Question

お世話になります。お恥ずかしながらこれまでトランザクションというものを知らず、一度の処理でテーブルをUPDETEなりDELETEなりを行ったときの整合性に関して調べていた時、このトランザクションというものを知りました。

このトランザクションですが、実際に動きを確認したいと考え、以下のようにinsertなりupdateなり、3つのテーブルの処理を行い、失敗したときの動きを確認したいと考えていますが、tryの中で失敗するものがあっても個別にsqlが実行してしま状況です。

恐れ入りますが、アドバイスいただくことは可能でしょうか？
よろしくお願いいたします。

//各テーブルの型はInnoDB型です。

$mysqli->begin_transaction();

try {


	$query = "INSERT INTO a_transaction_slip(slip_no, slip_date, slip_member_no, slip_price) VALUES (0, '$date', '$member_no', '$slip_price')";
	$res = $mysqli->query($query);
	$increment_no = mysqli_insert_id($mysqli);

	//sales_noカラムはauto_increment。あえて1をインサートして動きを見ている。
	$query = "INSERT INTO a_transaction_sales(sales_no, sales_slip_no, sales_item_no) VALUES (1, '$increment_no', '$item_no')";
	$res = $mysqli->query($query);

	//item_noカラムのデータ型はint型。あえて日付が入っている$date変数を条件にして動きを見ている。
	$query = "UPDATE a_transaction_item SET item_stock = item_stock - 1 WHERE item_no = '$date'";
	$res = $mysqli->query($query);


} catch( Exception $e ){
	// エラーが起きたらロールバック
	$mysqli->rollback();
}


// 正常に終了したらコミット
$mysqli->commit();

Comments

[m.ts10806]

どこでどう失敗する見込みなのでしょうか？
現状だと必ずコミットされるようになっています（ロールバック後だと別のエラーになるような気がしますが）
catchには入ってきているか確認しましたか？
個人的にはトランザクションよりもSQLに変数がそのまま入ってしまっているSQLインジェクションの脆弱性の方が気になりますが。

[chapp]

m.ts10806さん

書き込みありがとうございます。
はじめてのトランザクション処理なので、どのような動きをするのか？どのような形で失敗するのか？確認レベル・勉強レベルという状況なのですが、m.ts10806さんから書き込みに「catch」が書かれていたため、エラーの際メッセージを出力するようにし、あえて2つ目のSQLを失敗させるため、SQLを

INSERT INTO a_transaction_sales(sales_no, sales_slip_no, sales_item_no) VALUES (0, 0, '$increment_no', '$item_no')

としてみましたが、その他のSQLは正常に処理され、エラーメッセージも表示されませんでした。

try内の最後にcommit文を置いてみましたが、他おかしなところはありますでしょうか？アドバイス頂けると幸いです。

[m.ts10806]

せめてcatchで入ってきてる$eをvar_dumpするなりgetErrorMessageでエラー確認するなりはされた方が良いと思います。
つまり、エラーメッセージを出す実装になっていない。
「どこまで来ているか」の確認なら途中echo __LINE__;を随所に入れて確認すると良いです。

[m.ts10806]

定義次第では失敗しないSQLに思います。「どういう失敗」を想定していますか？

[chapp]

m.ts10806さん

早々のお返事をありがとうございます。

>せめてcatchで入ってきてる$eをvar_dumpするなりgetErrorMessageでエラー確認

勉強になります。ありがとうございます。
catchの直後に、
var_dump($e);
echo getErrorMessage($e);
を置いてみましたが、間違っていますでしょうか？
原状、特に何も表示はされませんでした。

>「どういう失敗」を想定していますか？

質問の回答になっていないかもしれませんが、今回のトランザクションを調べて実装しようと思った経緯は、1度の処理において複数のSQLを走らせたとき、いずれかが失敗したら、そのどれも実行しないことをイメージし、以下のサイトを参考に、テスト用テーブルを設けて動作確認をしています。

https://gray-code.com/php/transaction-by-using-mysqli/

しかし、実行後テーブルの中を見てみると、失敗したSQLがあっても、正常にインサートやUPDATEされているテーブルがあるので、どこが間違えているのか？ご教示いただきたく投稿した次第です。

[m.ts10806]

あの、可能でしたら質問本文に記載してもらえたらと。
こちらはあくまで質問への追記修正依頼欄ですし、コメントで散発されてしまうと全体の流れや問題の本質が見えにくくなります。

Answer 1

tryの中で失敗するものがあっても個別にsqlが実行してしま状況です。

失敗したかどうかは自分でチェックして、最終的にrollbackあるいはcommitを行う必要があります。

SQL文自体が文法的に成立していなくて実行できないなら明らかに失敗ですが、そうでなければ「失敗したかどうか」はプログラマの意図によります。自動で判定はできません。

Comments

[chapp]

maisumakunさん

ご親切なアドバイスをありがとうございます。
読解力がなく申し訳ありません。自分でチェックしてrollback（またはcommot）とのことですが、m.ts10806さんからの質問に回答した、私のトランザクションの認識が誤っているということでしょうか？
トランザクションの動きをみるためtry内に複数のSQLを置き、テーブルのカラム数より多いデータを登録してみたり、プライマリー指定されたカラムにあえて重複した値の登録を試したりし、「失敗」した時をイメージして動作を見ていますが、このような失敗は判定できないのでしょうか？

[maisumakun]

> あえて日付が入っている$date変数を条件にして動きを見ている。

このあたりは単に「データが一致せず、空のデータを取れて成功」となります。データベース側からは失敗とみなされません。

[maisumakun]

> トランザクションの動きをみるためtry内に複数のSQLを置き、テーブルのカラム数より多いデータを登録してみたり、

このような動作を必要とする場面が想定できません。

[chapp]

maisumakunさん

早々のお返事をありがとうございます。

>単に「データが一致せず、空のデータを取れて成功」となります。データベース側からは失敗とみなされません。

そうなのですね。腑に落ちました。ありがとうございます！

>このような動作を必要とする場面が想定できません。

失敗したときの処理を確認したいための処置でしたが、今回の様にトランザクションの動きを確認（テーブルでデータが更新されているか確認している）するためには、どのようなSQLを置いたらよろしいでしょうか？
はじめて触るトランザクションなので、目で見ながら確認出来ればと進めておりました。ご教示頂ければ幸いです。

[maisumakun]

「接続が途切れた」というようなサーバレベルの障害やデッドロックなどを除けば、トランザクションのロールバックはは「戻る」ものではなく、データの不整合をSQL外のプログラムで判断して、コードにより「戻す」ものです。

[chapp]

maisumakunさん

ありがとうございます。なるほどですね。
色々と確認したいことありますが、頭の中で一旦整理することが出来たかと思います。
ありがとうございました。ご親切なアドバイスに感謝いたします。

Answer 2

コミットはtry文の中で行うのが正しいのかなと思います。
https://www.php.net/manual/ja/mysqli.begin-transaction.php

//各テーブルの型はInnoDB型です。

$mysqli->begin_transaction();

try {


    $query = "INSERT INTO a_transaction_slip(slip_no, slip_date, slip_member_no, slip_price) VALUES (0, '$date', '$member_no', '$slip_price')";
    $res = $mysqli->query($query);
    $increment_no = mysqli_insert_id($mysqli);

    //sales_noカラムはauto_increment。あえて1をインサートして動きを見ている。
    $query = "INSERT INTO a_transaction_sales(sales_no, sales_slip_no, sales_item_no) VALUES (1, '$increment_no', '$item_no')";
    $res = $mysqli->query($query);

    //item_noカラムのデータ型はint型。あえて日付が入っている$date変数を条件にして動きを見ている。
    $query = "UPDATE a_transaction_item SET item_stock = item_stock - 1 WHERE item_no = '$date'";
    $res = $mysqli->query($query);
　　　　　　　　// 正常に終了したらコミット
　　　　　　　　$mysqli->commit();


} catch( Exception $e ){
    // エラーが起きたらロールバック
    $mysqli->rollback();
}

Comments

[chapp]

holy_さん

アドバイスありがとうございます。

> コミットはtry文の中で行うのが正しいのかなと思います。

あ、確かに言われてみればその通りですね。
確認させていただきます。
ありがとうございます！

[chapp]

holy_さん

アドバイスいただいたように、tryの中でコミットしましたが状況は変わらずで、複数あるうちのSQLいずれかが失敗しても、問題ないものはそのまま実行されてしまう状況です。

今回アドバイスただいた「tryの中にコミット」以外におかしなところはありますでしょうか？
アドバイス頂ければ幸いです。

[__horito]

try文の前に以下の処理をいれてみるのはどうでしょう？
$mysqli->query('SET autocommit=0');

[__horito]

参考：https://dev.mysql.com/doc/refman/8.0/en/commit.html

[m.ts10806]

確かにMySQLならデフォルトオートコミットONなので自動で処理されてしまう可能性は高いですね。

[chapp]

holy_さん

ご親切にありがとうございます。
try前に置いてみましたが、状況は何も変わらずです。

m.ts10806さんからの質問の回答に書きましたが、トランザクションの動作は、私の想定していたものと違うのでしょうか。色々とお手数おかけし申し訳ございません。

[__horito]

トランザクションの動作の認識は合っているように思います。
ですがそもそもの例外が起てるかわからないので
コミットの前で以下の処理を追加して例外をなげてみて下さい
throw new Exception("Exception");
これでDBが更新されているかみてほしいです。

[chapp]

holy_さん

ありがとうございます。
$mysqli->commit();
を、tryの最後に移動し、その直前に
throw new Exception("Exception");
を追加してみました。

すべてのテーブルが更新しなくなりましたが、全てSQLが正常な場合でも更新いたしません。

ご親切な対応に感謝しています。ありがとうございます。
引き続きご指導のほどいただければ幸いです。

[m.ts10806]

>すべてのテーブルが更新しなくなりましたが、全てSQLが正常な場合でも更新いたしません。

SQL正しいままthrowもそのままだとしたら、例外キャッチしてロールバックしてるからです。正常な動作。

[chapp]

m.ts10806さん

ありがとうございます。了解いたしました。

[chapp]

holy_さん

色々とありがとうございました。確認したいことありますが頭の中が整理出来たので、ここは解決済みとさせて頂きます。ありがとうございました。