Alex Rivera | Logout

MySQL Create Table as SELECT

Asked 2012-03-15T07:36:07.603
27

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.

Edit
Report

1 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 ;

answered 2012-03-19T10:09:12.400

Your Answer