first time poster
So my issue/question is this. I have a stored proc that is doing some "stuff". Within this "stuff", I am calling other stored procs as well that are doing "additional stuff".
I want to be able to set the auto commit to OFF in the main SP.
SET SESSION autocommit = 0;
Question is, when the other SPs get called, will it also still obey the autocommit=0? If not and I have to set it manully, how do I ensure the rollback works?
Example:
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;
If an error occurs within this sp_whatever proc or in the stored_proc() or stored_proc_additional(), will it rollback the whole transaction or just what is in the local procedure where the error occured. What is best practice to either make it so I can rollback the whole transaction if I am calling other procs when an error occurs? Is this even possible?
If I collapse all my SP into one massive SP, then the issue becomes within each BEGIN...END Block, I am unable to use a PREPARE STATEMENT due to the MySQL docs mentioning:
Because local variables are in scope only during stored program execution, references to them are not permitted in prepared statements created within a stored program. Prepared statement scope is the current session, not the stored program, so the statement could be executed after the program ends, at which point the variables would no longer be in scope. For example, SELECT ... INTO local_var cannot be used as a prepared statement.
Any advice/Thoughts?
Calling multiple Stored Procs but unable to rollback the whole transaction
If you define a catch-all-error exit handler to rollback in the main procedure, it works on all procedures within it when they send an error message.Take this case below for example. We have a procedure n1 which inserts numeric values 1 to 5 into our test table's PK column. Then we have procedure n2 which inserts 6 to 10, but it then inserts number 5 to the table, which raises a duplicate PK value error. Next, in our main SP nn we put those beforementioned procedures under the START TRANSACTION section and add some other stuff besides them.
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 ;
Let's call the main procedure:
call nn;
ERROR 1062 (23000): Duplicate entry '5' for key 'PRIMARY'
select * from test;
Empty set (0.00 sec)
As the result shows, the whole session is rolled back.
To wrap up, within your main procedure, if you have predefined an exit handler to rollback when an error is found, it applies to all the nested procedures under a transaction when they raise an err message , which can be regarded as an ALL-OR-NOTHING job.
But, this is not the end of it. What if we mute the error notification from a sub SP ? For example, we declare an exit handler in n2 but does not define the RESIGNAL statement, which will make n2 sends a query ok notice to the outside instead of unmasking the true event. Check this out:
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 |
+-----+
As we can see, since the error message is no longer received in the main SP nn, its exit handler will no longer be triggered, thus no rollback is performed.
In other words , you can tweak the sub SP to make it work to your favour like muting error message from certain SP which we just did. You can even customise error code in different SP and declare specific handlers in the main SP to address each code. It's for you to decide.
But beware, a commit or rollback from a nested SP also applies to the outside. In the simplified example below (no condition hanlder declared this time) , n2 has a rollback at the end which not only works on itself but spreads outside as well.
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 |
+-----+
As denoted above, the rollback from n2 undoes all changes in the transaction so far.
Finally, please be care when setting autocommit off. As its effect lasts as long as the session stays until toggled on. It may potentially give rise to loss of data when the mind intends the behaviors of autocommit. Use of START TRANSACTION is safer in this regard.
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 ;
Let's call it:
call nn;
select * from test;
+-----+
| id |
+-----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
| 100 |
| 200 |
| 300 |
| 999 |
+-----+
In addition, to demonstrate if a SQLEXCEPTION from 3 levels deep can be handled by outside handler, I'm purposely making n2.1 blunder. Then let's see if the exit handler in our main nn will deal with it.
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 ;
Now the moment of truth.
call nn;
ERROR 1062 (23000): Duplicate entry '999' for key 'PRIMARY'
select * from test;
+-----+
| id |
+-----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 100 |
| 200 |
| 999 |
+-----+
As the result depicts, it turns out the exit handler in nn took care of the error from n2.1 by committing changes so far and signaling the error message before terminating the procedure.
And now, in the event that you wonder what happens if both n2 and nn have handlers. I've also prepared two more cases for you. In case 1, n2 mutes the error by removing the resignal in its handler, while in case 2, n2 announces the error with the resignal in its handler. Note, in both cases, n2 uses continue handler this time .
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 ;
Here is the result for case 1:
call nn;
Query OK, 0 rows affected (0.03 sec)
select * from test;
+-----+
| id |
+-----+
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
| 300 |
+-----+
As the result reflects, the n2 rolled back changes so far and moved on (withouting raising an error due to the absence of resignal). And nn just carried on as if nothing abnormal had happened so far.
Now if you are still with me, here comes case 2. Pay attention to the contents of handlers as n2 has commit and resignal , while nn has rollback (and resignal for the sake of notification).
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 ;
Now we are getting the truth in 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 |
+-----+
So n2 committed the changes up until now as well as cried an error. HERE IS A BOOKMARK WHICH WE WILL COME BACK SHORTLY AFTER. Upon receiving the error, nn performed a rollback, which of course could not undo the changes already commited by n2, and called off the rest.
Now let's get back to the bookmark. What do you think really happened when n2 shouted the error? Because for its continue handler, it should carry on its job due to the handler action being continue. But the handler from nn would just rollback things and kill the current procedure. Check the code below to find out. Note there is a commit at the end of n2 so as to make a proof of things truly having happened.
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 ;
Lo and behold, here the curtain unveils.
call nn;
ERROR 1062 (23000): Duplicate entry '999' for key 'PRIMARY'
select * from test;
+-----+
| id |
+-----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 100 |
| 200 |
| 999 |
+-----+
It ends up n2 did not get to finish the rest of its procedure with the handler action continue. Which means, the exit handler in main procedure nn repealed everything as soon as it received the error from n2 but not before executing its rollback and resignal duty.
It's a long read which I pray did not bore you too much. Hopefully it helps.