MariaDB/MySQL: Rollback not working in procedure

Issue

I am trying to create a procedure that contains a transaction that has a rollback:

create or replace procedure move_animals(in diernaam varchar(25)) 
    BEGIN
        DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING
            START TRANSACTION;

        BEGIN
            ROLLBACK;
        end;
        insert into moved_animals(naam)
            values(diernaam);   
        delete from animals 
            where naam in (diernaam);
        COMMIT;
    end;
    
call move_animals("giraf");

However, when I am doing an insert on table moved_animals that isn’t possible due to unique constraints it’s still deleting that record from animal. I would like to then rollback and undo the delete. What am I missing here?

Solution

You have START TRANSACTION and ROLLBACK in the wrong places. You should roll back in the exception handler, and start the transaction in the main body of the procedure.

create or replace procedure move_animals(in diernaam varchar(25)) 
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING
        ROLLBACK;

    START TRANSACTION;
    insert into moved_animals(naam)
        values(diernaam);   
    delete from animals 
        where naam in (diernaam);
    COMMIT;
end;

Answered By – Barmar

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published