mysql transaction - roll back on any exception

You can use 13.6.7.2. DECLARE ... HANDLER Syntax in the following way:

DELIMITER $$CREATE PROCEDURE `sp_fail`()BEGIN    DECLARE `_rollback` BOOL DEFAULT 0;    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET `_rollback` = 1;    START TRANSACTION;    INSERT INTO `tablea` (`date`) VALUES (NOW());    INSERT INTO `tableb` (`date`) VALUES (NOW());    INSERT INTO `tablec` (`date`) VALUES (NOW()); -- FAIL    IF `_rollback` THEN        ROLLBACK;    ELSE        COMMIT;    END IF;END$$DELIMITER ;

For a complete example, check the following SQL Fiddle.

You could use EXIT HANDLER if you for example need to SIGNAL a specific SQL EXCEPTION in your code. For instance:

DELIMITER $$CREATE PROCEDURE `sp_fail`()BEGIN    DECLARE EXIT HANDLER FOR SQLEXCEPTION    BEGIN        ROLLBACK;  -- rollback any changes made in the transaction        RESIGNAL;  -- raise again the sql exception to the caller    END;    START TRANSACTION;    insert into myTable values1 ...    IF fail_condition_meet THEN        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Custom error detected.', MYSQL_ERRNO = 2000;    END IF;    insert into myTable values2 ...  -- this will not be executed    COMMIT; -- this will not be executedEND$$DELIMITER ;

The above solution are good but to make it even simpler

DELIMITER $$CREATE PROCEDURE `sp_fail`()BEGIN    DECLARE EXIT HANDLER FOR SQLEXCEPTION    BEGIN        ROLLBACK;  -- rollback any error in the transaction    END;    START TRANSACTION;    insert into myTable values1 ...    insert into myTable values2 ...  -- Fails    COMMIT; -- this will not be executedEND$$DELIMITER ;