CODEWITHSUNDEEP
mysqlstored-procedurestransactionsexitrollback
reformed
reformed

Reputation: 4808

Do you need to call ROLLBACK when exiting a MySQL stored procedure early?

For a MySQL stored procedure that uses a transaction, is it necessary to call ROLLBACK when exiting early?

CREATE PROCEDURE `Proc`(IN `param` VARCHAR(10))
sp:BEGIN
    START TRANSACTION;
    IF ( bad condition ) THEN
        ROLLBACK;     /* is this necessary? */
        LEAVE sp;
    END IF;
    ...
    COMMIT;
END

Upvotes: 1

Views: 694

Answers (1)

bamblack
bamblack

Reputation: 3779

It is necessary, because the transaction is still open since you haven't ended it. Anything that implicitly commits the transaction (for example, starting a new transaction) will act as though you ran COMMIT, which is the opposite of what you want

edit It may be different for a stored procedure, since this normally kills everything that was in reference to it when it finishes, but it's always better to be safe than sorry. Also, explicitly killing it makes things easier to understand.

Upvotes: 2

Related Questions