mysql的procedure错误回滚问题

先科普一点知识：mysql在一个事物中发生错误时，是不会回滚整个事物的。

我们先证明这点

mysql> desc wzy;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| x     | int(11) | NO   | PRI | NULL    |       |
| y     | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.09 sec)

这是表结构

我们建一个procedure用于测试

CREATE DEFINER=`root`@`%` PROCEDURE `err_test1`(in v_x int,in v_y int,in v_z int)
    SQL SECURITY INVOKER
BEGIN
START TRANSACTION;
INSERT INTO wzy.wzy VALUES(v_x,v_x+1);
INSERT INTO wzy.wzy VALUES(v_y,v_y+2);
INSERT INTO wzy.wzy VALUES(v_z,v_z+3);
COMMIT;
END

mysql> call wzy.err_test1(1,2,3);
Query OK, 0 rows affected (0.09 sec)


mysql> select * from wzy;
+---+------+
| x | y    |
+---+------+
| 1 |    2 |
| 2 |    4 |
| 3 |    6 |
+---+------+
3 rows in set (0.00 sec)

mysql> truncate table wzy;
Query OK, 0 rows affected (0.12 sec)mysql> call wzy.err_test1(1,1,3);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> select * from wzy;
+---+------+
| x | y    |
+---+------+
| 1 |    2 |
+---+------+
1 row in set (0.00 sec)

先测试过程可用，再清空表

mysql> call wzy.err_test1(1,1,3);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> select * from wzy;
+---+------+
| x | y    |
+---+------+
| 1 |    2 |
+---+------+
1 row in set (0.00 sec)

这次第二行数据与第一行主键冲突，过程结束，第三行数据并没有进入数据库，但是第一行数据还是进入了数据库

现在我们看下

DECLARE EXIT HANDLER FOR SQLEXCEPTION

和

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION

两种方式处理的procedure，在错误时处理的不同。

DECLARE EXIT HANDLER FOR SQLEXCEPTION的部分

CREATE DEFINER=`root`@`%` PROCEDURE `err_test2`(in v_x int,in v_y int,in v_z int)
    SQL SECURITY INVOKER
BEGIN

DECLARE EXIT HANDLER FOR SQLEXCEPTION
START TRANSACTION;

INSERT INTO wzy.wzy VALUES(v_x,v_x+1);
INSERT INTO wzy.wzy VALUES(v_y,v_y+2);
INSERT INTO wzy.wzy VALUES(v_z,v_z+3);
COMMIT;
END

清空表，跑包

mysql> truncate table wzy;
Query OK, 0 rows affected (0.06 sec)


mysql> call err_test2(1,1,3);
Query OK, 0 rows affected (0.01 sec)


mysql> select * from wzy;
+---+------+
| x | y    |
+---+------+
| 1 |    2 |
+---+------+
1 row in set (0.00 sec)

我们发现只有第一行数据入库，与不声明exit情况相同

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION的部分

CREATE DEFINER=`root`@`%` PROCEDURE `err_test3`(in v_x int,in v_y int,in v_z int)
    SQL SECURITY INVOKER
BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
START TRANSACTION;
INSERT INTO wzy.wzy VALUES(v_x,v_x+1);
INSERT INTO wzy.wzy VALUES(v_y,v_y+2);
INSERT INTO wzy.wzy VALUES(v_z,v_z+3);
COMMIT;
END

清空，并执行

mysql> truncate table wzy;
Query OK, 0 rows affected (0.07 sec)


mysql> call err_test3(1,1,3);
Query OK, 0 rows affected (0.02 sec)


mysql> select * from wzy;
+---+------+
| x | y    |
+---+------+
| 1 |    2 |
| 3 |    5 |
+---+------+
2 rows in set (0.00 sec)

我们看到1、3都被执行了。可见continue会忽略报错继续执行。

那我们该如何回滚整个包的事物呢？我们可以通过定义一个变量来判断是否进行回滚。

这里再对比下EXIT与CONTINUE的区别

EXCEPTION 的情况下用变量做判断。

CREATE DEFINER=`root`@`%` PROCEDURE `err_test4`(in v_x int,in v_y int,in v_z int)
    SQL SECURITY INVOKER
BEGIN
DECLARE EXCEPTION INT DEFAULT 0;
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET EXCEPTION = 1;
START TRANSACTION;
INSERT INTO wzy.wzy VALUES(v_x,v_x+1);
INSERT INTO wzy.wzy VALUES(v_y,v_y+2);
INSERT INTO wzy.wzy VALUES(v_z,v_z+3);
IF EXCEPTION = 1 THEN
ROLLBACK;
ELSE
COMMIT;
END IF;
END

mysql> truncate table wzy;
Query OK, 0 rows affected (0.13 sec)


mysql> select * from wzy;
Empty set (0.00 sec)


mysql> call err_test4(1,1,3);
Query OK, 0 rows affected (0.00 sec)


mysql> select * from wzy;
+---+------+
| x | y    |
+---+------+
| 1 |    2 |
+---+------+
1 row in set (0.00 sec)

数据还是入库了！可见变量没有发挥作用。

CONTINUE 的情况

CREATE DEFINER=`root`@`%` PROCEDURE `err_test5`(in v_x int,in v_y int,in v_z int)
    SQL SECURITY INVOKER
BEGIN
DECLARE EXCEPTION INT DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET EXCEPTION = 1;
START TRANSACTION;
INSERT INTO wzy.wzy VALUES(v_x,v_x+1);
INSERT INTO wzy.wzy VALUES(v_y,v_y+2);
INSERT INTO wzy.wzy VALUES(v_z,v_z+3);
IF EXCEPTION = 1 THEN
ROLLBACK;
ELSE
COMMIT;
END IF;
END

mysql> truncate table wzy;
Query OK, 0 rows affected (0.24 sec)


mysql> call err_test5(1,1,3);
Query OK, 0 rows affected (0.15 sec)


mysql> select * from wzy;
Empty set (0.00 sec)

整个procedure都被回滚了！可见CONTINUE会执行到最后，变量发挥了作用

经过对比我们可以知道

EXIT会在执行错误时，直接跳出过程，不去执行剩下的语句，不利与判断回滚，而且不声明时，mysql默认就是这么处理的。

CONTINUE会在执行错误时，继续执行过程，我们可以通过设置变量判断这个过程是否需要回滚