MySQL トランザクションの処理について

Question

トランザクションの処理について、色々ネットサーフィンし色々な記事を見ましたが、
自分なりに理解したつもりでしたが、実際に組み込んで試してみた所、
動作に不可解な部分があったので質問させていただきました。

データの整合性を保つため、トランザクションの処理が必要と、サイトを確認しました。
（トランザクションについて調べ、銀行の残高を例にしたサイトでした）
振込の際、銀行Aの減額処理と銀行Bの増額処理が正常に行われてないと、データの整合性が保てない等の内容でした。

現在、こちらのサイトのトランザクション処理を参考に記述しておりますが、
下記動作が正常なのか、確認お願いします。

id は PRIMARY

• クエリ1. (初期データ)

INSERT INTO testtable (id, name) VALUES (1, 'yamada');

• クエリ2. (処理時間稼ぐ為のデータ)

INSERT INTO testtable (id, name) SELECT COALESCE(MAX(id)+1,1), 'kato' FROM testtable;
INSERT INTO testtable (id, name) SELECT COALESCE(MAX(id)+1,1), 'sato' FROM testtable;
......（2,000件程度）

• クエリ3.

INSERT INTO testtable (id, name) SELECT COALESCE(MAX(id)+1,1), 'suzuki' FROM testtable;

当方の認識としては、
『クエリ1のデータ(id:1)が予め入力されており、クエリ2の大量データをトランザクションで処理。
クエリ2の処理に時間かかる場合、クエリ3を実行すると、クエリ2の処理が終わってから実行される。』と、思っておりましたが
実際は、クエリ2の実行・処理中にクエリ3を実行したところ、ID:2として処理が完了し、クエリ2はID:3から連番が振られていました。

上記のトランザクション処理は正しいのか。また、トランザクション処理でおすすめや参考になるサイト等あればお教えいただければ幸いです。
初歩的な質問で申し訳御座いませんが、お願い致します。

動作環境(Xserver)
MariaDB 10.5
PHP 7.4.25
追記：
'SELECT @@GLOBAL.tx_isolation, @@tx_isolation'
REPEATABLE-READ, REPEATABLE-READ

クエリ1，2，3は別セッションとなります（3セッション）

Comments

[nukasa]

トランザクションのことをうんぬんするなら、トランザクション分離(アイソレーション)のことを考慮しなければなりません。
クエリ1～3が、同一DBセッションで実行されたのか、別のDBセッションで実行されたのか、分離レベルは何なのかを明確にしなければ、議論をスタートできません。

[退会済みユーザー]

コメントをいただき、ありがとうございます。
恐れ入ります、「クエリ1～3が、同一DBセッションで実行されたのか、別のDBセッション」の部分ですが、
DBセッションとは
（https://lukesilvia.hatenablog.com/entry/20090523/p1）
の内容の事で合ってますでしょうか。
知識が乏しく勉強中ですため、気分を害す返答・質問でしたら申し訳御座いませんが、
ご教授いただければ幸いです。

[退会済みユーザー]

MAX(cmid)+1で発番するのもナンセンス。AUTO_INCREMENTを使うとか。シーケンスを使うとか。 https://qiita.com/Nyokki/items/c68dddff2368c557c4bf

[nukasa]

https://lukesilvia.hatenablog.com/entry/20090523/p1
に書かれているのは、アプリケーションサーバーのセッション情報をDBに永続化するための方法を書いてあるもので、DBセションとは関係ありません。

DBセッションというのは、DBに接続してから切断するまでの一連の作業を紐づけるものです。mysqlコマンドで、クエリ1～3を連続して発行しているなら、同一セッションです。クエリ2を処理している最中に、別のウィンドウからmysqlコマンドを別に起動し、そこでクエリ3を実行するのであれば、セッションは別になります。

[退会済みユーザー]

ご返答ありがとうございます。

php-gres さま
AUTO_INCREMENT にて設定しますと、idのカラムは他テーブルとのデータにJOINしており、削除等が行われると手間が掛かるかと思いました事、1万件を挿入した後、5千件のデータを削除した際、AUTO_INCREMENT を戻さないと歯抜けになることが懸念され、長さでも直ぐに天井となる事、その際のメンテナンス軽減のためです。
MAX()+1の連番はそこまで良くない方法となりますでしょうか、、。
不具合が発生する例もしくは、処理速度の面でもシーケンスの方がよろしいでしょうか。。
ご意見をお教えいただけませんでしょうか。
シーケンスについては、新しい情報でしたため、URLをお教えいただきありがとうございます。
参考にします。

nukasa さま
DBセッションについて、噛み砕いていただきありがとうございます。
セッションについては、クエリ1、2、3で別セッション（計3セッション）になります。
また、分離レベルを確認しましたところ、
'SELECT @@GLOBAL.tx_isolation, @@tx_isolation'
REPEATABLE-READ, REPEATABLE-READ
となっております。が、情報は足りてますでしょうか。

Answer 1

処理を待たせるならsleepが使えると思います

//基本データ

create table tbl(id int primary key auto_increment,name varchar(10));
insert into tbl(name) values('yamada');

//以下実行中に、その下のSQLを実行

start transaction;
select * from tbl for update;
select sleep(5);
insert into tbl(name) values('kato'),('sato');
commit;

start transaction;
insert into tbl(name) values('suzuki');
commit;

必ず並行して動作させてください

Comments

[退会済みユーザー]

ご回答いただき、ありがとうございます。
コードまでご丁寧に記載いただき、一度当方にてコードを修正した後、上記コードも試してみようと思います。
今回は他の方の回答にベストアンサーを付けさせていただきましたが、身近にプログラムの詳しい人がおりませんため、別質問させていただく時には、コメントをいただければ幸いです。
よろしくお願いします。

Answer 2

クエリ1～3がすべて別セッションということですから、トランザクションは別々に制御されます。トランザクション分離レベルが REPEATABLE READ とのことですから、INSERTした行は、commitするまでは、他のセッションからは見えません。
MAX(cmid) も他のセッションで登録したcmidは、commit するまで見えないので、自セッションがINSERT文を実行した時点で、見えているsumidの最大値ということになります。
IDをAUTO INCREMENT ではなく、自力で採番するというのは、重複したIDを振ってしまう、ダメなコーディングの代表(アンチパターン)ですね。

Comments

[退会済みユーザー]

ご丁寧に回答いただきありがとうございます。
トランザクションについて、理解が深まりました。
また、自力採番のダメなコーディングも指摘いただき、ありがとうございます。
AUTO INCREMENTでは意図しない場面でカウンターが進み、悩み自力採番を採用しましたが、改めて調べました所、カウンターが進む対策も出来そうです。（私の調べが足りませんでした）
少しコードを書き直し、試してみようと思います。
また、こちらの回答をベストアンサーとさせていただきます。