MySQL: Truncate Table within Transaction?
http://dev.mysql.com/doc/refman/5.1/en/truncate-table.html
According to this URL, as of MySQL 5.1.32, TRUNCATE TABLE
is DDL and NOT DML like DELETE. This means that TRUNCATE TABLE
will cause an implicit COMMIT
in the middle of a transaction block. So, use DELETE FROM
on a table you need to empty instead of TRUNCATE TABLE
.
Even DELETE FROM tblname;
can be rolled back. It could take a while to rollback, so make sure InnoDB is properly tuned to handle the transaction time for such rollback possibilities.
A better way to accomplish this might be to insert the data into a new table, and then use rename on both tables in order to swap them. A single rename is all that's needed for the swap, and this is an atomic action, which means the users won't even be able to detect that it happened, except for the new data showing up. You can then truncate/delete the old data.