3

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?

Improve this question
3
  • 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ᵀᴹ
    Commented May 10, 2013 at 23:33
  • I was hoping for something faster, but that may be my best bet
    – Michael La Voie
    Commented May 10, 2013 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.
    – user1822
    Commented May 11, 2013 at 9:14

1 Answer 1

Reset to default
6

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

Improve this answer
2
  • Thanks @Nathan Jolly. Although the docs say you can only due it once: "no swapsies backsies" - MySQL docs
    – Michael La Voie
    Commented May 13, 2013 at 16:48
  • Is there an equivalent in SQL Server?
    – Triynko
    Commented Oct 1, 2019 at 19:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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