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

Have you considered using Handlers? [13.6.7.2. DECLARE ... HANDLER Syntax](http://dev.mysql.com/doc/refman/5.6/en/declare-handler.html) — wchiquito, Nov 11 '13 at 13:07

score 69 · Accepted Answer · answered Nov 11 '13 at 13:59

69

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.

answered Nov 11 '13 at 13:59

wchiquito

16,177
2
34
45

my question is, Is this stored procedure is going to be persisted in database forever?. – MDaniyal Sep 01 '16 at 07:06
1

@MDaniyal Short answer: Yes. – RnMss Oct 18 '16 at 11:51
3

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. – MDaniyal Oct 18 '16 at 19:20
If the exception occurs in the 1st `INSERT`, wouldn't MySQL do the 2nd and 3rd ones, leading sometimes to unexpected results? – Xenos Apr 25 '17 at 12:23
3

@Xenos 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. – cdhowie Jun 02 '18 at 15:35
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;` – Malus Jan Jul 16 '18 at 19:30
@MalusJan: Can you give more information?. In the [dbfiddle](https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=fb6202f09a82a9f037716f28afa7df2f) everything works as expected. – wchiquito Jul 17 '18 at 04:36
@wchiquito 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'). – Malus Jan Jul 17 '18 at 13:52
@wchiquito I found the problem. The table engine should be InnoDB – Malus Jan Jul 17 '18 at 18:58
@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) ? – joedotnot Jan 24 '20 at 19:48
Why using CONTINUE HANDLER for exception ? Why not EXIT HANDLER ? – Prateek Pande May 24 '20 at 14:14
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. – aSystemOverload May 25 '21 at 19:42
How to do the same without persisting the `stored procedure` in DB ? without even using SP, is it possible ? – Enissay Jun 17 '21 at 15:43
Simplest and best answer – MrrMan Feb 09 '23 at 09:29

score 32 · Answer 2 · answered Aug 25 '16 at 20:32

32

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 ;

answered Aug 25 '16 at 20:32

KGs

913
11
19

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? – Curtis Apr 23 '19 at 16:18
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: https://gist.github.com/0x49D1/bbc73d9d59aafd16ced01e145c3b34b0 – 0x49D1 Aug 02 '19 at 07:48

score 8 · Answer 3 · answered May 17 '19 at 07:42

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 ;

mysql transaction - roll back on any exception

3 Answers3

Linked