Search code examples
mysqltriggersatomic

MySql - Is tables name swap is an atomic operation

I have a table, let's call it myTbl. I also have two tables - Monitor and Monitor_bkp. now i built a Trigger to create a new row in Monitor whenever the is an update in myTbl:

CREATE TRIGGER mon_task_after_update_task 
AFTER UPDATE ON myTbl
FOR EACH ROW INSERT INTO Monitor VALUES(new.id, SYSDATE())
    ON DUPLICATE KEY UPDATE last_modified=SYSDATE();

all works good, and i have a lot of updates to myTbl at all time. now my question is if i'll swap the names of Monitor and Monitor_bkp, using

RENAME TABLE foo TO foo_old, foo_new To foo;

Is there a possibility the trigger will fire while the swap is going and if so will it fail?

Solution

  • According to the manual (and my experience using this feature for log rotation) the operation is atomic and the threads that are trying to use the table have to wait for RENAME TABLE to complete.

    This also means that RENAME TABLE will wait for all transactions which touched the tables to finish, and while waiting it will lock every subsequent transaction which tries to touch the tables.

    Basically everything will freeze for the duration of the longest transaction during the swap, but will be consistent.