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