Suppose a read (select) query R is reading some 100 records from a table, 50 records are read so far, could in the meanwhile a write query W change some of the unread records?
We didn't take any locks on the table (or to be read records).
I think it is obvious that reading single records (reading every record) is an atomic operation, but i am not sure about reading several (possibly millions) records. Is it an atomic operation? Do we need to take a read lock when reading multiple records (in one query) if it is possible that a write query be run in the meanwhile and we want a consistent snapshot of all of the records at the time the read query starts to be executed?
I am using MySQL.