0

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?

| improve this question | |
  • What's with the downvotes? – FrontierPsycho Dec 3 '15 at 16:57
  • Downvotes are for a question that "does not show any research effort". If you had checked the MySQL documentation for INSERT...SELECT, as Mr. Brownstone did, you could have found the answer quite quickly. – LowlyDBA Dec 3 '15 at 19:33
  • I see. I did read the documentation (not thoroughly, though), but as you can see in my comment fo Mr. Brownstone, even if I had found that paragraph I wouldn't have assumed the answer. His updated answer covers it, though! – FrontierPsycho Dec 4 '15 at 8:24
2

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.

| improve this answer | |
0

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.)

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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