transaction - rollback mysql

¿Cómo puedo usar transacciones en mi procedimiento almacenado de MySQL?

Question

Estoy tratando de modificar mi procedimiento almacenado de MySQL y hacerlo transaccional. El procedimiento almacenado existente funciona bien sin problemas, pero tan pronto como lo hago transaccional, ni siquiera me permite guardar mis cambios. Comprobé la documentación de MySQL y busqué en línea, pero no encuentro ningún problema con mi código. Parece ser bastante directo pero no puede resolverlo.

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

alguna idea ?

Answer 1

Dos errores de sintaxis:

• Necesita comas entre las condiciones para su manejador de salida. Observe que la documentación de sintaxis muestra comas.

• Debe terminar el END del manejador de salida con un punto y coma. La sentencia DECLARE sí misma (incluido su bloque BEGIN ... END) es una declaración como cualquier otra, y necesita un terminador.

Entonces necesitas esto:

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

Answer 2

Pruebe de esta manera, es decir, incluya su declaración Declare dentro de START TRANSACTION; . Anteriormente, tu ROLLBACK no formaba parte de TRANSACTION como lo escribiste antes de 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

Transacción en MySQL Procedimiento almacenado

Para realizar el ROLLBACK en MySQL Stored Procedure, debemos declarar el manejador de salida en el procedimiento almacenado. Hay dos tipos de controladores que podemos tener en MySQL Stored Procedure.

1. sqlexception
2. sqlwarning

sqlexception se ejecutará cuando se produzca algún error durante la ejecución de la consulta y sqlwarning se ejecutará cuando se produzca una advertencia en MySQL Stored Procedure. Veamos cómo podemos tener esos bloqueos en el Procedimiento almacenado.

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