MYSQL事务封装的问题

Question

现在有一个作业，分为三个SP来执行，分别为SP_1,SP_2,SP_3,为了能在发生错误时回滚整个作业，现在在一个SP里面调用这个三个SP：CREATE PROCEDURE SP
BEGIN
    START TRANSACTION;    CALL SP_1;
    CALL SP_2;
    CALL SP_3;    COMMITE;
END但是这样似乎不能实现上述的需求，在SP_2调用出错时，并没有回滚。

Answer 1

补充一下，SP里面有大量的PREPARE语句，难道是因为这个原因？

Answer 2

CREATE PROCEDURE SP
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
    START TRANSACTION;
    CALL SP_1;
    CALL SP_2;
    CALL SP_3;
    COMMITE;
END

Answer 3

这是我的SP，没有回滚：DROP PROCEDURE IF EXISTS SP_ImportTestSuitesTestCases;CREATE PROCEDURE SP_ImportTestSuitesTestCases(
 TestProjectID     INT
,UserID INT
,TemporaryTableName VARCHAR(100)
  )
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK; START TRANSACTION;
/*alter temporary table*/
CALL SP_PrepareTemporaryTable (TemporaryTableName); /*import testsuites*/
CALL SP_ImportTestSuites (TestProjectID, TemporaryTableName); /*import testcases*/
CALL SP_ImportTestCases (TestProjectID, UserID, TemporaryTableName); COMMIT;
END

Answer 4

另外三个，都是类似这样的：DROP PROCEDURE IF EXISTS SP_ImportTestCases;CREATE PROCEDURE SP_ImportTestCases(
 TestProjectID     INT
,UserID INT
,TemporaryTableName VARCHAR(100)
 )
BEGIN

#insert data into nodes_hierarchy
#insert testcase
SET @InsertData = CONCAT('INSERT INTO `nodes_hierarchy` (`name`,`parent_id`,`node_type_id`,`node_order`) 
SELECT  A.`Name`,B.`NodeID`,3,A.`OrderNO` 
FROM `', TemporaryTableName, '` A INNER JOIN `', TemporaryTableName, '` B
ON A.`ParentName` = B.`Name`
 AND A.`Level` = B.`Level` + 1
 AND A.`Type` = ''C''
 AND B.`Type` = ''S''');
PREPARE InsertData FROM @InsertData;

EXECUTE InsertData;

DEALLOCATE PREPARE InsertData; #update `NodeID` from temp table
SET @UpdateNodeID = CONCAT('UPDATE `', TemporaryTableName, '`  A INNER JOIN `', TemporaryTableName, '` B
ON A.`ParentName` = B.`Name`
 AND A.`Level` = B.`Level` + 1
 AND A.`Type` = ''C''
 AND B.`Type` = ''S''
 INNER JOIN `nodes_hierarchy` C
    ON A.`Name` = C.`name`
 AND B.`NodeID` = C.`parent_id`
  SET A.`NodeID` = C.`id`'); PREPARE UpdateNodeID FROM @UpdateNodeID;

EXECUTE UpdateNodeID;

DEALLOCATE PREPARE UpdateNodeID;

#insert data into nodes_hierarchy
#insert testversion
SET @InsertData = CONCAT('INSERT INTO `nodes_hierarchy` (`name`,`parent_id`,`node_type_id`,`node_order`) 
SELECT '''',`NodeID`,4,0 FROM ', TemporaryTableName , ' WHERE `Type` = ''C'''); PREPARE InsertData FROM @InsertData;

EXECUTE InsertData;

DEALLOCATE PREPARE InsertData; #insert data into tcversions
SET @InsertData = CONCAT('INSERT INTO `tcversions` SELECT B.`id`,'
 ,TestProjectID
   ,',1,1,1,A.`Summary`,NULL,2,'
 ,UserID
 ,',NOW(),NULL,''0000-00-00 00:00:00'',1,1,1 FROM '
 ,TemporaryTableName, ' A JOIN `nodes_hierarchy` B ON A.`NodeID` = B.`parent_id`'
 ,' WHERE A.`Type` = ''C'''); PREPARE InsertData FROM @InsertData;

EXECUTE InsertData;

DEALLOCATE PREPARE InsertData; #insert data into nodes_hierarchy
#insertsteps
SET @InsertData = CONCAT('INSERT INTO `nodes_hierarchy` (`name`,`parent_id`,`node_type_id`,`node_order`) 
SELECT '''',B.`id`,9,0 
FROM ', TemporaryTableName ,' A JOIN `nodes_hierarchy` B
ON A.`Type` = ''C''
AND B.`node_type_id` = 4
AND A.`NodeID` = B.`parent_id`'); PREPARE InsertData FROM @InsertData;

EXECUTE InsertData;

DEALLOCATE PREPARE InsertData; #insert data into tcsteps
SET @InsertData = CONCAT('INSERT INTO `tcsteps` 
SELECT B.`id`,1,A.`Steps`,A.`Results`,1,1 
FROM (
SELECT A.`Type`,A.`Steps`,A.`Results`,B.`id` AS `NodeID` 
FROM ', TemporaryTableName, ' A JOIN `nodes_hierarchy` B
ON A.`NodeID` = B.`parent_id`
AND A.`Type` = ''C''
AND B.`node_type_id` = 4
) A JOIN `nodes_hierarchy` B
ON A.`NodeID` = B.`parent_id`
AND B.`node_type_id` = 9
AND A.`Type` = ''C'''); PREPARE InsertData FROM @InsertData;

EXECUTE InsertData;

DEALLOCATE PREPARE InsertData; #insert custom_fields
SET @GetCfieldsP = CONCAT('SELECT GROUP_CONCAT(''SELECT `NodeID`,`name`,'''''',A.`name`,'''''' AS CfieldName,`'',A.`name`,''` AS CfieldValue FROM '
, TemporaryTableName ,' WHERE TYPE=''''C'''''' SEPARATOR '' UNION ALL '') INTO @GetCfields
FROM `custom_fields` A INNER JOIN `cfield_testprojects` B ON A.`id` = B.`field_id` 
AND B.`testproject_id` = ', TestProjectID ,'
 INNER JOIN `cfield_node_types` C ON A.`id` = C.`field_id`
    AND C.`node_type_id` = 3');
PREPARE GetCfieldsP FROM @GetCfieldsP; EXECUTE GetCfieldsP; DEALLOCATE PREPARE GetCfieldsP; SET @InsertData = CONCAT('INSERT INTO `cfield_design_values`
SELECT B.`id`,E.`id`,A.`CfieldValue`
FROM (', @GetCfields ,') A INNER JOIN `custom_fields` B ON A.`CfieldName` = B.`name`
AND IFNULL(A.`CfieldValue`, '''') <> ''''
 INNER JOIN `cfield_testprojects` C ON B.`id` = C.`field_id`
AND C.`testproject_id` = ' ,TestProjectID, '
 INNER JOIN `cfield_node_types` D ON B.`id` = D.`field_id`
AND D.`node_type_id` = 3
 INNER JOIN `nodes_hierarchy` E ON A.`NodeID` = E.`parent_id`
AND E.`node_type_id` = 4'); PREPARE InsertData FROM @InsertData;

EXECUTE InsertData;

DEALLOCATE PREPARE InsertData;

END