How to improve INSERT INTO ... SELECT locking behavior

mysql select insert locking innodb
Artem picture Artem · Apr 14, 2010 · Viewed 42.9k times · Source

In our production database, we ran the following pseudo-code SQL batch query running every hour:

INSERT INTO TemporaryTable
    (SELECT FROM HighlyContentiousTableInInnoDb
     WHERE allKindsOfComplexConditions are true)

Now this query itself does not need to be fast, but I noticed it was locking up HighlyContentiousTableInInnoDb, even though it was just reading from it. Which was making some other very simple queries take ~25 seconds (that's how long that other query takes).

Then I discovered that InnoDB tables in such a case are actually locked by a SELECT! https://www.percona.com/blog/2006/07/12/insert-into-select-performance-with-innodb-tables/

But I don't really like the solution in the article of selecting into an OUTFILE, it seems like a hack (temporary files on filesystem seem sucky). Any other ideas? Is there a way to make a full copy of an InnoDB table without locking it in this way during the copy. Then I could just copy the HighlyContentiousTable to another table and do the query there.

Answer

Morgan Tocker picture Morgan Tocker · Oct 28, 2010

The answer to this question is much easier now: - Use Row Based Replication and Read Committed isolation level.

The locking you were experiencing disappears.

Longer explaination: http://harrison-fisk.blogspot.com/2009/02/my-favorite-new-feature-of-mysql-51.html

Related questions

MySQL INSERT INTO ... VALUES and SELECT

Is there a way to insert pre-set values and values I get from a select-query? For example: INSERT INTO table1 VALUES ("A string", 5, [int]). I have the value of "A string" and the number 5, but I've to find the [int] …

Read More
MySQL error 1241: Operand should contain 1 column(s)

I am trying to Insert data from a table1 into table2 insert into table2(Name,Subject,student_id,result) select (Name,Subject,student_id,result) from table1; Key for table2 is student_id. Assume that there are not any duplicates. …

Read More
Insert data into table with result from another select query

I am seeking help on the following issue: I have two tables Table_1 columns are itemid, locationid, quantity Table_2 columns are itemid, location1, location2, location3 I want to copy data from Table_1 (only quantity column) into Table_2 (into location1 column). …

Read More