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

Answer 1

Answer to 1: You don't need to set autocommit=0

With START TRANSACTION, autocommit remains disabled until you end the transaction with COMMIT or ROLLBACK. The autocommit mode then reverts to its previous state.

http://dev.mysql.com/doc/refman/5.6/en/commit.html

Answer 2

Different Approach to Answer 2: You could use a boolean variable to know if you should COMMIT or ROLLBACK. For example:

BEGIN

DECLARE `should_rollback` BOOL DEFAULT FALSE;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET `should_rollback` = TRUE;

START TRANSACTION;

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

IF `should_rollback` THEN
    ROLLBACK;
ELSE
    COMMIT;
END IF;
END

Or, you could use your very usefull 3)