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

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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!