MySql stored procedures, transactions and rollbacks

Question

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 SP 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;

Thanks.

Answer 1

see duplicate key error does not cancel/rollback mysql transaction for answers

http://stackoverflow.com/q/1630400/444728