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 SohailApr 25, 2016 at 15:26
-
It depends, because select doesnt create alock in innodb, bug select for update does– omriApr 25, 2016 at 16:13
Add a comment
|
1 Answer
You just asked
Does:
INSERT INTO table1... SELECT .. FROM table2Also 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
answered Apr 25, 2016 at 17:21
-
ty ronalando!!! Say, is there anything i can do to prevent the lock in terms of isolation level?– omriApr 26, 2016 at 7:29