Transaction with Stored Procedure in MySQL Server

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.

Thanks
A Rahim Khan

About these ads
  1. interesting…

  2. thank! very useful post!

    • Marian
    • October 6th, 2011

    Good job ! Thanx a lot from Slovakia

    • Nalin Bakshi
    • December 30th, 2011

    Thanks for lot for putting such a straight forward and detailed example.

    Nicely written.

    • fidalgo
    • February 10th, 2012

    thank you very much for the guide, from portugal :)

    • Carlos
    • May 17th, 2012

    Thanks !!, I spent a lot of time finding this king of information … from Peru..

    • XavierAviles
    • June 3rd, 2012

    Men thanks
    Y work in a service Site with Php & Mysql (INNOBD) Transaccion Flow

    God Day from my home: the earth. one Place one Nation

    • Alejandrolll
    • October 30th, 2012

    thanks, i had been searching several days this solution.

    • Amith K Bharathan
    • January 8th, 2013

    Thank you Rahim. its really useful shall i ask somthing This transaction and rollback is necessary only if we have one or more updation. Otherwise just BEGIN .. END is enough

  3. Great work.Thanks man.

  4. Thank you a lot from Argentina

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 73 other followers

%d bloggers like this: