如何在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.

我正在尝试修改我的MySQL存储过程并使其成为事务性的。现有的存储过程工作正常，没有问题，但只要我进行事务处理，它甚至不允许我保存我的更改。我检查了MySQL文档并在线搜索，但我发现我的代码没有任何问题。它似乎非常直接，但无法弄明白。

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 ?

任何想法 ？

3 个解决方案

#1

Transaction in MySQL Stored Procedure

To perform the ROLLBACK in MySQL Stored Procedure, we must have to declare exit handler in stored procedure. There are two types of handler we can have in MySQL Stored Procedure.

1. sqlexception
2. 的SQLException
3. sqlwarning
4. 一个SQLWarning

sqlexception will execute when there is any error occurs during the query execution and sqlwarning will execute when any warning occurs in MySQL Stored Procedure. Let’s see how we can have those block in Stored Procedure.

sqlexception将在查询执行期间发生任何错误时执行，并且当MySQL存储过程中发生任何警告时将执行sqlwarning。让我们看看我们如何在存储过程中拥有这些块。

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
$$

#2

Two syntax errors:

两个语法错误：

• You need commas in between the conditions for your exit handler. Notice the syntax documentation shows commas.

  您需要在退出处理程序的条件之间使用逗号。请注意语法文档显示逗号。

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

  您需要使用分号终止退出处理程序的END。 DECLARE语句本身（包括其BEGIN ... END块）是一个与任何其他语句一样的语句，需要有一个终止符。

So you need this:

所以你需要这个：

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

#3

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:-

试试这样，即在START TRANSACTION中包含你的Declare语句;之前您的ROLLBACK不是TRANSACTION的一部分，因为您在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