How can we perform ROLLBACK transactions inside a MySQL stored procedure?

MySQLMySQLi Database

As we know that ROLLBACK will revert any changes made to the database after the transaction has been started. To perform the ROLLBACK in MySQL stored procedure we must have to declare EXIT handler. We can use a handler for either sqlexception or SQL warnings. It can be understood with the help of an example in which stored procedure having ROLLBACK created for the table having the following details −

mysql> SHOW CREATE table gg\G
*************************** 1. row ***************************
       Table: gg
Create Table: CREATE TABLE `gg` (
   `Id` int(11) NOT NULL AUTO_INCREMENT,
   `Name` varchar(30) NOT NULL,
   PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

We can see that the column ‘name’ cannot have NULL values and table have the following data −

mysql> Select * from employee.tbl;
+----+---------+
| Id | Name    |
+----+---------+
|  1 | Mohan   |
|  2 | Gaurav  |
|  3 | Sohan   |
|  4 | Saurabh |
|  5 | Yash    |
|  6 | Rahul   |
+----+---------+
6 rows in set (0.00 sec)

Example

mysql> Delimiter //
mysql> Create Procedure st_transaction_Rollback()
    -> BEGIN
    -> DECLARE exit handler for sqlexception
    -> BEGIN
    -> ROLLBACK;
    -> END;
    -> DECLARE exit handler for sqlwarning
    -> BEGIN
    -> ROLLBACK;
    -> END;
    -> START TRANSACTION;
    -> INSERT INTO employee.tbl(name) values();
    -> UPDATE employee.tbl set name = 'YashPal' where id = 5;
    -> COMMIT;
    -> END //
Query OK, 0 rows affected (0.02 sec)

mysql> Delimiter ;
mysql> CALL st_transaction_Rollback ()
Query OK, 0 rows affected (0.00 sec)

mysql> Select * from employee.tbl;
+----+---------+
| Id | Name    |
+----+---------+
|  1 | Mohan   |
|  2 | Gaurav  |
|  3 | Sohan   |
|  4 | Saurabh |
|  5 | Yash    |
|  6 | Rahul   |
+----+---------+
6 rows in set (0.00 sec)

We can see from the above result set that the changes made by UPDATE statement have been ROLLBACKED because the first query of INSERT has thrown an error (trying to insert NULL values).

raja
Published on 21-Feb-2018 15:31:54
Advertisements