MariaDB/MySQL: Rollback not working in procedure

Question

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?

This code makes no sense. When an exception happens you start a transaction? — Barmar, Aug 30 '22 at 22:57
I fixed the indentation to match the code structure. Do you see the problem now? — Barmar, Aug 30 '22 at 22:58
@Barmar, I understand better now yes, thank you! I was going off of this answer:https://stackoverflow.com/a/9987510/5790778 — Konan Pruiksma, Aug 31 '22 at 07:18
So you just copied it wrong, since that answer is basically the same as my answer here. You need to understand the answer instead of just copy/pasting blindly. — Barmar, Aug 31 '22 at 14:59

score 3 · Accepted Answer · answered Aug 30 '22 at 23:02

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;

That works! Thank you so much, that makes a lot more sense. – Konan Pruiksma Aug 31 '22 at 07:18 — Konan Pruiksma, Aug 31 '22 at 07:18

MariaDB/MySQL: Rollback not working in procedure

1 Answers1