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?