Suppose, I have a table foo, which contains some statistics that are computed every now and then. It is heavily used by other queries.

That's why I want to compute more recent statistics in foo_new and swap them when computation is ready.

I could do


but what happens if a query needs table foo inbetween those two lines when there is no table foo? I guess I have to lock it somehow... or is there another way to do it?

1 Answer 1


Use this one command:

RENAME TABLE foo TO foo_old, foo_new To foo;

It is an atomic operation: both tables are locked together (and for a very short time), so any access occurs either before or after the RENAME.

  • 4
  • 7
    One problem with this is that, If I have tableX that has got parent constraint referencing foo. After this RENAME it will be now referencing foo_old but if there are no constraints referencing foo then you should be OK... Commented Jan 17, 2017 at 11:23
  • 1
    @MarcinWasiluk - yet another drawback to FOREIGN KEYs.
    – Rick James
    Commented Apr 4, 2017 at 20:32
  • 5
    Also worth being aware that RENAME TABLE has to wait for existing queries on the table to finish until it can be executed. That would be fine, but it also locks out other queries while waiting for RENAME to happen! This can cause a serious locking up of your database tables (it has for us). This applies to innodb!
    – John Hunt
    Commented May 15, 2018 at 10:17

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.