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

mxg2im7a  于 2023-05-28  发布在  Mysql
关注(0)|答案(1)|浏览(185)

第一次海报
所以我的问题/问题是这样的。我有一个正在做一些“事情”的存储过程。在这个“东西”中，我也调用了其他正在做“附加东西”的存储过程。
我希望能够在主SP中将自动提交设置为OFF。

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_whatever proc中或者在stored_proc（）或stored_proc_additional（）中发生错误，它会回滚整个事务还是只回滚发生错误的本地过程中的事务。如果我在发生错误时调用其他进程，那么最佳实践是什么？这可能吗
如果我将所有SP折叠成一个大型SP，那么问题就出现在每个开始... END块中，由于MySQL文档提到：
由于局部变量仅在存储程序执行期间在作用域中，因此在存储程序内创建的预准备语句中不允许引用局部变量。预准备语句的作用域是当前会话，而不是存储的程序，因此语句可以在程序结束后执行，此时变量将不再处于作用域中。例如，SELECT... INTO local_var不能用作预准备语句。
任何建议/想法？
调用多个Stored Procs但无法回滚整个事务

mysql

1条答案

按热度按时间
8wigbo56

8wigbo561#

如果你在main过程中定义了一个catch-all-error退出处理程序来回滚，那么当main过程中的所有过程发送错误消息时，它都会起作用。我们有一个过程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)

如结果所示，整个会话被回滚。
最后，在你的主过程中，如果你已经预定义了一个退出处理程序，当发现错误时回滚，它会在事务下的所有嵌套过程引发错误消息时应用到它们，这可以被认为是一个ALL-OR-NOTHING作业。
但是，这不是它的结束。如果我们将来自子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回滚将撤消事务中到目前为止的所有更改。
最后，请小心设置自动提交关闭。因为它的效果持续时间与会话停留的时间一样长，直到切换为打开。它可能会潜在地给予数据丢失时，头脑打算的行为的自动提交。在这方面，使用START TRANSACTION更安全。

更新了n2中抛出的过程n2.1

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 ;

下面是案例1的结果：

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 ;

现在我们在案例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提交了到目前为止的更改，并报了一个错误。**这里有一个书签，我们很快就会回来。**在收到错误后，nn执行了一个回滚，当然不能撤消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的错误后立即废除了所有内容，但不是在执行其回滚和重新发送信号任务之前。
这是一本很长的书，我希望你不要太无聊。希望能帮上忙。

赞(0）分享  回复(0）举报  2023-05-28
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com