mysql update if 事务_如何回滚MySQL事务中的所有语句？

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:

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.

解决方案

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).