How can I use transactions in my MySQL stored procedure?

Question

I am trying to modify my MySQL stored procedure and make it transactional. The existing stored procedure works fine, no problem, but as soon as I make the transaction, it doesn’t even allow me to save the changes. I checked the MySQL documentation and searched the Internet, but I can not find any problems with my code. It seems pretty straightforward, but it cannot understand.

BEGIN DECLARE poid INT; DECLARE EXIT HANDLER FOR SQLEXCEPTION SQLWARNING BEGIN ROLLBACK; END START TRANSACTION; -- ADD option 5 INSERT INTO product_option(product_id,option_id,required) VALUES(insertedProductID,5,0); SET poid = (SELECT LAST_INSERT_ID()); INSERT INTO product_option_value(product_option_id,product_id,option_id,option_value_id,quantity,subtract,price,price_prefix,points,points_prefix,weight,weight_prefix) VALUES(poid,insertedProductID,5,50,0,0,4.99,'+',0,'+',0,'+'); -- ADD option 12 INSERT INTO product_option(product_id,option_id,required) VALUES(insertedProductID,12,1); -- ADD option 13 INSERT INTO product_option(product_id,option_id,required) VALUES(insertedProductID,13,0); COMMIT; END 

any idea?

Answer 1

Transaction in MySQL stored procedure

To execute a ROLLBACK in a MySQL stored procedure, we need to declare an exit handler in the stored procedure. There are two types of handlers in the MySQL stored procedure.

• SQLException
• sqlwarning

sqlexception will execute when an error occurs during query execution, and sqlwarning will execute when the warning procedure is stored in MySQL. Let's see how we can have this block in a stored procedure.

 DELIMITER $$ CREATE PROCEDURE `transaction_sp` () BEGIN DECLARE exit handler for sqlexception BEGIN -- ERROR ROLLBACK; END; DECLARE exit handler for sqlwarning BEGIN -- WARNING ROLLBACK; END; START TRANSACTION; -- ADD option 5 INSERT INTO product_option(product_id,option_id,required) VALUES(insertedProductID,5,0); SET poid = (SELECT LAST_INSERT_ID()); INSERT INTO product_option_value(product_option_id,product_id,option_id,option_value_id,quantity,subtract,price,price_prefix,points,points_prefix,weight,weight_prefix) VALUES(poid,insertedProductID,5,50,0,0,4.99,'+',0,'+',0,'+'); -- ADD option 12 INSERT INTO product_option(product_id,option_id,required) VALUES(insertedProductID,12,1); -- ADD option 13 INSERT INTO product_option(product_id,option_id,required) VALUES(insertedProductID,13,0); COMMIT; END $$ 

Answer 2

Try this, for example, include the Declare statement inside START TRANSACTION; . Your ROLLBACK not previously part of TRANSACTION , since you wrote it over START TRANSACTION : -

 BEGIN START TRANSACTION; DECLARE poid INT; DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING BEGIN ROLLBACK; END -- ADD option 5 INSERT INTO product_option(product_id,option_id,required) VALUES(insertedProductID,5,0); SET poid = (SELECT LAST_INSERT_ID()); INSERT INTO product_option_value(product_option_id,product_id,option_id,option_value_id,quantity,subtract,price,price_prefix,points,points_prefix,weight,weight_prefix) VALUES(poid,insertedProductID,5,50,0,0,4.99,'+',0,'+',0,'+'); -- ADD option 12 INSERT INTO product_option(product_id,option_id,required) VALUES(insertedProductID,12,1); -- ADD option 13 INSERT INTO product_option(product_id,option_id,required) VALUES(insertedProductID,13,0); COMMIT; END 

Answer 3

Two syntax errors:

• You need commas between conditions for your exit handler. Note: the syntax documentation shows commas.

• You need to end the END exit handler with a semicolon. The DECLARE statement itself (including the BEGIN ... END block) is an statement like any other, and must have a terminator.

So you need this:

 DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING BEGIN ROLLBACK; END; 

Answer 4

Put your DECLARE after the first BEGIN and it should work.