首页 / 技术问答 / Mysql问答列表 / 问答详情

MySQL 事务绑定多个存储过程调用和回滚的简单方法

首发海报

所以我的问题是这样的.我有一个存储的过程，它正在做一些"事情".在这个"任务"中，我还调用了其他正在执行"附加任务"的存储过程.

我希望能够在主SP中将自动提交设置为关闭.

SET SESSION autocommit = 0;

问题是，当调用其他SP时，它是否也会遵守自动提交=0？如果不是，并且我必须强制设置它，我如何确保回滚工作？

示例:


CREATE PROCEDURE sp_whatever ()

BEGIN
    SET SESSION autocommit = 0;
    DECLARE track_no INT DEFAULT 0;
     DECLARE EXIT HANDLER FOR SQLEXCEPTION, NOT FOUND, SQLWARNING;
    -- Error Handling Block
        errorHandling:BEGIN
            
                ROLLBACK;
                GET DIAGNOSTICS CONDITION 1 @`errno` = MYSQL_ERRNO, @`sqlstate` = RETURNED_SQLSTATE, @`text` = MESSAGE_TEXT;
                SET @full_error = CONCAT('ERROR ', @`errno`, ' (', @`sqlstate`, '): ', @`text`);
                SELECT track_no, @full_error;
            
            END errorHandling;
                
START TRANSACTION;
     DO SOME SELECTS;
     DO SOME DMLS;
     Call some stored_proc();   
     DO SOME MORE SELECTS;
     DO SOME MORE DMLS;
     Call some additional stored_proc_additional();

  COMMIT;       
        
END;

如果错误发生在此sp_any过程中，或者在stored_proc()或stored_proc_additional()中，它是否会回滚整个事务，或者只是回滚发生错误的本地过程中的内容.如果我在发生错误时调用其他进程，那么使其能够回滚整个事务的最佳实践是什么？这有可能吗？

如果我将我的所有SP折叠到一个巨大的SP中，那么问题就会出现在每个Begin...End块中，因为MySQL文档提到:

由于局部变量仅在存储程序执行期间才在作用域中，因此在存储程序中创建的预准备语句中不允许引用它们.预准备语句的作用域是当前会话，而不是存储的程序，因此该语句可以在程序结束后执行，此时变量将不再在作用域中.例如， Select ...INTO LOCAL_var不能用作预准备语句.

有什么建议/ idea 吗？

调用多个存储的过程，但无法回滚整个事务

推荐答案

如果您在主过程中定义了一个捕获所有错误的退出处理程序来回滚，那么当其中的所有过程发送错误消息时，它都会对它们起作用.我们有一个过程n1，它将数值1到5插入到测试表的PK列中.然后我们有过程n2，它插入6到10，但随后将数字5插入到表中，这会引发重复的PK值错误.接下来，在我们的主SP nn中，我们将前面提到的过程放在START TRANSACTION部分下，并在它们之外添加一些其他东西.

create table test (id int primary key);
delimiter //
drop procedure if exists n1 //
drop procedure if exists n2 //
drop procedure if exists nn //

create procedure n1()
begin
declare n int default 1;
while n<=5 do
insert test values(n);
set n=n+1;
end while;

end//

create procedure n2()
begin
declare n int default 6;
while n<=10 do
insert test values(n);
set n=n+1;
end while;
insert test values(5); -- this raises a dup pk value error

end//


create procedure nn()
begin
declare exit handler for sqlexception 
begin  
rollback; 
resignal;
end;

truncate test;

start transaction ;
insert test values(100);
call n1;
insert test values(200);
call n2;
insert test values(300);
commit;
end//

delimiter ;

让我们调用Main过程:

call nn;
ERROR 1062 (23000): Duplicate entry '5' for key 'PRIMARY'

select * from test;
Empty set (0.00 sec)

如结果所示，整个会话被回滚.

总而言之，在主过程中，如果您已经预定义了一个退出处理程序，以便在发现错误时回滚，则当事务下的所有嵌套过程引发ERR消息时，它将应用于这些过程，这可以被视为一个要么全有要么全不执行的作业(job).

