Safe table rotation in MySQL (MyISAM)

Hi all,

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 ?

Thanks,
Ben
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;

Open in new window

LVL 7
BenMorelAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

racekCommented:
CREATE TABLE content_temp SELECT * FROM content;
BEGIN TRANSACTION;
ALTER TABLE content RENAME old_content;
ALTER TABLE content_temp RENAME content;
COMMIT;

DROP TABLE old_content;
0
racekCommented:
more advanced - if you have high frequence of inserts into content.
But remeber that constructions like CREATE TABLE content_temp SELECT * ... are locking the whole table content.


CREATE TABLE content_temp SELECT * FROM content;
ADD primary key, indexes etc .....
SELECT MAX(id) last_id from content_temp;
START TRANSACTION;
INSERT INTO content_temp SELECT * FROM content WHERE id > $last_id;
ALTER TABLE content RENAME old_content;
ALTER TABLE content_temp RENAME content;
COMMIT;

DROP TABLE old_content;
0
sjon1966Commented:
Check out the RENAME statement which can atomically work out a series of table renames.
In your case you could use this (which keeps a backup of your original table):

CREATE TABLE content_temp (...);
INSERT INTO content_temp SELECT * FROM content;
/* ... make my mods here on the content_temp table ... */
RENAME TABLE content TO content_backup, content_temp TO content;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BenMorelAuthor Commented:
@racek : thank you but as I stated, I use a MyISAM table which does not support transactions.
@sjon1966 : nice, I didn't know RENAME TABLE ! Thanks a lot, you got the points :)

Ben
0
BenMorelAuthor Commented:
Exactly what I wanted. Thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.