MySQL: Handling Rollback in Nested Stored Procedures With Transactions

Question

I have 4 stored procedures, lets name them spA, spB, spC and spD.

Each of these stored procedures has a start transaction, commit and rollback.

spA is currently the parent stored procedure(SP) and within it, I am calling spB, spC, spD in respective order. Apparently, because all the SPs have commit in them, when spC fails, the DML operations performed in spB are not reverted as they are already committed and same when spD fails operations in spB and spC are not rollbacked.

The reason why I have commit in spB, spC, and spD is that later on, I might simply use either of these SPs directly to perform operations respective to the SP.

Currently, I plan on using an input bit flag to decide whether to commit the transaction. In MS SQL, there is an option to check the @@transcount, however, I am unable to find something similar in MySQL.

I was wondering if there is a better way to handle this scenario.

My sugestion is not to put commits in strored procedures. Commit after the sp if everything completed as expected. I didn't really understand the reason why you are doing it this way but I think the cost of doing it this way outweights the benefits. — Juan, May 22 '18 at 14:15
As I mentioned that I plan to use the individual SPs inside the parent SP separately later on. Let's say the spB, spC, spD inserts data in table B, C and D. That way, spA inserts data in all the three tables (B, C, and D) at once. However, later on I might just want to insert data in either table B or C or D. For that, I would simply call the respective spB, spC or spD. Therefore, I would need transaction(start, commit and rollback) in those SPs as well. Does it explain why I am doing it? — Afsan Abdulali Gujarati, May 22 '18 at 14:26
It is easier to manage only one transaction (MySQL itself doesn't have the tools for nested). You can always wrap the B, C and D calls in transactions when you use them later. — Vatev, May 22 '18 at 14:41
One thing I found is that I cannot do what I plan to do because in MySQL every time a new start transaction is encountered, all the DML queries prior to the new start transaction are implicitly committed. So I will have to stick with the wrapping technique. I would have loved if there was a more convenient way. — Afsan Abdulali Gujarati, May 22 '18 at 17:41

score 0 · Answer 1 · answered May 22 '18 at 14:57

I agree with the comments above. I recommend you should start and commit the transaction only at the "top level" in your application, before you call any stored procedures. Do not begin or commit transactions within stored procedures.

Trying to trick ROLLBACK and COMMIT like Microsoft SQL Server does with @@trancount leads to anomalies that are even harder to work with than the guideline above, to manage transactions at the application level.

I posted a similar answer 10 years ago, but it was related to ORM classes, instead of stored procedures: How do detect that transaction has already been started?

Cesar Feliz Damiron · Answer 2 · 2018-08-28T18:24:40.293

manage your own variable it looks like MySQL doesn't handle this behavior. so add an input p_tran_call bit and set it to 1 if spA calls else 0 then do the if p_tran_call is 0 then begin tran and rollback.

sure this is how i did it.

create procedure spB(p_tran_control bit) being

   declare exit handler for sqlexception
   begin
         if p_tran_control = 0 the 
             rollback;
         end if;
   end; #end of handler

  if p_tran_control = 0 then
         begin transaction;
   end if;


    #more code;


   if p_tran_control = 0 then
        commit;
    end if;

end

create procedure spA begin

  begin transaction;


 call spB(1);


  commit transaction;

end

now, something i noticed, the transactions dont go to any nested calls. so if you call spB from spA that works but if you call spC from spB that was called from spA that holds the transaction spC will commit. even when autocommit is set to off;

MySQL: Handling Rollback in Nested Stored Procedures With Transactions

2 Answers2