mysql transaction - roll back on any exception

Question

Is it possible to roll back automatically if any error occurs on a list of mysql commands?

for example something along the lines of:

begin transaction;  insert into myTable values1 ... insert into myTable values2 ...;  -- will throw an error  commit; 

now, on execute i want the whole transaction to fail, and therefore i should NOT see values1 in myTable. but unfortunately the table is being pupulated with values1 even though the transaction has errors.

any ideas how i make it to roll back? (again, on any error)?

EDIT - changed from DDL to standard SQL

Answer 1

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.

Answer 2

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 executed END$$  DELIMITER ;