Problem with MySQL RENAME and CREATE TABLE statements clashing

Question

I have a problem where a RENAME statement clashes with a CREATE TABLE statement to cause deadlock.

I have a CREATE TABLE statement of the form

CREATE TABLE `ProductTitles`
(
    PRIMARY KEY (`ProductId`),
    INDEX ( `Title`, `ProductScore` )
)    
SELECT p.ProductId, p.Title, ps.ProductScore
FROM Products p
JOIN ProductScores ps USING (ProductId)

Due to table sizes, this can take around 30 minutes to complete.

The problem occurs when this statement is running when another process tries to replace the Products table using a RENAME statement of the form:

RENAME TABLE
Products TO ProductsOld, 
ProductsNew TO Products

This rename statement then locks the Products table for reads and writes until the CREATE TABLE statement has completed.

My question is how do I avoid this situation?

Is is possible to use locks, or perhaps querying for the presence of a CREATE TABLE statement, to avoid running the RENAME statement until the CREATE statement is done?

As far as I can tell, this occurs whether using MyISAM or InnoDb. There must be some mechanism to protect against this happening.

Answer 1

If you use myisam count() is very quick, you could compare the sizes of Products to ProductTitles before attempting the rename