MYSQL: InnoDB inserts wont rollback transaction inside a stored procedure

Question

I have this stored procedure:

    START TRANSACTION;

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
            ROLLBACK;
            LEAVE code_gen_loop;
        END IF;

    END IF;

    EXECUTE qry_rand_stmt;
    EXECUTE qry_insert_stmt;

    SET @Codes = @Codes - ROW_COUNT();

END WHILE;

IF @Canceled = 0 THEN
    COMMIT;
    EXECUTE qry_complete_stmt;
END IF;

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

Answer 1

I found the issue. I was running TRUNCATE TABLE on the memory table in one of the prepared statements. According to the docs, "Truncate operations cause an implicit commit." – https://dba.stackexchange.com/u/32842

See MySQL backup InnoDB for a list of statements that trigger implicit commits. - https://dba.stackexchange.com/u/877

Another approach might be to generate and execute a KILL command instead of writing to the memory table. - https://dba.stackexchange.com/u/1876