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.

From your description I can't really explain your time difference. The only thing that comes to mind is that you don't actually wrap the inserts into one transaction, but loop it.

The key difference with SET AUTOCOMMIT=0 is that if it's already 0, it won't do anything, where as with START TRANSACTION you will initiate a sub transaction within the current transaction.