How do I resolve MySQL INSERT INTO... SELECT causing write lock for SELECTed table?

Question

I have a single, unreplicated MySQL server.

We're experiencing a problem where an INSERT INTO ... SELECT statement is causing a write lock on the origin table.

The origin table is in one database. The destination table is a temporary table in another database.

Following the advice of this question's accepted answer: How to improve INSERT INTO ... SELECT locking behavior

I changed the following three configuration variables in my.cnf and restarted the server:

innodb_autoinc_lock_mode=2
binlog_format=row
transaction-isolation=READ-COMMITTED

This seemed to make the problem worse, though correlation may not be causation in this case.

How do I prevent this write lock?

Answer 1

It sounds to me as if your transaction isolation level does not allow INSERTS while a sequenced read of the same data is underway.

Understand that this might be a good thing. MySQL doesn't block here for nothing. It just means that the data being read by your one statement might not be changed during the atomic operation. So an INSERT is not given permission to insert data that would otherwise change the data you're inserting into the temporary table now.

You can take a look at this page describing everything there is to know about transaction isolation from a MySQL perspective.