CODEWITHSUNDEEP
mysqltransactionsrollback
mo.meisam
mo.meisam

Reputation: 43

How to roll back in Mysql?

I want to execute multiple command to update a customer database, but I want this command to execute in a transaction, and when an error occurs in one command all changes would be rolled backed.

When I run the code in this example, if test2 table has existed the rollback has not worked and the inserted row exists in test table.

What am I doing wrong?

MySQL server is 5.1. the engine of tables is Innodb.

code example:

set autocommit = 0;
drop procedure if EXISTS rollbacktest;
delimiter //

CREATE PROCEDURE rollbacktest ()
   BEGIN

   DECLARE CONTINUE HANDLER FOR SQLEXCEPTION,SQLWARNING  SET @x2 = 4; 
   SET autocommit = 0;
   start transaction;   

    SET @x2 = 0;

insert into test(t)values (800);

CREATE TABLE `test2` (
`t`  int(11) UNSIGNED NOT NULL 
    )
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_persian_ci ;


if @x2 = 4 THEN    ROLLBACK; else  commit;    end if;

   END;
//

CALL rollbacktest()

Upvotes: 0

Views: 3894

Answers (2)

Romain
Romain

Reputation: 12839

Your problem is that you're doing DDL (CREATE TABLE), which cannot be done in a transaction, so it'll implicitly commit the stuff you've done before.

This will also be the case if you tro to do DROP TABLE, ALTER TABLE, or TRUNCATE TABLE, among other things. Essentially any statement that cannot be rolled back will cause existing transactions to be auto-COMMITed

Upvotes: 1

Jim H.
Jim H.

Reputation: 5579

If I remember correctly, the CREATE TABLE implicitly commits the transaction.

http://dev.mysql.com/doc/refman/5.1/en/implicit-commit.html

Upvotes: 0

Related Questions