但是，这并不是事情的结束.如果我们将来自子SP的错误通知设置为静音，情况会怎样？例如，我们在n2中声明了一个退出处理程序，但没有定义RESIGNAL语句，这将使n2向外部发送query ok通知，而不是取消屏蔽真实事件.看看这个:

delimiter //
drop procedure if exists n1 //
drop procedure if exists n2 //
drop procedure if exists nn //

create procedure n1()
begin
declare n int default 1;
while n<=5 do
insert test values(n);
set n=n+1;
end while;

end//

create procedure n2()
begin
declare n int default 6;
declare exit handler for sqlexception begin end;

while n<=10 do
insert test values(n);
set n=n+1;
end while;
insert test values(5); -- this raises a dup pk value error

end//


create procedure nn()
begin
declare exit handler for sqlexception 
begin  
rollback; 
resignal;
end;

truncate test;

start transaction ;
insert test values(100);
call n1;
insert test values(200);
call n2;
insert test values(300);
commit;
end//

delimiter ;

call nn;
Query OK, 0 rows affected (0.02 sec)

 select * from test;

+-----+
| id  |
+-----+
|   1 |
|   2 |
|   3 |
|   4 |
|   5 |
|   6 |
|   7 |
|   8 |
|   9 |
|  10 |
| 100 |
| 200 |
| 300 |
+-----+

如我们所见，由于主SP nn不再收到错误消息，因此其退出处理程序将不再被触发，因此不会执行回滚.

换句话说，您可以调整子SP，使其工作对您有利，就像我们刚刚做的那样，将来自某些SP的错误消息静音.您甚至可以在不同的SP中自定义错误代码，并在主SP中声明特定的处理程序来处理每个代码.这由您决定.

但请注意，来自嵌套SP的提交或回滚也适用于外部.在下面的简化示例中(这次没有声明条件处理程序)，n2在末尾有一个回滚，该回滚不仅在其自身工作，而且还在外部传播.

delimiter //
drop procedure if exists n1 //
drop procedure if exists n2 //
drop procedure if exists nn //

create procedure n1()
begin
declare n int default 1;

while n<=5 do
insert test values(n);
set n=n+1;
end while;

end//

create procedure n2()
begin
declare n int default 6;

while n<=10 do
insert test values(n);
set n=n+1;
end while;

rollback;

end//


create procedure nn()
begin

truncate test;

start transaction ;
insert test values(100);
call n1;
insert test values(200);
call n2; -- there is a rollback at the end of the procedure
insert test values(300);
commit;
end//

delimiter ;

 call nn;
Query OK, 0 rows affected (0.06 sec)

select * from test;
+-----+
| id  |
+-----+
| 300 |
+-----+

如上所述，从n2开始回滚将undo撤消到目前为止事务中的所有更改.

最后，将自动提交设置为关闭时请小心.因为它的效果持续到会话一直持续到切换.当大脑打算执行自动提交操作时，可能会潜在地导致数据丢失.在这方面，使用START TRANSACTION更安全.

UPDATED with procedure n2.1 thrown in n2个

delimiter //
drop procedure if exists n1 //
drop procedure if exists n2 //
drop procedure if exists `n2.1` //
drop procedure if exists nn //

create procedure n1()
begin
declare n int default 1;

while n<=5 do
insert test values(n);
set n=n+1;
end while;

end//

create procedure `n2.1`()
begin
insert test values(999);
end//

create procedure n2()
begin
declare n int default 6;

call `n2.1`;
while n<=10 do
insert test values(n);
set n=n+1;
end while;

end//


create procedure nn()
begin

truncate test;

start transaction ;
insert test values(100);
call n1;
insert test values(200);
call n2; 
insert test values(300);
commit;
end//

delimiter ;

让我们称之为:


call nn;

select * from test;
+-----+
| id  |
+-----+
|   1 |
|   2 |
|   3 |
|   4 |
|   5 |
|   6 |
|   7 |
|   8 |
|   9 |
|  10 |
| 100 |
| 200 |
| 300 |
| 999 |
+-----+

