I have a big MyISAM table (hence not supporting transactions, but I do need MyISAM for performance reasons), that needs to have a lot of records deleted / added every day at a fixed time (by a cron), and only at this time.
To avoid downtimes caused by the locks I need to do on the table, I want to copy the table to a new one, make my mods on the new table, then rotate the tables.
The code snippet below shows my idea, for rotating a "content" table.
The problem is that in the (very small) amount of time between DROP TABLE and RENAME, a query could be done by another script on the "content" table, which does not exist anymore. This is likely to happen under a very high load.
So I would like to "lock" any thread trying to access the "content" table, even if it does not exist.
I tried with a LOCK TABLES content WRITE, but the lock is of course released as soon as the table is droped.
Is there any way to achieve this ? Some kind of locking a table name even if the table does not exist ?
CREATE TABLE content_temp (...);
INSERT INTO content_temp SELECT * FROM content;
/* ... make my mods here on the content_temp table ... */
DROP TABLE content;
ALTER TABLE content_temp RENAME content;