MySQL: Truncate Table within Transaction?

Question

I have an InnoDB table that needs to be re-populated every ten minutes within anywhere from 60k to 200k records. Our approach up to this point has been as follows:

1. Turn off Autocommit
2. Truncate the table
3. Perform Select Queries & additional Calculations (using PHP)
4. Insert new records
5. Commit

After the Truncate operation is performed though, the data is immediately deleted, and is no longer available from the User Interface. To our users, this has been pretty disconcerting, even though within about 30 seconds or so the script encounters the Commit operation and the table is repopulated.

I thought that perhaps I could wrap the whole operation, including the Truncate, in a transaction, and that this might cut down on the length of time during which the table appears empty to users. So I changed SET AUTOCOMMIT=0 to START TRANSCATION.

Yikes! This had the opposite of the desired effect! Now the TRUNCATE operation still occurs at the beginning of the script, but it takes much longer to actually execute the INSERT operations within the transaction, so that by the time the COMMIT operation takes place and the data in the table is available again, it has been nearly ten minutes!

What could possibly cause this? Truthfully, I wasn't expecting any change at all, because I was under the impression that initiating a transaction basically just turns off Autocommit anyway??

Taylor

Answer 1

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.

Beechnut

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. :) – Taylor

It's been nearly a year, just wanted to follow up and mention that this is what we ended up doing, and it works like a charm. We create the new table, insert a bucketload of records, and swap them out as soon as the update is complete -- to the end users it appears instant. Thanks again. – Taylor

Thanks for this. Been wrestling with the best way to do a bulk data update without needing to take the DB down for a few seconds. This is a great workaround. – Mortonmortuary

You may as well DROP TABLE on the old one after the rename. – Debug

We've used this approach in production for a while, using an atomic RENAME table TO table_old, table_new TO table and it worked perfectly. I had the same "this is hackish" feeling at first, but I could not see any downside to this approach. – Grenadier

Won't this affect the statistics of the table and any indexes? Do you need to run ANALYZE on it after? – Ecumenicist

Answer 2

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.

Interfere

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. – Taylor

@Interfere Notice that the difference between DELETE FROM table and TRUNCATE table is not in time of execution only. TRUNCATE resets also AUTO_INCREMENT value to 1, however DELETE does not. – Iasis

"so make sure InnoDB is properly tuned to handle the transaction time for such rollback possibilities." Any insight on how to do this? – Indemonstrable

It's interesting to note that even a truncate operation causing an error (e.g. "truncate customers" if the table customers doesn't exist) could cause an implicit commit. – Partridgeberry

Answer 3

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.

Bombay

Answer 4

TRUNCATE implies COMMIT so that transaction is not an ACID transaction. This is noted above.

--

I use your approach in MySQL to emulate an OUTER JOIN of two tables by email address. The result stays in a table I can quickly INNER JOIN later.

Your approach is already having out-of-date data (since you require DELETE). So here is another approach which also uses out-of-date data, but foregoes transactions entirely. Less locking FTW.

Just INSERT... ON DUPLICATE KEY UPDATE and mark an "update time". At end of your script, DELETE anything with an old "update time".

Drucilla