MySQL Transaction with Stored Procedure

MySQL Transaction with Stored Procedure 

Stored Procedure in MySQL Server is not atomic by default. To ensure atomicity, you need to write all your SQL Statements within START TRANSACTION .. COMMIIT Block. Moreover, you need to declare EXIT HANDLER for SQLEXCEPTION and SQLWARNINGS to ROLLBACK all of the SQL Statements in START TRANSACTION .. COMMIIT Block.

DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
DECLARE EXIT HANDLER FOR SQLWARNING ROLLBACK;

You need to keep in mind those two important issues while using Transaction and Exit Handler in MySQL Server.

# Never declare any Variable within START TRANSACTION .. COMMIIT Block.

# Exit Handler Declaration should be your last Declaration just ahead of START TRANSACTION .. COMMIIT Block.

Let's do some coding. In the following code snippet, I write "sp_cheque_leaves_posting" Procedure that accepts two Parameters. Second one is OUT Parameter that returns 1 on succeess of this Stored Procedure Routine.

CREATE PROCEDURE sp_cheque_leaves_posting(IN param_requisition_id INT, OUT param_sp_success TINYINT)

BEGIN

DECLARE details_id_loop INT DEFAULT 0;
DECLARE starting_serial_no INT;
DECLARE record_not_found INT DEFAULT 0;
DECLARE cursor_details_id CURSOR FOR SELECT details_id FROM khan_trial.table_requisition_details WHERE requisition_master_id = param_requisition_id;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET record_not_found = 1;

DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
DECLARE EXIT HANDLER FOR SQLWARNING ROLLBACK;

START TRANSACTION;

SET param_sp_success = 0;

SELECT MAX(ending_serial_no) INTO starting_serial_no FROM table_cheque_leaves_posting;

IF starting_serial_no IS NULL THEN
 SET starting_serial_no = 1;
ELSE
 SET starting_serial_no = starting_serial_no + 1;
END IF;

OPEN cursor_details_id;

all_details_id:LOOP

 FETCH cursor_details_id INTO details_id_loop;
 
 IF record_not_found THEN 
  LEAVE all_details_id;
 END IF;
 
 INSERT INTO table_cheque_leaves_posting 
 VALUES(details_id_loop,starting_serial_no,starting_serial_no+9,12,12,'A Rahim Khan');
 
 SET starting_serial_no = starting_serial_no + 10;

END LOOP all_details_id;

CLOSE cursor_details_id;

UPDATE table_requisition_master SET cheque_leaves_posting = 1
WHERE requisition_id = param_requisition_id;

SET param_sp_success = 1;

COMMIT;

END

Download the SQL Script. "khan_trial" Database has three tables i.e. “table_cheque_leaves_posting”, “table_requisition_details”, “table_requisition_master”. “table_cheque_leaves_posting” does not allow duplicate “details_id”. Call the "sp_cheque_leaves_posting" routine in the following way.

CALL sp_cheque_leaves_posting(1,@a);
SELECT  @a;

OUtput:
@a
--------
0

That is the Stored Procedure has not completed successfully. Inserting duplicate value for “details_id” Field in “table_cheque_leaves_posting” has resulted in SQLEXCEPTION and SQL Statements within the START TRANSACTION .. COMMIIT Block has not commited as well.