Mysql ストアド中の一時テーブルの利用方法

Question

mysqlのストアドプロシージャ内で、
トランザクション実行中に、一時テーブルのInsertとそのテーブルの読み込みをしたいのですが、
コミットを切らず、さらにトランザクション分離レベルを変えずにできる方法はあるのでしょうか。

Version:5.7.22-log
分離レベル：REPEATABLE-READ

TBL内にいくつかの塊があってそれごとに処理するというものです。
※最下部に具体データと処理概要を記載

具体的なソースがまだなく、実装して動くかすらわからないですが、
やりたいことは以下のようなフローです。
配列が使えればそうでもないんですが、なさそうなのでTableを配列代わりに使おうとしてます。

カーソルAにTBLをいれる。
 トランザクション開始
  カーソルAループ
　　IF １つの塊が終了したかの判定
      カーソルBにtmpを入れる。
　　　 カーソルBループ
　　　 　処理をして本TBLにInsert
       カーソルB終了
    ELSE
       計算処理をして、tmp にInsert
   END IF;
 カーソルA終了
 コミット

データの例示が難しいので、処理概要も含めて具体的に記載します。
TBL_A(元データ) これが1つの塊です。必ず区分は1,4はあって1塊。
日付,時刻,区分,種別
20201205,0900,1,
20201205,1000,2,A
20201205,1100,3,A
20201205,1200,2,A
20201205,1300,3,B
20201205,1500,2,B
20201205,1700,3,B
20201205,1800,4,
日付違いが続く

処理概要
１．区分3→2の時間をそれぞれ求める。 A:1h B:2h
２．区分2→3のすべての合計を求めて、種別数で割る。1h + 1h + 2h = 4h / 2 = 2h
１に対して2を足しこみ、データを投入する。

最終結果(TBL_B)
20201205,A,0300
20201205,B,0400

tmpとしては、
1の処理を以下のように保持し、2の結果をそれぞれ足しこむという処理になります。
A,0100
B,0200

今回の知りたかった場所の実装はできました。
コミットとロールバックテストはできてませんが、こんな感じでサンプルテスト。

create table raw (id int,primary key(id));
insert into raw values(1),(2),(3);
create table tmp (id int,primary key(id));
create table hon (id int,primary key(id));

delimiter //

DROP PROCEDURE IF EXISTS ABC;
truncate table tmp;

CREATE PROCEDURE `ABC`()
Block1: begin
declare v_done int default 0;
declare v_id int;
declare dummy int default 0;
declare v_cur cursor for select * from raw;
declare continue handler for sqlstate '02000' set v_done = 1;

Start transaction;

open v_cur;
 fetch v_cur into v_id;
 while v_done != 1 do
  	if v_id <> 3 then
  		SET dummy = 0;
  		insert into tmp value (v_id) ON DUPLICATE KEY UPDATE id = v_id;
  	else
  		SET dummy = 0;
  		Block2: begin
  			declare v2_done int default 0;
			declare v2_id int;
			declare v2_cur cursor for select * from tmp;
			declare continue handler for sqlstate '02000' set v2_done = 1;
			open v2_cur;
			fetch v2_cur into v2_id;
			while v2_done != 1 do
				insert into hon value (v2_id) ON DUPLICATE KEY UPDATE id = v2_id;
				fetch v2_cur into v2_id;
			end while;
		End Block2;
  	end if; 
	fetch v_cur into v_id;
 end while;

 close v_cur;
COMMIT;
end Block1;
//
delimiter ;

Comments

[yambejp]

塊ごとの処理とトランザクションがどう連携したいのかわかりません
せめてサンプルテーブルと、期待する結果の例示が必要です

Answer 1

累計などであれば、テーブルを介さなくともSQLのみで行えそうですが、取りあえずそれは置いておくとして、質問にあるフローならコミットは１回だけですので問題は無いと思います。

トランザクション分離レベルを気にされているようですが、同一のトランザクション内での話なので、関係ないですよ。

Comments

[FujiCandy]

トランザクション中に一時テーブルにコミット切らずにInsertしたデータを
そのまま読めるのかなという疑問があり、確認させていただきました。
コミット切ってないデータの読み込みはREAD UNCOMMITTEDに変更が必要なのか、
毎回tmpに入れるたびにcommit切らないといけないのかなと感じたので。。

[sazi]

分離レベルの話は、コミットしていない時に他のトランザクションからファントムリードされるとかの類です。
自身のトランザクションの結果はコミットせずとも確認できます。

[FujiCandy]

ありがとうございます！そういう仕組みなんですね。勉強になりました。