- 프로시저 내에서 트랜잭션을 사용하고, 실패시 롤백하는 구성
CREATE PROCEDURE `test`.`sp_diary_cnt`()
BEGIN
DECLARE V_SchemaList TEXT;
DECLARE V_Schema TEXT;
DECLARE V_TableName TEXT;
DECLARE V_SQL TEXT;
DECLARE err INT default '0';
DECLARE continue handler for SQLEXCEPTION set err = -1;
SET SESSION group_concat_max_len = 10000;
SELECT GROUP_CONCAT(SCHEMA_NAME) INTO V_SchemaList FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME NOT IN('information_schema', 'mysql', 'performance_schema', 'sys','test');
SELECT CONCAT(V_SchemaList, ',') INTO V_SchemaList;
DROP TABLE IF EXISTS tmp_diary_count;
CREATE TABLE tmp_diary_count (ins_name char(20) null, schema_name char(8) null, count INT UNSIGNED NULL);
SELECT 'tbl_post' INTO V_TableName;
START TRANSACTION;
WHILE (LOCATE(',', V_SchemaList) > 0)
DO
SET V_Schema = LEFT(V_SchemaList, LOCATE(',',V_SchemaList) - 1);
SET V_SchemaList = SUBSTRING(V_SchemaList, LOCATE(',', V_SchemaList) + 1);
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = V_TableName AND TABLE_SCHEMA = V_Schema ) THEN
SET @V_SQL = CONCAT('INSERT INTO tmp_diary_count (count, ins_name, schema_name) SELECT count(id), \'HOME01\',\'',V_Schema, '\' FROM ', V_Schema, '.', V_TableName,' WHERE service_type in (\'M\',\'R\',\'O\')');
PREPARE STMT2 FROM @V_SQL;
EXECUTE STMT2;
DEALLOCATE PREPARE STMT2;
END IF;
END WHILE;
if err < 0 then
rollback;
else
commit;
end if;
END
- 반복되는 Insert를 Transaction으로 처리하여, 읽기와 쓰기가 동시에 되는 것을 분리
DECLARE err INT default '0';
DECLARE continue handler for SQLEXCEPTION set err = -1;
START TRANSACTION;
if err < 0 then
rollback;
else
commit;
end if;