I have this stored procedure:


code_gen_loop: WHILE @Codes > 0 DO

    IF MOD( @Codes, 10000 ) = 0 THEN

        EXECUTE qry_status_stmt2;
        EXECUTE qry_status_stmt3;
        EXECUTE qry_canceled_stmt;

        IF @Canceled = 1 THEN
            LEAVE code_gen_loop;
        END IF;

    END IF;

    EXECUTE qry_rand_stmt;
    EXECUTE qry_insert_stmt;

    SET @Codes = @Codes - ROW_COUNT();


IF @Canceled = 0 THEN
    EXECUTE qry_complete_stmt;

The main loop is made to insert millions of rows into a table. Every 10,000 inserts it updates a memory table with its progress and checks if the job has been canceled. If the job is canceled then ROLLBACK the inserts and break out of the loop. If the job hasn't been canceled then continue the loop until finish and then commit the inserts.

The problem is that if the job is canceled it breaks from the loop correctly but does not rollback the commits. If I go look in the table I can see it has committed the inserts up to where the job was canceled.

Why is my ROLLBACK not working here?

Full procedure can be found http://pastebin.com/2YghzJrE

  • I agree it isn't obvious why this wouldn't work. Right before IF @Canceled = 1 THEN try adding SAVEPOINT foo; ROLLBACK TO SAVEPOINT foo;. The net effect of this should be no change in behavior -- however, it will throw an exception if you are, for whatever reason, not actually inside a transaction at that point, which seems like the most likely explanation, even though it's not obvious from the code shown here why that might be the case. – Michael - sqlbot Feb 10 '15 at 12:50
  • @Michael-sqlbot Done that. Now when running the stored procedure I get SAVEPOINT foo does not exist – Brady Feb 10 '15 at 12:57
  • So, something else is either committing or rolling back your transaction. You are calling EXECUTE on 5 different prepared statements, but what those contain is not shown here. It should be one of those that's doing it. (Unfortunately, ROLLBACK; and COMMIT; are both accepted by MySQL without raising an exception, when you don't actually have a transaction running; the ROLLBACK TO SAVEPOINT hack doesn't silently fail like the others, and hence the reason this test works.) – Michael - sqlbot Feb 10 '15 at 13:01
  • @Michael-sqlbot Thanks for your help so far. I have updated my question to include a link to the full procedure. As you can see none of those prepared statements are committing or rolling back... – Brady Feb 10 '15 at 13:23
  • 1
    I think I have found the issue. I am running TRUNCATE TABLE on the memory table in one of the prepared statements. According to the docs Truncate operations cause an implicit commit. – Brady Feb 10 '15 at 13:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.