How to rollback all statements inside a MySQL Transaction?

Question

I need to update a specific column of a table (bigtable) containing ids of another table (FK constraint to oldsmalltable) to point to ids on another table (FK constraint to newsmalltable). Basically this is what I am doing:

DELIMITER //

CREATE PROCEDURE updatebigtable ()
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING ROLLBACK;

    START TRANSACTION;
        ALTER TABLE bigtable DROP FOREIGN KEY bigtable_ibfk_1,
            MODIFY smalltable_id SMALLINT ;
        UPDATE bigtable SET smalltable_id=CASE smalltable_id
                WHEN 1 THEN 1592
                WHEN 2 THEN 1593
                WHEN 3 THEN 1602
                ...
                ELSE 0
            END;
        ALTER TABLE bigtable ADD CONSTRAINT bigtable_ibfk_1
            FOREIGN KEY(smalltable_id) REFERENCES newsmalltable(id);
    COMMIT;
END//
DELIMITER ;

CALL updatebigtable();
DROP PROCEDURE updatebigtable;

I need to ensure that if by some reason the new Foreign Key constraint fails (e.g. with columns with different types, the error would occur on the last alter table statement), the UPDATE and the first ALTER TABLE should be rolled back as well, i.e. they should remain as they were initially.

According to MySQL documentation, by using START TRANSACTION the autocommit mode is disabled for that transaction, which will not allow:

that as soon as you execute a statement that updates (modifies) a table, MySQL stores the update on disk to make it permanent.

I only found this question as minimally related to mine:

How can I use transactions in my MySQL stored procedure?

If that error I mentioned occurs inside the transaction, the previous statements were already executed and the updates were "permanently done on disk"...

I also tried to place SET autocommit=0; before creating the procedure but the behavior is still the same... Am I missing something? Or is this the expected behavior of a MySQL transaction rollback?

If it makes any difference, I am using MySQL v.5.6.17.

Check: [13.3.3 Statements That Cause an Implicit Commit](https://dev.mysql.com/doc/refman/5.6/en/implicit-commit.html). — wchiquito, Dec 01 '15 at 12:47

score 6 · Accepted Answer · edited May 23 '17 at 11:54

ALTER TABLE statements always cause an implicit commit (section 13.3.3 from MySQL docs, thanks wchiquito), which means that even if they're inside a START TRANSACTION; ... COMMIT; block, there will be as many commits as the number of alters done inside that block.

Locking the table is not an option as well since (from problems with ALTER TABLE):

If you use ALTER TABLE on a transactional table or if you are using Windows, ALTER TABLE unlocks the table if you had done a LOCK TABLE on it. This is done because InnoDB and these operating systems cannot drop a table that is in use.

The only option left for avoiding unwanted reads/writes while the alter and update statements are being executed is emulating all the steps of an ALTER TABLE:

Create a new table named A-xxx with the requested structural changes.

Copy all rows from the original table to A-xxx.

Rename the original table to B-xxx.

Rename A-xxx to your original table name.

Delete B-xxx.

This way the updates can be done in the new table (after step 2) and the only time the bigtable is unavailable is while doing step 3 and 4 (renaming).

score -2 · Answer 2 · answered Dec 01 '15 at 15:05

-2

Use a TRY CATCH block BEGIN TRAN before BEGIN TRY and ROLLBACK TRAN inside CATCH block

answered Dec 01 '15 at 15:05

Piotr Sowiak

89
11

[try-catch statement](http://common-schema.googlecode.com/svn/trunk/common_schema/doc/html/query_script_try_catch.html) -> [common-schema](http://common-schema.googlecode.com/svn/trunk/common_schema/doc/html/introduction.html)?. – wchiquito Dec 01 '15 at 16:43
MySQL, not MS SQL. – Marc L. Jan 17 '17 at 15:50

How to rollback all statements inside a MySQL Transaction?

2 Answers2

Linked