MySQL存储过程回滚

恒星星 發表於 2026-1-13 14:05:00

MySQL存储过程出错回滚是保证数据一致性的重要措施，如何处理好存储过程出错后的回滚，请看回滚处理步骤及需要注意的几个事项： 
MySQL存储过程回滚 
首先看一下当前MySQL数据库是否已经开启了自动提交。 
在数据库中，使用Mysql>show variables like ‘autocommit’; 
Vaiable_name Value 
Autocommit On 
在存储过程中，有两种方式进行回滚和提交操作，第一种方式采用开启事务的方法；第二种采用关闭自动提交的方法，值得时在存储过程中关闭，而不是整个数据库关闭。 
以下操作是在数据库是自动提交的情况中演示的： 
一、第一种方法开启事务。 
准备两个存储过程分别是NewTest和NewTest1，一张数据表test111： 
Id 
1 
2 
3 
4 
单个存储过程 
1.回滚：单纯的一个存储过程NewTest，如果想要进行回滚或者提交操作的时候需要开启事务，否则每执行一个更新或者删除操作都会被自动提交。 
CREATE DEFINER=<code>root</code>@<code>%</code> PROCEDURE <code>NewTest</code>() 
BEGIN 
START TRANSACTION;#开启事务 
delete from test111 where id=1;#删除操作 
Rollback； #回滚 
commit; #提交 
END； 
结果：（删除操作回滚后，数据不变） 
Id 
1 
2 
3 
4 
2.提交 
CREATE DEFINER=<code>root</code>@<code>%</code> PROCEDURE <code>NewTest</code>() 
BEGIN 
START TRANSACTION;#开启事务 
delete from test111 where id=1;#删除操作 
commit; #提交 
END 
结果：（id=1的删除掉了） 
两个存储过程NewTest为父，NewTest1为子，父调子： 
CREATE DEFINER=<code>root</code>@<code>%</code> PROCEDURE <code>NewTest</code>() 
BEGIN 
START TRANSACTION;#开启事务 
delete from test111 where id=1;#删除操作 
Call NewTest1(); 
delete from test111 where id=3;#删除操作 
commit; #提交 
End;
CREATE DEFINER=<code>root</code>@<code>%</code> PROCEDURE <code>NewTest1</code>() 
BEGIN 
START TRANSACTION;#开启事务 
delete from test111 where id=2;#删除操作 
commit; #提交 
END 
结果：只剩下id=4 
3.父子都开启事务，夫回滚，子提交： 
CREATE DEFINER=<code>root</code>@<code>%</code> PROCEDURE <code>NewTest</code>() 
BEGIN 
START TRANSACTION;#开启事务 
delete from test111 where id=1;#删除操作 
Call NewTest1(); 
delete from test111 where id=3;#删除操作 
Rollback; #回滚 
commit; #提交 
End;
CREATE DEFINER=<code>root</code>@<code>%</code> PROCEDURE <code>NewTest1</code>() 
BEGIN 
START TRANSACTION;#开启事务 
delete from test111 where id=2;#删除操作 
commit; #提交 
END
结果：只剩下ID=4的记录
4.父子都开启事务，夫回滚，子回滚： 
CREATE DEFINER=<code>root</code>@<code>%</code> PROCEDURE <code>NewTest</code>() 
BEGIN 
START TRANSACTION;#开启事务 
delete from test111 where id=1;#删除操作 
Call NewTest1(); 
delete from test111 where id=3;#删除操作 
Rollback; #回滚 
commit; #提交 
End;
CREATE DEFINER=<code>root</code>@<code>%</code> PROCEDURE <code>NewTest1</code>() 
BEGIN 
START TRANSACTION;#开启事务 
delete from test111 where id=2;#删除操作 
Rollback; #回滚 
commit; #提交 
END
结果：剩下ID=2，4的记录 
5.父子都开启事务，夫提交，子回滚： 
CREATE DEFINER=<code>root</code>@<code>%</code> PROCEDURE <code>NewTest</code>() 
BEGIN 
START TRANSACTION;#开启事务 
delete from test111 where id=1;#删除操作 
Call NewTest1(); 
delete from test111 where id=3;#删除操作 
commit; #提交 
End;
CREATE DEFINER=<code>root</code>@<code>%</code> PROCEDURE <code>NewTest1</code>() 
BEGIN 
START TRANSACTION;#开启事务 
delete from test111 where id=2;#删除操作 
Rollback; 
commit; #提交 
END 
结果：剩下ID=2,4的记录 
从以上四个例子可以得出结论：在十五开启中，有且只能有一个事务能够被开启，当子存储过程开启了事务后，前面父事务自动失效，相当于没有开启过事务（也可以认为是第二个事务会将第一个事务给提交掉）。只要开启了事务之后，后面的代码都将被十五所管辖。所以推荐，只让父亲开启事务，这样能够酱紫存储过程也包含到父存储过程的事务中，实现多个存储过程回滚和提交的一致性。 
如果子存储过程既可以当父亲，也可以档子存储过程，那么推荐用一个输入参数来判定，当前他是做为父亲还是儿子，做为父亲就开启事务并负责自己的提交和回滚；做为儿子就不开启事务，接受父亲的管辖，提交和回滚操作也都交于父亲来执行。重要的一点必须在李凯存储过程之前需要有个提交或者是回滚的操作做为结束。 
二、第二种方法关闭自动提交事务设置 
在test111中增加id=12，13，14，15三条记录 
存储过程如下： 
CREATE DEFINER=<code>root</code>@<code>%</code> PROCEDURE <code>NewTest</code>() 
BEGIN 
Set autocommit=0;# 关闭自动提交 
Update test111 set id=66 where id=12;#修改操作 
Commit；#提交 
Update test111 set id=77 where id=13;#修改操作 
Rollback；#回滚 
Update test111 set id=88 where id=14;#修改操作 
Commit; #回滚 
commit; #提交 
End; 
执行后的结果： 
Id 
13 
15 
66 
88 
关闭自动调教的方法比开启事务更加简洁，当存储过程关闭自动提交后，遇到commit将会提交，遇到rollback将会回滚。Commit和rollback互不干扰，不具备包含关系，就如上图所示，commit之后到rollback之前的代码，采后rollback管辖。 
已开启事务的方法一样，父存储过程可以掌控子存储过程（被调用的子存储过程就相当于一段下载父存储过程的代码，与其他父中的代码地位相等）。而且set autocommit=0作用范围不局限于begin-end之间，只要是set autocommit=0之后的代码，都会被其所管辖，最重要一点，一旦set autocommit=0，必须在李凯存储过程之前需要有个commit或rollback的操作做为结束，否则没被提交或回滚的那段代码中操作过增删改的表将会被锁住。 
来源：https://www.cnblogs.com/abcoder-Robert/p/19475649

頁: [1]

琼殿技术论坛's Archiver

MySQL存储过程回滚