此外，为了演示外部处理程序是否可以处理来自3个级别的SQLEXCEPTION，我特意犯了n2.1个错误.然后让我们看看主nn中的出口处理程序是否会处理它.

delimiter //
drop procedure if exists n1 //
drop procedure if exists n2 //
drop procedure if exists `n2.1` //
drop procedure if exists nn //

create procedure n1()
begin
declare n int default 1;

while n<=5 do
insert test values(n);
set n=n+1;
end while;

end//

create procedure `n2.1`()
begin
insert test values(999);
insert test values(999);
end//

create procedure n2()
begin
declare n int default 6;

call `n2.1`;
while n<=10 do
insert test values(n);
set n=n+1;
end while;

end//


create procedure nn()
begin
declare exit handler for sqlexception 
begin  
commit; 
resignal;
end;
truncate test;

start transaction ;
insert test values(100);
call n1;
insert test values(200);
call n2; 
insert test values(300);
commit;
end//

delimiter ;

现在是关键时刻了.

call nn;
ERROR 1062 (23000): Duplicate entry '999' for key 'PRIMARY'
select * from test;
+-----+
| id  |
+-----+
|   1 |
|   2 |
|   3 |
|   4 |
|   5 |
| 100 |
| 200 |
| 999 |
+-----+

如结果所示，nn中的退出处理程序通过提交到目前为止的更改并在终止过程之前发出错误消息来处理n2.1中的错误.

现在，如果您想知道如果n2和nn都有处理程序会发生什么情况.我还为你准备了另外两个箱子.在情况1中，n2通过删除其处理程序中的resignal来静音错误，而在情况2中，n2通过在其处理程序中删除resignal来通知错误.请注意，在这两种情况下，这次都使用了n2个Continue处理程序.

delimiter //
drop procedure if exists n1 //
drop procedure if exists n2 //
drop procedure if exists `n2.1` //
drop procedure if exists nn //

create procedure n1()
begin
declare n int default 1;

while n<=5 do
insert test values(n);
set n=n+1;
end while;

end//

create procedure `n2.1`()
begin
insert test values(999);
insert test values(999);
end//

create procedure n2()
begin
declare n int default 6;
declare continue handler for sqlexception 
begin  
rollback; 
end;

call `n2.1`;
while n<=10 do
insert test values(n);
set n=n+1;
end while;

end//


create procedure nn()
begin
declare exit handler for sqlexception 
begin  
commit; 
resignal;
end;
truncate test;

start transaction ;
insert test values(100);
call n1;
insert test values(200);
call n2; 
insert test values(300);
commit;
end//

delimiter ;

以下是第一种情况的结果:

call nn;
Query OK, 0 rows affected (0.03 sec)

select * from test;
+-----+
| id  |
+-----+
|   6 |
|   7 |
|   8 |
|   9 |
|  10 |
| 300 |
+-----+

正如结果所反映的那样，到目前为止，n2个回滚的更改并继续进行(没有因为缺少resignal而引发错误).而nn人则继续工作，就像到目前为止没有任何异常发生一样.

现在如果你还在听我的话，第二种情况就来了.注意处理程序的内容，因为n2有提交和重发信号，而nn有回滚(为了通知而重发信号).

delimiter //
drop procedure if exists n1 //
drop procedure if exists n2 //
drop procedure if exists `n2.1` //
drop procedure if exists nn //

create procedure n1()
begin
declare n int default 1;

while n<=5 do
insert test values(n);
set n=n+1;
end while;

end//

create procedure `n2.1`()
begin
insert test values(999);
insert test values(999);
end//

create procedure n2()
begin
declare n int default 6;
declare continue handler for sqlexception 
begin  
commit; 
resignal;
end;

call `n2.1`;
while n<=10 do
insert test values(n);
set n=n+1;
end while;

end//


create procedure nn()
begin
declare exit handler for sqlexception 
begin  
rollback; 
resignal;
end;
truncate test;

