CODEWITHSUNDEEP
mysql
Zhe
Zhe

Reputation: 1070

mysql rollback by error HANDLER not working

I just build a stored procedure to test transaction in mysql.

But somehow it's not working.

Here is my code:

USE `test`;
DROP procedure IF EXISTS `testTran`;
DELIMITER $$
USE `test`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `testTran`()
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION rollback;
    START TRANSACTION;
    INSERT INTO `test`.`books`(`name`,`number`) VALUES ('asd', 20);
    ALTER TABLE `test`.`books` ADD COLUMN `name` VARCHAR(45) NULL  AFTER `number` ;
    COMMIT;
END$$
DELIMITER ;

Inside transaction, the second command is create an exist column which will be fail. What I expect is the first insert will be rollback. But actually, it's not. The insert do work.

Can anyone help?

Upvotes: 1

Views: 1890

Answers (2)

Ralf
Ralf

Reputation: 29

The ALTER TABLE statement causes an implicit COMMIT before it is executed.

Upvotes: 0

walterquez
walterquez

Reputation: 359

In order to use transactions you need to convert the tables to a transaction table like InnoDB.

Optionally, you can rollback changes made by your stored procedure using SAVEPOINT.

DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK TO identifier;

SAVEPOINT identifier;

START TRANSACTION;

INSERT INTO `test`.`books`(`name`,`number`) VALUES ('asd', 20);
ALTER TABLE `test`.`books` ADD COLUMN `name` VARCHAR(45) NULL  AFTER `number` ;

COMMIT;

Upvotes: 1

Related Questions