Transaction in MySQL Stored Procedure

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

Flashback

[gads]

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

[gads]

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.

[cc lang=”mysql”]
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
$$
[/cc]

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.

[gads]

[cc lang=”mysql”]
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
$$
[/cc]

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.