If you don’t know, you should read this. Even you know, you should read it.
When you run this sql:
INSERT INTO table1 SELECT * FROM table2
If it does not finish yet and you run this sql:
INSERT INTO table2 ....
You will have a problem that it is slow. It will do nothing until the first sql is done. That is because “INSERT INFO … SELECT FROM” locks the table2.
You may already know this thing.
But there is another thing. Assume that you have table3 and it has a foreign key linking to the primary key in table2. When you insert in to table3 with a value for that foreign key, you will have the same problem too.
So, it can have a scenario that you wonder why a simple INSERT talking time. You cannot find anywhere SQL locking on that table. Which, actually, the cause of the problem is there is a mystery job running INSERT INFO on another table which your target table has a foreign key to.
For in here: