How to "swap" out production tables

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?

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

StackExchange Database Administrators Q#41963, answer score: 6