mysql 创建表 select,MySQL将表创建为SELECT

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.

解决方案

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.