MySQL Create Table as SELECT

Question

Everytime I use MySQL's CREATE TABLE AS SELECT ... all the tables/indexes being selected from are locked for the duration of the query. I do not really understand why? Is there any way around this?

Using: MySQL 5.1.41 and InnoDB

Added Example:

For example, the following query might take up to 10 minutes to complete:

CREATE TABLE temp_lots_of_data_xxx AS 
SELECT
    a.*
    b.*
    c.*
FROM a
LEFT JOIN b ON a.foo = b.foo
LEFT JOIN c ON a.foo = c.foo

Trying to update values in tables a, b or c during the above query will wait for the above query to finish first. I want to avoid this lock, as I am not interested in the most complete data in the created temp table.

p.s. SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; yields no change in behavior.

Answer 1

See also http://www.mysqlperformanceblog.com/2006/07/12/insert-into-select-performance-with-innodb-tables/

if not using replication, can change innodb_locks_unsafe_for_binlog to change this locking behaviour.

Or can dump the data to a file, then reload the data from a file. This also avoids the locks.

Answer 2

Have you tried to do the operation in 2 phases (first Create the table, then Insert the values) and having set the lowest isolation level?:

CREATE TABLE temp_lots_of_data_xxx AS 
    SELECT
        a.*
        b.*
        c.*
    FROM a
        LEFT JOIN b ON a.foo = b.foo
        LEFT JOIN c ON a.foo = c.foo
    WHERE FALSE

INSERT INTO temp_lots_of_data_xxx
    SELECT
        a.*
        b.*
        c.*
    FROM a
        LEFT JOIN b ON a.foo = b.foo
        LEFT JOIN c ON a.foo = c.foo

Answer 3

I didn't test this, but you might have a try with

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
CREATE TABLE ...
COMMIT ; /*See comment by Somnath Muluk*/

But be aware:

Select statements are performed in a nonlocking fashion, but a possible earlier version of a row might be used. Thus, using this isolation level, such reads are not consistent. This is also called a “dirty read.”

Read more about it here:

MySQL SET TRANSACTION manual entry

EDIT: added the COMMIT ;

Answer 4

If your engine is InnoDB than it uses automatic row-level locking. Update statements have higher priority then select statements so that's why you are having this problem.

In order to workaround this issue you could SET LOW_PRIORITY_UPDATES=1 and then you should be able to run your command. But this does not fully fit your case. So you could also give higher priority to a SELECT statement as well. To give a specific SELECT statement higher priority, use the HIGH_PRIORITY attribute.

CREATE TABLE temp_lots_of_data_xxx AS 
SELECT HIGH_PRIORITY
    a.*
    b.*
    c.*
FROM a
LEFT JOIN b ON a.foo = b.foo
LEFT JOIN c ON a.foo = c.foo

For details please refer to this page table-locking-issues this page select-syntax and also this page: option_mysqld_low-priority-updates