68

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

ALTER TABLE foo RENAME foo_tmp;
ALTER TABLE foo_new RENAME foo;

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?

Ben
  • 877
  • 1
  • 7
  • 9

1 Answers1

115

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.

Shlomi Noach
  • 7,363
  • 1
  • 24
  • 24
  • 4
    http://dev.mysql.com/doc/refman/5.5/en/rename-table.html – miracle173 Aug 08 '12 at 22:45
  • 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... – Marcin Wasiluk Jan 17 '17 at 11:23
  • 1
    @MarcinWasiluk - yet another drawback to FOREIGN KEYs. – Rick James Apr 04 '17 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 May 15 '18 at 10:17