mysql存储过程中事务,如何在MySQL存储过程中使用事务？

I'm trying to modify my MySQL stored procedure and make it transactional. The existing stored procedure works fine with no problem but as soon as I make it transactional it does not even allow me to save my changes. I checked MySQL documentation and searched online but I cannot find any problem with my code. It seems to be pretty straight forward but can't figure it out.

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 ?

解决方案

Try like this ie, include your Declare statement inside the START TRANSACTION;. Earlier your ROLLBACK was not a part of TRANSACTION as you wrote it above the START TRANSACTION:-

BEGIN

DECLARE poid INT;

DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING

START TRANSACTION;

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