Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a MySQL table that holds a few thousand records which go out of date very quickly. A few times a day we run a slow process which needs to pull data from multiple sources and re-populate the table.

The catch is the table needs to be available all the time. My initial idea was to do the slow population process on a temp table, then drop the live table and rename the temp table to replace the live table.

Is it possible to lock the live table before the drop/rename process so that queries will be delayed and not lost?

Or is this the wrong approach?

share|improve this question
    
Why not just do an insert followed by a delete in the same transaction? Or is the MySQL isolation level too crap to cope with something like that? –  Phil May 10 '13 at 23:33
    
I was hoping for something faster, but that may be my best bet –  Michael La Voie May 10 '13 at 23:43
2  
"a few thousand" rows is nothing. Just do the insert/delete in a single transaction and everything should be fine. If inserting a few thousand rows is slow your problem is somewhere else. That shouldn't take longer than a couple of seconds. –  a_horse_with_no_name May 11 '13 at 9:14

1 Answer 1

up vote 2 down vote accepted

You can do swapsies by specifying both tables in your RENAME statement.

CREATE TABLE new_table (...);
RENAME TABLE old_table TO backup_table, new_table TO old_table;

MySQL documentation on RENAME TABLE says:

The rename operation is done atomically, which means that no other session can access any of the tables while the rename is running. For example, if you have an existing table old_table, you can create another table new_table that has the same structure but is empty, and then replace the existing table with the empty one...

share|improve this answer
    
Thanks @Nathan Jolly. Although the docs say you can only due it once: "no swapsies backsies" - MySQL docs –  Michael La Voie May 13 '13 at 16:48

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.