MySQL Locks while CREATE TABLE AS SELECT

I am running the following (dummy) query

CREATE TABLE large_temp_table AS 
    SELECT a.*, b.*, c.* 
    FROM a
    LEFT JOIN b ON a.foo = b.foo
    LEFT JOIN c ON a.bar = c.bar

Suppose the query takes 10 minutes to run. Trying to update values in tables a, b or c while it is running will wait for the above query to finish first. I want to avoid this lock (data consistency is not of interest). How can I achieve that?

Using: MySQL 5.1.41 and InnoDB Tables

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

Update
While the query is being executed, the output of SHOW ENGINE INNODB STATUS is the following (I have made a very slow query here in purpose)

```
=====================================
120323 15:26:29 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 8 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 1470, signal count 1468
Mutex spin waits 0, rounds 7525, OS waits 112
RW-shared spins 803, OS waits 364; RW-excl spins 1300, OS waits 959
------------
TRANSACTIONS
------------
Trx id counter 0 3145870
Purge done for trx's n:o < 0 3141943 undo n:o < 0 0
History list length 22
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, OS thread id 2958192640
MySQL thread id 7942, query id 69073 localhost root
SHOW ENGINE INNODB STATUS
---TRANSACTION 0 3145869, ACTIVE 20 sec, OS thread id 2955325440, thread declared inside InnoDB 343
mysql tables in use 1, locked 1
6 lock struct(s), heap size 1024, 162 row lock(s)
MySQL thread id 7935, query id 69037 localhost root Copying to tmp table
CREATE TABLE 1_temp_foo AS
SELECT SQL_NO_CACHE
a.*
FROM
crm_companies AS a
LEFT JOIN users b ON a.zipcode = b.uid
LEFT JOIN calc_base_materials c ON a.zipcode = c.material_id
LE

I see this query in your SHOW INNODB STATUS\G

CREATE TABLE 1_temp_foo AS 
                   SELECT SQL_NO_CACHE 
                       a.* 
                   FROM 
                       crm_companies AS a 
                   LEFT JOIN users b ON a.zipcode = b.uid 
                   LEFT JOIN calc_base_materials c ON a.zipcode = c.material_id 
                   LEFT JOIN calc_base_material_langtext d ON a.zipcode = d.material_id 
                   LEFT JOIN crm_people e ON a.zipcode = e.telephone1_number 
                   ORDER BY a.country, a.name1

This query gives me the creeps because it combines three things you may not have thought of:

• InnoDB is involved based on your initial premise : Using: MySQL 5.1.41 and InnoDB Tables

• MyISAM is also involved. Why is MyISAM involved? ALL INTERNAL TEMP TABLES ARE MyISAM !!! The resulting join is a MyISAM table that must be converted into InnoDB when the temp table has been populated. What is the default lock level for MyISAM tables? Table Level Locking.

• DDL is involved since a newly created table must be brought into existence. That new table would not be manifested until the temp table is populated, converted to InnoDB, and finally renamed 1_temp_foo.

There is another side effect worth noting. When you do

CREATE TABLE tblname AS SELECT ...

The resulting table has no indexes.

I have something you might find helpful to bypass the locking issue. It involves making the table first as a separate query, then populating it. There are two options for making your temp table:

OPTION #1 : Try creating the table with the same layout

CREATE TABLE 1_temp_foo LIKE crm_companies;

This will create the table 1_temp_foo to have the exact same indexes and storage engine as the original table crm_companies.

OPTION #2 : Try creating the table with the same storage engine only, but no indexes.

CREATE TABLE 1_temp_foo SELECT * FROM crm_companies WHERE 1=2;
ALTER TABLE 1_temp_foo ENGINE=InnoDB;

After creating the table (whichever way you choose), you can now populate the table like this:

INSERT INTO 1_temp_foo
SELECT SQL_NO_CACHE a.*                   
FROM                   
    crm_companies AS a                   
    LEFT JOIN users b ON a.zipcode = b.uid                   
    LEFT JOIN calc_base_materials c ON a.zipcode = c.material_id                   
    LEFT JOIN calc_base_material_langtext d ON a.zipcode = d.material_id                   
    LEFT JOIN crm_people e ON a.zipcode = e.telephone1_number                   
    ORDER BY a.country, a.name
;

Now, this query should produced row-level locks for the sake of having data available for repeatable reads. In other words, this is a transactional query.
CAVEAT

OPTION #2 has advantages over OPTION #1

• Advantage #1 : If crm_companies has any foreign key constraints, OPTION #1 is not really possible. You would have to choose OPTION #2 for the sake of simplicity.

• Advantage #2 : Since OPTION #2 creates a table with no user-defined indexes, the table should load faster than if the table were made via OPTION #1.

StackExchange Database Administrators Q#15461, answer score: 12