mysql transaction - roll back on any exception
67,173
Solution 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.
Solution 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 ;
Solution 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 ;
Related videos on Youtube
Author by
Urbanleg
Updated on August 01, 2020Comments
-
Urbanleg almost 4 years
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
-
Alma Do over 10 yearsThis is pointless since transactions in MySQL do not support DDL
-
Urbanleg over 10 yearsthanks for the comment, i edited my original post
-
wchiquito over 10 yearsHave you considered using Handlers? 13.6.7.2. DECLARE ... HANDLER Syntax
-
-
MDaniyal over 7 yearsmy question is, Is this stored procedure is going to be persisted in database forever?.
-
RnMss over 7 years@MDaniyal Short answer: Yes.
-
MDaniyal over 7 yearsThanks 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.
-
Xenos about 7 yearsIf the exception occurs in the 1st
INSERT
, wouldn't MySQL do the 2nd and 3rd ones, leading sometimes to unexpected results? -
cdhowie almost 6 years@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 executeROLLBACK;
instead ofCOMMIT;
. 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 (exceptROLLBACK TO
) and will implicitly rollback on commit. -
Malus Jan almost 6 yearsI 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;
-
wchiquito almost 6 years@MalusJan: Can you give more information?. In the dbfiddle everything works as expected.
-
Malus Jan almost 6 years@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 almost 6 years@wchiquito I found the problem. The table engine should be InnoDB
-
Curtis about 5 yearsIm 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?
-
0x49D1 almost 5 yearsIn 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
-
joedotnot over 4 years@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) ?
-
Prateek Pande almost 4 yearsWhy using CONTINUE HANDLER for exception ? Why not EXIT HANDLER ?
-
aSystemOverload almost 3 yearsThis didn't work for me, it raised an error and issued a ROLLBACK, but for some reason didn't actually roll the transaction back.
-
Enissay almost 3 yearsHow to do the same without persisting the
stored procedure
in DB ? without even using SP, is it possible ?