Транзакция mysql - откат на любом исключении

Можно ли автоматически откат, если какая-либо ошибка возникает в списке команд mysql?

например, что-то вроде строк:

begin transaction;

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

commit;

теперь, при выполнении я хочу, чтобы вся транзакция завершилась неудачей, и поэтому я должен НЕ видеть значения1 в myTable. но, к сожалению, таблица купируется значениями1, хотя транзакция имеет ошибки.

какие-нибудь идеи, как я заставляю его откатываться? (опять же, при любой ошибке)?

EDIT - изменено с DDL на стандартный SQL

Ответ 1

Вы можете использовать 13.6.7.2. DECLARE... HANDLER Синтаксис следующим образом:

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 ;

В качестве полного примера проверьте следующий SQL Fiddle.

Ответ 2

Вы можете использовать EXIT HANDLER, если вам, например, нужно СИГНАЛ указать конкретный SQL EXCEPTION в вашем коде. Например:

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 ;

Ответ 3

Вышеупомянутое решение хорошо, но сделать его еще проще

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

DELIMITER ;