Question 229208 in Mysql Question posted in
The official MySQL documentation can be found here.

mysql doesn't rollback when error occur when using transaction

This is a pretty simple sql command:

start transaction;

insert into new_data(textvalue) values ("data1");

insert into new_data(brak) values ("data2");

commit;

so the table new_data only has a field called "textvalue". "brak" doesn’t exist. I think because I declare "start transaction" at the real beginning so when error happens due to the second insertion, the first insertion will be rollbacked. However, I checked the database, the first insertion still succeeds and table has a new roll "data1".

Can anyone explain why this happens?

Thanks

Tags:

2

Answers


  1. 0

    the first insertion is ok but the second,fails because you are trying to insert a value into a non-existent column brak,it causes an error, but since you have not specified any error handling or rollback mechanism, the transaction is not rolled back automatically, so you can add error handling and rollback statements! let me give you an example:

    START TRANSACTION;

BEGIN TRY
    INSERT INTO new_data(textvalue) VALUES ("data1");
    INSERT INTO new_data(brak) VALUES ("data2");
    COMMIT;
END TRY
BEGIN CATCH
    ROLLBACK;
END CATCH;

    as you see if the second insertion fails, the transaction will be rolled back and no changes will be made to the table!

    Login or Signup to reply.
  2. 0

    Mysql will not rollback for you, you need to run the rollback if you see the error. If you are running the script in workbranch, it you run you sql one by one, so eventually it is commit

    This example show you how a store procedure rollback for you.

    DELIMITER $$

CREATE PROCEDURE `sp_to_insert`()
BEGIN
    DECLARE `_rollback` BOOL DEFAULT 0;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET `_rollback` = 1;
    START TRANSACTION;

    INSERT INTO new_data(textvalue) VALUES ("data1");
    INSERT INTO new_data(brak) VALUES ("data2");

    IF `_rollback` THEN
        ROLLBACK;
    ELSE
        COMMIT;
    END IF;
END$$

DELIMITER ;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search