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.
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.
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.
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
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
Read more about it here:
EDIT: added the
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
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
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.)
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.
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.