MySQL INSERT ... SELECT Performance

Question

I know that this query will lock table2:

UPDATE table1... SELECT .. FROM table2

How about this query ?

INSERT INTO table1... SELECT .. FROM table2

Does this query also create a lock on table2?

Answer 1

You just asked

Does:

INSERT INTO table1... SELECT .. FROM table2

Also create a lock on table2?

Yes, it does create a lock on table2.

I wrote about this behavior back on Aug 08, 2014 (See my answer to MySQL consistent nonlocking reads vs. INSERT ... SELECT) In my old post, I mentioned from the MySQL Documentation:

By default, InnoDB uses stronger locks and the SELECT part acts like READ COMMITTED, where each consistent read, even within the same transaction, sets and reads its own fresh snapshot