How do I correctly rollback a transaction

Question

0.00/5 (No votes)

See more:

I have a transaction that will be applied to 2 tables. Table 2 has field
'image'
that is unique. when trying to execute the following code in the first time, it is executed correctly but when trying to Re-execute it again (to show effect of rollback), a duplicate error occurred in table2 and the transaction was applied correctly to table1 and committed so the table1 committed record was not rolled back.

What I have tried:

Here is my code:

SQL

DROP PROCEDURE IF EXISTS mprocedure;
DELIMITER $$
CREATE PROCEDURE mprocedure ()
BEGIN
START TRANSACTION;
INSERT INTO `mschema`.`table1`
(`maxbudget`,
`blocked`,
`d_percentage`,
`max discount`)
VALUES
('2250',
'0',
'.9',
'.99');
if  (@@error_count = 0 ) then    
    INSERT INTO `mschema`.`table2`
     (`name`,`image`,`date`,`fKey_id`)
     values
         ('jhon','jfdd', '2018-01-01 00:00:00', LAST_INSERT_ID());
      if (@@error_count = 0 ) then 
          commit;
      else 
          rollback;
      end if;
else rollback;
end if;
commit;
END;
$$
DELIMITER ;

Posted 20-Apr-18 14:49pm

Ahmed AE

Updated 21-Apr-18 5:34am

v2

Add a Solution

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Accepted Answer · 2018-04-20T18:32:00

Solution 1

Try declaring an exit handler. Something like below. Sorry for any typos, I don't have MySql at hand at the moment

SQL

DROP PROCEDURE IF EXISTS mprocedure;
DELIMITER $$
CREATE PROCEDURE mprocedure ()
BEGIN
   DECLARE EXIT HANDLER FOR SQLEXCEPTION
   BEGIN
      ROLLBACK;
      SELECT 'An exception occurred';
   END;

   START TRANSACTION;
   INSERT INTO `mschema`.`table1`
      (`maxbudget`, `blocked`, `d_percentage`, `max discount`)
      VALUES ('2250', '0', '.9', '.99');
   INSERT INTO `mschema`.`table2`
      (`name`,`image`,`date`,`fKey_id`)
     VALUES ('jhon','jfdd', '2018-01-01 00:00:00', LAST_INSERT_ID());
   COMMIT;
END;
$$
DELIMITER ;

Posted 20-Apr-18 18:32pm

Wendelius

Comments

Ahmed AE 21-Apr-18 7:58am

this was very helpful, i have another question:
what is the difference between the EXIT and CONTINUE handler_actions?
form https://dev.mysql.com/doc/refman/8.0/en/declare-handler.html, i have found that:

CONTINUE: Execution of the current program continues.
EXIT: Execution terminates for the BEGIN ... END compound statement in which the handler is declared. This is true even if the condition occurs in an inner block.
what is the difference between them?

Wendelius 21-Apr-18 9:10am

Exit handler exists the procedure when the handler condition is met while continue handler lets the procedure continue from the next statement

Ahmed AE 21-Apr-18 9:45am

thank you, again one more question:
how long does the stored procedure last in server? i mean when i close MySQL workbench, and open it again does the procedure will be found, if yes what makes the procedure to be dropped from the server?

Wendelius 21-Apr-18 11:00am

You're welcome

Normal procedure is a permanent object in the database, just like a table. So unless you drop it, it'll remain.

Ahmed AE 21-Apr-18 11:20am

thank you very much

Wendelius 21-Apr-18 11:21am

You're most welcome!

How do I correctly rollback a transaction

1 solution

Solution 1

Add your solution here

Preview 0

How do I correctly rollback a transaction

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us