MySQL Create Table as 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.

Contents hide

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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.

Method 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

Method 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.”

Method 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

Method 5

My psychic debugging skills suggest that you’re trying to access the tables/indexes while you’re debugging the query that uses them.

In general, I’d not be too surprised if a CREATE TABLE query locks all the tables and indexes from which it is reading.

If my psychic premonition is right, I’d suggest letting the query finish before accessing the tables and indexes it is using.

(Please correct me if I made any wrong assumptions.)

Method 6

All InnoDB locks held by a transaction are released when the
transaction is committed or aborted. Thus, it does not make much sense
to invoke LOCK TABLES on InnoDB tables in autocommit=1 mode because
the acquired InnoDB table locks would be released immediately.

As read here

EDIT and this:

You cannot lock additional tables in the middle of a transaction
because LOCK TABLES performs an implicit COMMIT and UNLOCK TABLES.

Method 7

I’m not as experienced in mysql but I ran into the same problem in mssql. The solution there was to run the “create table as select…” with zero rows so that it only creates the table with the appropriate structure and releases the locks immediately. Then use “insert into” with the same select statement to populate the table, which won’t hold any schema locks while it runs.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating