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?
MySQL 5.1.41 and
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.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; yields no change in behavior.