mysql 提交 block 中的事务

Question

CREATE DEFINER=`root`@`localhost` PROCEDURE `PrcCopyQuestion_Admin`(in Param1,in Param2 varchar(45))
BEGIN

 DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
      ROLLBACK;

Select 'Fail' as 'Status' ;      

  END; 

   DECLARE EXIT HANDLER FOR sqlwarning
  BEGIN
      ROLLBACK;

 Select 'Fail' as 'Status' ;         

  END; 

Start transaction;

   Insert statement 1; 

   Insert statement 2; 


   SELECT 'Success' AS 'Status'; 

call PrcGetQuestionAndOption_Admin(@variable); 

 Commit;

END

我正在使用Mysql 5.7。当在提交 block 中时，如果第二个(插入语句 2)失败。它将进入回滚部分并为我提供 'Failed' 输出。但是当我获得输出时，它仍然执行 Select 'Success' as Status in commit block。所以我的问题是第二个插入语句何时失败。它应该直接回滚并给我状态为失败。它不应该在提交 block 中将状态执行为“成功”。

例如:回滚时我得到两个结果集:

Select 'Fail'..1st result set

Select 'Success'....2nd result set

我只需要输出

Select 'fail'

感谢任何帮助!!

Answer 1

我无法重现该问题。

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.7.12    |
+-----------+
1 row in set (0.00 sec)

mysql> DROP TABLE IF EXISTS  `t1`, `t2`;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `t1` (
    ->   `c0` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `t2` (
    ->   `c0` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER //

mysql> DROP PROCEDURE IF EXISTS `PrcCopyQuestion_Admin`//
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE PROCEDURE `PrcCopyQuestion_Admin`(Param1 INT, Param2 VARCHAR(45))
    -> BEGIN
    ->   DECLARE EXIT HANDLER FOR SQLEXCEPTION
    ->     BEGIN
    ->       ROLLBACK;
    ->       SELECT 'Fail' Status;
    ->     END; 
    ->   DECLARE EXIT HANDLER FOR SQLWARNING
    ->     BEGIN
    ->       ROLLBACK;
    ->       SELECT 'Fail' Status;
    ->   END;
    ->   START TRANSACTION;
    ->   INSERT INTO `t1` (`c0`) VALUES (0);
    ->   IF Param1 = 0 THEN
    ->     INSERT INTO `ERR_t2` (`c0`) VALUES (0);
    ->   ELSE
    ->     INSERT INTO `t2` (`c0`) VALUES (0);
    ->   END IF;
    ->   SELECT 'Success' Status;
    ->   COMMIT;
    -> END//
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

mysql> CALL `PrcCopyQuestion_Admin`(0, NULL);
+--------+
| Status |
+--------+
| Fail   |
+--------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT
    ->   `c0`
    -> FROM
    ->   `t1`;
Empty set (0.00 sec)

mysql> SELECT
    ->   `c0`
    -> FROM
    ->   `t2`;
Empty set (0.00 sec)

mysql> CALL `PrcCopyQuestion_Admin`(1, NULL);
+---------+
| Status  |
+---------+
| Success |
+---------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT
    ->   `c0`
    -> FROM
    ->   `t1`;
+----+
| c0 |
+----+
|  2 |
+----+
1 row in set (0.00 sec)

mysql> SELECT
    ->   `c0`
    -> FROM
    ->   `t2`;
+----+
| c0 |
+----+
|  1 |
+----+
1 row in set (0.00 sec)