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?

logically yes if you go for full table scan in select criteria — Nawaz Sohail, Apr 25 '16 at 15:26
It depends, because select doesnt create alock in innodb, bug select for update does — omri, Apr 25 '16 at 16:13

score 4 · Accepted Answer · edited Apr 13 '17 at 12:42

4

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

edited Apr 13 '17 at 12:42

Community

1

answered Apr 25 '16 at 17:21

RolandoMySQLDBA

182,700
33
317
520

ty ronalando!!! Say, is there anything i can do to prevent the lock in terms of isolation level? – omri Apr 26 '16 at 07:29

MySQL INSERT ... SELECT Performance

1 Answers1

Linked