INSERT… SELECT isolation?

Question

I'm not sure isolation is the right term, but I think it is. Let me know if not.

I'm doing an insert...select statement, from a table with keys into a lock table. I have some WHERE conditions by which I select the row I want.

From what I understand, first a select happens, then the result is used for an insert. The question is, is there a chance that while the insert is happening, my WHERE conditions no longer apply to the row I insert? ie, can another query change the rows I SELECT...INSERTed, or are they locked between the two queries?

Answer 1

No, MySQL builds a temporary result set from your query before performing the insert.

When selecting from and inserting into a table at the same time, MySQL creates a temporary table to hold the rows from the SELECT and then inserts those rows into the target table.

Source: MySQL Documentation

Edit based on Comments

The insert is an atomic operation and so the data used to build the temporary result set will match the committed data in the source table at the precise moment that the query was executed (i.e. it is a snapshot of that point in time).

After the temporary result set has been built any modifications to the source table will not be reflected in the results being inserted into the target table - unless you run the query again.

Answer 2

Depending on the tx_isolation (REPEATABLE-READ, READ_COMMITTED, etc), you get some slice of the data when SELECTing.

Of course, if you are doing INSERT...SELECT on two tables that have FOREIGN KEY relationships between them, changes could mess up the relationships. This can be overcome with BEGIN...COMMIT.

(Caveat: What I have said here is meant to be clues of what to study, not a definitive answer to the question.)