title: How to improve INSERT INTO ... SELECT locking behavior
body: <p>In our production database, we ran the following pseudo-code SQL batch query running every hour:</p>
  
  <pre><code>INSERT INTO TemporaryTable
      (SELECT FROM HighlyContentiousTableInInnoDb
       WHERE allKindsOfComplexConditions are true)
  </code></pre>
  
  <p>Now this query itself does not need to be fast, but I noticed it was locking up <code>HighlyContentiousTableInInnoDb</code>, 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).</p>
  
  <p>Then I discovered that InnoDB tables in such a case are actually locked by a SELECT! <a href="https://www.percona.com/blog/2006/07/12/insert-into-select-performance-with-innodb-tables/" rel="noreferrer">https://www.percona.com/blog/2006/07/12/insert-into-select-performance-with-innodb-tables/</a></p>
  
  <p>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 <code>HighlyContentiousTable</code> to another table and do the query there.</p>