2

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?

2
  • logically yes if you go for full table scan in select criteria Commented Apr 25, 2016 at 15:26
  • It depends, because select doesnt create alock in innodb, bug select for update does
    – omri
    Commented Apr 25, 2016 at 16:13

1 Answer 1

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

1
  • ty ronalando!!! Say, is there anything i can do to prevent the lock in terms of isolation level?
    – omri
    Commented Apr 26, 2016 at 7:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.