start transaction ;
insert test values(100);
call n1;
insert test values(200);
call n2; 
insert test values(300);
commit;
end//

delimiter ;

现在，我们在 case 2中得到了真相.

call nn;
ERROR 1062 (23000): Duplicate entry '999' for key 'PRIMARY'

select * from test;
+-----+
| id  |
+-----+
|   1 |
|   2 |
|   3 |
|   4 |
|   5 |
| 100 |
| 200 |
| 999 |
+-----+

因此，n2提交了到目前为止的更改，同时也喊出了一个错误.HERE IS A BOOKMARK WHICH WE WILL COME BACK SHORTLY AFTER.收到错误后，nn执行回滚，当然无法undo撤消n2已提交的更改，并取消其余更改.

现在让我们回到书签上.当n2喊出错误时，你认为真正发生了什么？因为对于它的Continue处理程序，由于处理程序操作为continue，它应该继续其工作.但是来自nn的处理程序只会回滚并终止当前过程.判断下面的代码以找出答案.注意，在n2的末尾有一个提交，以便对真实发生的事情进行证明.

 
delimiter //
drop procedure if exists n1 //
drop procedure if exists n2 //
drop procedure if exists `n2.1` //
drop procedure if exists nn //

create procedure n1()
begin
declare n int default 1;

while n<=5 do
insert test values(n);
set n=n+1;
end while;

end//

create procedure `n2.1`()
begin
insert test values(999);
insert test values(999);
end//

create procedure n2()
begin
declare n int default 6;
declare continue handler for sqlexception 
begin  
commit; 
resignal;
end;

call `n2.1`;
while n<=10 do
insert test values(n);
set n=n+1;
end while;
commit;
end//


create procedure nn()
begin
declare exit handler for sqlexception 
begin  
rollback; 
resignal;
end;
truncate test;

start transaction ;
insert test values(100);
call n1;
insert test values(200);
call n2; 
insert test values(300);
commit;
end//

delimiter ;

看哪，窗帘在这里揭开了面纱.

call nn;
ERROR 1062 (23000): Duplicate entry '999' for key 'PRIMARY'

select * from test;
+-----+
| id  |
+-----+
|   1 |
|   2 |
|   3 |
|   4 |
|   5 |
| 100 |
| 200 |
| 999 |
+-----+

它以n2结束，没有使用处理程序动作continue完成其过程的其余部分.这意味着，主过程nn中的退出处理程序一旦接收到来自n2的错误，就废除一切，而不是在执行其回滚和重发信号职责之前.

这是一本很长的书，我希望你没有太厌烦.希望这能有所帮助.

Mysql相关问答推荐

如何重新采样SQL数据库

查找关联数据库表的超集

MySQL工作台的编码问题

无法确定查询逻辑

发生的原因及解决方法

使用mysql查询获取不关注user1的user3

拆分列值并适当地返回拆分值

如何使用sql查询在日期列中按值和最大值分组的表中添加列？

仅计算 DATEDIFF (MySQL) 中的工作日

如何替换 SELECT 查询中重复记录的列？

Mysql 相等性反对 false 或 true

计算每个用户在第二个表中有多少条记录

如何使用 C++ 连接 mySQL 数据库

mysql错误：错误1018(HY000)：无法读取'.'的目录(错误号：13)

docker-entrypoint-initdb 中的 MySQL 脚本未执行

PHP 判断 NULL

MySQL ORDER BY rand()，名称为 ASC

MYSQL_ROOT_PASSWORD 已设置但在 docker 容器中获取用户'root'@'localhost'的访问被拒绝(使用密码：YES)

在 Mac 上设置 Laravel php artisan migrate 错误：没有这样的文件或目录

将数据库从 Postgres 迁移到 MySQL

实用课程推荐
相关教程推荐
休闲君评测 |  TOOLFK工具网 |  古诗文网 |  良许Linux教程网 |  Python之禅 |  红色石头 |  入党申请书 | 

© 2020 Hi LearnFk 意见反馈 免责声明 关于我们 桂ICP备11002319号-3 友链&广告位+QQ: 1963612630