[SOLVED] MySQL transaction - roll back on any exception

MySQL transaction - roll back on any exception

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 populated with values1 even though the transaction has errors.

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

Solution

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.