怎么捕捉到错误进行roolback
DELIMITER $$DROP PROCEDURE IF EXISTS `test`.`transaction`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `transaction`()
BEGIN
set autocommit = 0;
/*start TRANSACTION;*/
insert into t3 (id) values(90000);
insert into t3 (id3) values(60000);
if ERRORS <> 0 then
/*rollback to SAVEPOINT savepoint_1;*/
rollback;
end if;
END$$DELIMITER ;
第二条是错误的,但第一条却执行了,
怎么让第一条也rollback啊?
表的结果是innodb类型的
DELIMITER $$DROP PROCEDURE IF EXISTS `test`.`transaction`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `transaction`()
BEGIN
set autocommit = 0;
/*start TRANSACTION;*/
insert into t3 (id) values(90000);
insert into t3 (id3) values(60000);
if ERRORS <> 0 then
/*rollback to SAVEPOINT savepoint_1;*/
rollback;
end if;
END$$DELIMITER ;
第二条是错误的,但第一条却执行了,
怎么让第一条也rollback啊?
表的结果是innodb类型的
if @@error_count <> 0 then
要进行异常处理,否则,后面的语句都执行不了。
示例:DROP PROCEDURE IF EXISTS `p_test`;
CREATE DEFINER=`root`@`localhost` PROCEDURE `p_test`()
BEGIN
declare v_i int;
declare continue handler for 1062 set v_i=1;
set v_i=0;
start transaction;
insert into tb_test22 (id) values(90000);
insert into tb_test22 (id) values(90000); if v_i <> 0 then
rollback;
else
commit;
end if;
END;
BEGIN
set autocommit = 0;
insert IGNORE into t3 (id) values (1);
insert IGNORE into t3 (idrr) values (2);
if @@error_count <> 0 then
rollback;
else
commit;
end if;
END$$DELIMITER ;
是这样吗?
还是不好用啊,第一个还是存进去了啊
DROP PROCEDURE IF EXISTS `test`.`p_test`$$
CREATE PROCEDURE `test`.`p_test`()
BEGIN
declare v_i int;
declare continue handler for 1062 set v_i=1;
set v_i=0; start transaction;
insert into tt (id) values(9);
insert into tt (ideee) values(10); if v_i <> 0 then
rollback;
else
commit;
end if; END$$DELIMITER ;
还是一样的,第一个存进去了
请楼上大哥说清楚、具体些
mysql> DROP PROCEDURE IF EXISTS `transaction1`$$
Query OK, 0 rows affected (0.00 sec)mysql> CREATE PROCEDURE transaction1()
-> BEGIN
-> set autocommit = 0;
-> insert IGNORE into t3 (id) values (1);
-> insert IGNORE into t3 (idrr) values (2);
-> if @@warning_count <> 0 then
-> rollback;
-> else
-> commit;
-> end if;
-> END$$
Query OK, 0 rows affected (0.00 sec)mysql> DELIMITER ;
mysql> call transaction1();
Query OK, 0 rows affected, 1 warning (0.06 sec)mysql> select * from t3;
Empty set (0.00 sec)mysql>
mysql> DROP PROCEDURE IF EXISTS `transaction1`$$
Query OK, 0 rows affected (0.00 sec)mysql> CREATE PROCEDURE transaction1()
-> BEGIN
-> set autocommit = 0;
-> insert IGNORE into t3 (id) values (1);
-> insert IGNORE into t3 (idrr) values (2);
-> if @@warning_count <> 0 ||@@error_count>0 then
-> rollback;
-> else
-> commit;
-> end if;
-> END$$
Query OK, 0 rows affected (0.00 sec)mysql> DELIMITER ;
mysql> call transaction1();
Query OK, 0 rows affected, 1 warning (0.06 sec)mysql> select * from t3;
Empty set (0.00 sec)mysql>