CODEWITHSUNDEEP
mysqlsql
shanmugharaj
shanmugharaj

Reputation: 3924

Multiple Transactions in a Stored procedures MySql

Code structure like 

    Procedure (sp1)
        start transaction 
          select ....    
          insert ...    
          CALL sp2    
        END Transaction
   end procedure

The wire frame is like that. In that my doubt is

1.sp2 is called within the sp1 under transaction. will the same transaction is applicable for sp2 or not ?

2.secondly if i need a different transaction level in sp2 can i include transaction in sp2 separetly or not along with transaction in sp1 also?

I am new to DataBase. Help me in figuring out this..

Thanks in advance..

Upvotes: 2

Views: 1926

Answers (1)

Michael - sqlbot
Michael - sqlbot

Reputation: 179074

You can't directly nest transactions in MySQL, but sp2 would run just fine inside sp1's transaction, as long as sp2 doesn't also try to START TRANSACTION, because starting a transaction while already inside a transaction implicitly commits the first transaction, making a rollback impossible for the original transaction.

A better and safer approach is for the invoker of the procedure to be in charge of the transaction, and the procedure to wrap itself in a savepoint. Then the procedure can rollback to the savepoint that it created, if needed, to handle errors -- and savepoints can be nested.

http://dev.mysql.com/doc/refman/5.6/en/savepoint.html

Upvotes: 1

Related Questions