69

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
  • 887
  • 1
  • 7
  • 9

1 Answers1

116

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,403
  • 1
  • 25
  • 24