Transaction in MySQL Stored Procedure

  • Avinash
  • 1
  • Jun 13, 2012
  • Database

Here I come with the second last article in the MySQL Stored Procedure Article Series. In this article we will learn about Transactions in MySQL Stored Procedure. But first let’s have a flashback of the articles now.

Flashback

Till now we have gone through with Introduction to MySQL Stored Procedure, Parameters in MySQL Stored Procedure, Conditional Controls in MySQL Stored Procedure, Different Types of Loops in MySQL Stored Procedure and now its turns for Transactions in MySQL Stored Procedure. So Let’s start with that.

Transaction in MySQL

Transactions are very useful part while dealing with large application when you are dealing with multiple tables. It becomes helpful to keep your data consistence. There are three main parts of Transaction in any database.

  1. START TRANSACTION
  2. ROLLBACK
  3. COMMIT

For having transaction in PDO I would suggest you to read this article: Transaction in PDO

START TRANSACTION will start the transaction and set the autocommit mode to off. ROLLBACK will revert any changes made to database after transaction started. COMMIT will make all changes made to database permenant after transaction started and set autocommit mode to true. So now we will see how we can have transaction in MySQL Stored Procedure.

Transaction in MySQL Stored Procedure

START TRANSACTION;” is used to start the transaction and “COMMIT;” is used to commit any changes made after starting the transaction. Have a look at sample stored procedure for the same.

DELIMITER $$ CREATE PROCEDURE `transaction_sp` () LANGUAGE SQL DETERMINISTIC SQL SECURITY DEFINER COMMENT 'First SP at Expertdeveloper' BEGIN START TRANSACTION; INSERT INTO table_name (id, name, address) values ('1','Avinash','xpertdeveloper.com'); UPDATE second_table set name="xyz" where id=4; COMMIT; END $$
DELIMITER $$

CREATE PROCEDURE `transaction_sp` ()
LANGUAGE SQL
DETERMINISTIC
SQL SECURITY DEFINER
COMMENT 'First SP at Expertdeveloper'
BEGIN
START TRANSACTION;
  INSERT INTO table_name (id, name, address) values ('1','Avinash','xpertdeveloper.com');
  UPDATE second_table set name="xyz" where id=4;
COMMIT;
END
$$

In above stored procedure you can see that I have mentioned START TRANSACTION; and COMMIT;, but what if my first query execute properly and second qurey generate any error. It will still commit the changes of the first query. So where ROLLBACK stands? Have a look at below section for the same:

ROLLBACK in MySQL Stored Procedure

To perform the ROLLBACK in MySQL Stored Procedure, we must have to declare exit handler in stored procedure. There are two types of handler we can have in MySQL Stored Procedure.

  1. sqlexception
  2. sqlwarning

sqlexception will execute when there is any error occurs during the query execution and sqlwarning will execute when any warning occurs in MySQL Stored Procedure. Let’s see how we can have those block in Stored Procedure.

DELIMITER $$ CREATE PROCEDURE `transaction_sp` () LANGUAGE SQL DETERMINISTIC SQL SECURITY DEFINER COMMENT 'First SP at Expertdeveloper' BEGIN DECLARE exit handler for sqlexception BEGIN -- ERROR ROLLBACK; END; DECLARE exit handler for sqlwarning BEGIN -- WARNING ROLLBACK; END; START TRANSACTION; INSERT INTO table_name (id, name, address) values ('1','Avinash','xpertdeveloper.com'); UPDATE second_table set name="xyz" where id=4; COMMIT; END $$
DELIMITER $$

CREATE PROCEDURE `transaction_sp` ()
LANGUAGE SQL
DETERMINISTIC
SQL SECURITY DEFINER
COMMENT 'First SP at Expertdeveloper'
BEGIN

DECLARE exit handler for sqlexception
  BEGIN
    -- ERROR
  ROLLBACK;
END;
 
DECLARE exit handler for sqlwarning
 BEGIN
    -- WARNING
 ROLLBACK;
END;

START TRANSACTION;
  INSERT INTO table_name (id, name, address) values ('1','Avinash','xpertdeveloper.com');
  UPDATE second_table set name="xyz" where id=4;
COMMIT;
END
$$

Conclusion

So here we are done with the Transactions in MySQL Stored Procedure. Main thing to keep in mind is to set the exithandlers for ROLLBACK. If you do not declare exithandlers then there is no use of starting transaction.

Related Posts

Written by Avinash

Avinash Zala is leading various projects which deals with the various technology involved with the web. A combination of perfect technical and management skills. Avinash would like to chat with you and convert your imagination into the working system. You can get in touch with him on Facebook and Twitter.

View all posts by:

  • Yuriy Lisenko

    Thanks its helped me.