Как я могу использовать транзакции в моей хранимой процедуре MySQL?

Я пытаюсь изменить мою хранимую процедуру 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

любая идея?

Ответ 1

Транзакция в хранимой процедуре MySQL

Чтобы выполнить ROLLBACK в хранимой процедуре MySQL, нам нужно объявить обработчик exit в хранимой процедуре. В хранимой процедуре MySQL есть два типа обработчиков.

SQLException
sqlwarning

sqlexception будет выполнять при возникновении ошибки во время выполнения запроса, а sqlwarning будет выполнять, когда в MySQL хранится процедура предупреждения. Давайте посмотрим, как мы можем иметь этот блок в Хранимой процедуре.

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

Попробуйте это, например, включите оператор Declare внутри START TRANSACTION;. Раньше ваш 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

Ответ 3

Две синтаксические ошибки:

Вам нужны запятые между условиями для вашего обработчика выхода. Обратите внимание: синтаксическая документация показывает запятые.
Вам нужно завершить END обработчика выхода точкой с запятой. Сам оператор DECLARE (включая блок BEGIN... END) является оператором, подобным любому другому, и должен иметь терминатор.

Итак, вам это нужно:

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

Ответ 4

Поставьте свой DECLARE после первого BEGIN и он должен работать.