MySQL: Truncate Table within Transaction?

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.

This is a really intriguing idea. My initial gut reaction was that it felt a bit hackish on the surface, but in fact theres a certain elegant simplicity to it and it would solve my problem perfectly. Wish I'd thought of it myself.

:) – Brian Lacy May 13 at 1:32.

dev.mysql.com/doc/refman/5.1/en/truncate... 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.

That's excellent information, Rolando; I really appreciate the clarification (and the reference)! I'd absolutely have given you the Best if Kibbee hadn't given me such a brilliantly simple solution. – Brian Lacy May 13 at 1:36.

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.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.