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

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 ?

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

Dos errores de syntax:

  • Necesita comas entre las condiciones para su manejador de salida. Observe que la documentación de syntax 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 statement como cualquier otra, y necesita un terminador.

Entonces necesitas esto:

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

Pruebe de esta manera, es decir, incluya su statement 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