Solving Transactional Issues in MySQL Stored Procedures

When attempting to implement transactions within a MySQL stored procedure, developers may encounter unexpected errors. This article will explore the syntax and logical errors that can prevent the successful execution of transactional stored procedures.

The Case: An Unsuccessful Transaction

A developer encounters an issue when attempting to implement transactions in a stored procedure. Despite following the MySQL documentation, the procedure fails to execute successfully and prompts an inability to save changes. Upon further inspection, the code appears to be syntactically correct.

Analyzing the Code

The provided code snippet is as follows:

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

Identifying the Errors

Upon close examination, two syntax errors are identified:

1. Missing Commas in Exit Handler:

   • In the EXIT HANDLER declaration, there should be commas separating the conditions.

2. Missing Semicolon:

   • The END statement of the EXIT HANDLER is missing a terminating semicolon.

The Corrected Code

The corrected code is as follows:

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;