How to rollback data in mysql?

Question

0.00/5 (No votes)

See more:

I got 2 stored procedure that using for insert to different table. if my 1st table insert successful and 2nd table failed how i rollback to initial data before the 1st table insert??? i have already try several times.The mysql connection will auto close after end of stored procedure. So is that another ways to solve this problem?? I dont want join 2 table in 1 stored procedure.

What I have tried:

Currently i m using start transaction and set autocommit=0 then set a savepoint to rollback the statement but its failed.For this statement i found that this is just suitable for one stored procedure only.

Posted 23-Feb-16 15:11pm

Member 11105301

Updated 23-Feb-16 17:19pm

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Garth J Lancaster · Answer 1 · 2016-02-23T17:19:00

Solution 1

Im wondering if you need to create a 'master' stored procedure that calls stored procedure 1 and then stored procedure 2 ... that way, you could do something like

SQL

BEGIN
    START TRANSACTION;

    CALL StoredProcedure1(@1, @2)

    CALL StoredProcedure2(@1, @2, etc )

    DECLARE EXIT HANDLER FOR SQLEXCEPTION 
        BEGIN
            ROLLBACK;
            EXIT PROCEDURE;
        END;

    COMMIT;
END

Posted 23-Feb-16 17:19pm

Garth J Lancaster

Comments

Member 11105301 2-Mar-16 23:03pm

if i insert 3 row in different table can i roll back all the row??

Garth J Lancaster 2-Mar-16 23:19pm

you can insert 50 billion rows into whatever tables you need, and as long as you are in the 'transaction scope' you are covered

Member 11105301 3-Mar-16 0:00am

ya it's work thanks

Member 11105301 3-Mar-16 1:42am

if i insert multiple row in 1 table can i rollback??

Member 11105301 3-Mar-16 3:14am

i means after executenonquery row by row