Does a SELECT happen all at once, or progressively

Question

I have a process which finds a list of files to be deleted using a SELECT wheredelete= 'Y'.

I set this process running the other day but it takes a while because it actually does the file deletions too.

And in the middle of its long operation, I was using the application and deleted one more file.

At this point I realised I didn't know if that file would be deleted, because I didn't know if the SELECT would have found all the files at the start, or if it was finding them progressively and would get to my newly-deleted file eventually.

Answer 1

It depends on when the SELECT statement was executed, and how your app is written (if it is using an ORM, etc), but I suspect the below analysis is probably valid.

If you have code that's like this:

<prepare query>
<execute query>
for each row in <query_result_cursor>
    <delete file>

The select will have acquired all it's rows, and if later a row is updated to have Delete='Y', then it won't pick it up.

The key is identifying when <execute query> occurs, and when that occurs in relation to your delete. If <execute query> occurs before you deleted the file using your app, it will get picked up in your process. If <execute query> occurs after, it won't pick it up.

The short answer: it happens all at once.