CODEWITHSUNDEEP
mysqltransactionsrollback
Urbanleg
Urbanleg

Reputation: 6532

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)?

Upvotes: 54

Views: 85733

Answers (4)

Katapofatico
Katapofatico

Reputation: 772

Reading the v.8.4 current documentation about "commit", we can read

By default, MySQL runs with autocommit mode enabled. This means that, when not otherwise inside a transaction, each statement is atomic, as if it were surrounded by START TRANSACTION and COMMIT. You cannot use ROLLBACK to undo the effect; however, if an error occurs during statement execution, the statement is rolled back.

and

With START TRANSACTION, autocommit remains disabled until you end the transaction with COMMIT or ROLLBACK.

This example demonstrates that START TRANSACTION makes it contents a single atomic operation with no effects when an error is thrown:

CREATE TABLE testing (
  id int NOT NULL PRIMARY KEY
) ENGINE=InnoDB;

INSERT INTO testing (id) VALUES (1);

START TRANSACTION;
INSERT INTO testing(id) VALUES (9); 
INSERT INTO testing(id) VALUES (1); -- It fails because primary key
COMMIT;

The first INSERT was not saved:

SELECT * from testing;
+----+
| id |
+----+
|  1 |
+----+

You can see more explanations and examples in the section "autocommit, commit and rollback".

Many SOF answers redirect to the same PROCEDURE solution, and I don't understand why. Might it be a matter of MySQL versions?

Upvotes: 1

James
James

Reputation: 1829

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

DELIMITER ;

Upvotes: 8

KGs
KGs

Reputation: 923

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 ;

Upvotes: 32

wchiquito
wchiquito

Reputation: 16551

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.

Upvotes: 70

Related Questions