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.
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.
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.
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.
“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.
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:
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.
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.
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.
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: Avinash