Why does INSERT SELECT into an unrelated table block when SELECT doesn't? Mysql 5.6

Simplified, I have a table 'app' with a primary auto increment field app_id, and one field created_at datetime not null.

I have another table, 'app_subset', with a single key 'app_id'.

If I run a query like this:

SELECT app_id FROM app;

then while that is running, I execute:

INSERT INTO app (created_at) VALUES (NOW());

The insert executes immediately, not waiting for the select to finish.

If, however, I run a query like this:

INSERT IGNORE INTO app_subset SELECT app_id FROM app;

and then run the same insert into app:

INSERT INTO app (created_at) VALUES (NOW());

now the INSERT INTO app query blocks, waiting for the INSERT IGNORE to finish.

I'm trying to understand why this is the case. I understand that the results of my select will be changed by the INSERT INTO app, but it seems like if the original SELECT doesn't block, then an INSERT using the results of that select shouldn't block either. I'd like to be able to set things up so this is the case in some fashion, or at least fully understand why it is happening so I can anticipate similar issues in the future.

from Rolando's answer below, it sounds like READ UNCOMMITED should work, and indeed when I execute:

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

The INSERT IGNORE .. SELECT no longer causes my INSERT INTO app to block.

I am curious if there is a way to do it as a single line (something like:)

INSERT IGNORE INTO app_subset SELECT app_id FROM app LOCK IN NONSHARING MODE;

Actually, INSERT INTO .. SELECT does lock the SELECT table

I wrote about this before

• Apr 25, 2016 : MySQL INSERT ... SELECT Performance

• Aug 08, 2014 : MySQL consistent nonlocking reads vs. INSERT ... SELECT (More Details)

StackExchange Database Administrators Q#149826, answer score: 10