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

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

Linoleum

Have you considered using Handlers? 13.6.7.2. DECLARE ... HANDLER Syntax – Glenglencoe

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.

Glenglencoe

my question is, Is this stored procedure is going to be persisted in database forever?. – Sheets

@Sheets Short answer: Yes. – Monometallism

Thanks for the answer :) .Do you think it is a good idea that we save a new stored procedure for every new script? as we have 10 to 15 scripts per release so we would have hundreds of stored procedures. – Sheets

If the exception occurs in the 1st INSERT, wouldn't MySQL do the 2nd and 3rd ones, leading sometimes to unexpected results? – Flattery

@Flattery No, though time will be wasted. If either of the first two queries fails, _rollback is still set to 1 and so the function will execute ROLLBACK; instead of COMMIT;. However, the following queries will still execute (within the transaction) only to be ultimately rolled back later. It really baffles me that MySQL chose this continue-on-error behavior. Contrast this to PostgreSQL, which places the transaction in a failed state, guarantees all future queries in the transaction will fail (except ROLLBACK TO) and will implicitly rollback on commit. – Cheekpiece

I wrote it ,but not working: the second line should generate an error because ID =1 exists in DB DELIMITER $$ CREATE PROCEDURE prod() BEGIN DECLARE _rollback BOOL DEFAULT 0; DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET _rollback = 1; START TRANSACTION; INSERT INTO table1 values (2, 'B'); INSERT INTO table1 values (1, 'A'); IF _rollback THEN ROLLBACK; ELSE COMMIT; END IF; END$$ DELIMITER ; CALL prod(); DROP PROCEDURE IF EXISTS prod; – Anschauung

@MalusJan: Can you give more information?. In the dbfiddle everything works as expected. – Glenglencoe

@Glenglencoe I created a temporary table in mysql (table1) with 2 fields (id --> pk, name), I insert (1, 'Text') to database and after that I ran the above query. But it inserts (2, 'B'). – Anschauung

@Glenglencoe I found the problem. The table engine should be InnoDB – Anschauung

@anyone is this method / syntax only possible inside a stored proc ? how would you write transaction commit / rollback within a script only, to be executed by a client (say, HeidiSQL or Workbench) ? – Garret

Why using CONTINUE HANDLER for exception ? Why not EXIT HANDLER ? – Namesake

This didn't work for me, it raised an error and issued a ROLLBACK, but for some reason didn't actually roll the transaction back. – Inner

How to do the same without persisting the stored procedure in DB ? without even using SP, is it possible ? – Wassail

Simplest and best answer – Psia

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 ;

Coranto

Im using 5.2. RESIGNAL seems to have been added in 5.5. Any suggestions for how I can get the error message using 5.2? – Stupe

In case you need to log the exception: ROLLBACK will reset all the exception details, so you should set some session variables BEFORE ROLLBACK and pass them to the logging procedure AFTER ROLLBACK and before RESIGNAL for example: gist.github.com/0x49D1/bbc73d9d59aafd16ced01e145c3b34b0 – Sacramentalist

Answer 3

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 ;

Betteann

Answer 4

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?

Elgon