MySql stored procedures, transactions and rollbacks

Viewed 20847

I can't find an optimal way to use transactions in a MySql Stored Procedure. I want to ROLLBACK if anything fails:

BEGIN

    SET autocommit=0;
    START TRANSACTION;

    DELETE FROM customers;
    INSERT INTO customers VALUES(100);
    INSERT INTO customers VALUES('wrong type');

    COMMIT;
END

1) Is autocommit=0 required?

2) If the second INSERT breaks (and it does of course) the first INSERT is not rolled back. The procedure simply continues down to the COMMIT. How can I prevent this?

3) I've found I can DECLARE HANDLER, should I use this instruction or is there a simpler way to say that if any command fails, the stored procedure should ROLLBACK and fail too?

DECLARE HANDLER works fine, but since I have MySql version 5.1 I can't use RESIGNAL. So if an error occurs, the caller won't be notified:

DECLARE EXIT HANDLER FOR SQLEXCEPTION 
BEGIN
    ROLLBACK; 
    -- RESIGNAL; not in my version :(
END;

START TRANSACTION;
2 Answers
Related