Return to Revisions

You should use SELECT ... LOCK IN SHARE MODE. Why ?

SELECT ... LOCK IN SHARE MODE sets a shared mode lock on any rows that are read. Other sessions can read the rows, but cannot modify them until your transaction commits. If any of these rows were changed by another transaction that has not yet committed, your query waits until that transaction ends and then uses the latest values.

In your case, you could attempt this

START TRANSACTION;
SELECT ... LOCK IN SHARE MODE;
SELECT ... INTO OUTFILE;
ROLLBACK;

This would do two SELECT queries

• First SELECT to lock the rows in the table you wish
• Second SELECT to perform SELECT ... INTO OUTFILE

Personally, I do not think you have to be this heavy-handed. Transaction isolation should be smart enough to pull off this atomic SELECT and use the same rows for the INSERT. I know I said should be which is why your are asking you question in the first place.

Whether you do SELECT ... INTO OUTFILE as one command or in the heavy-handed manner I am proposing, the row data of the source table will be fully readable.

GIVE IT A TRY !!!

#UPDATE 2014-12-10 15:12 EST

Your comment

Thx for the answer, and it does help, but the main point of the OP was to determine if there is a benefit for using SELECT INTO OUTFILE over INSERT INTO ... SELECT?

They are operationally different

• SELECT INTO OUTFILE creates a text file
• INSERT INTO SELECT loads one table from the results of the SELECT