1

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.

Improve this question

1 Answer 1

Reset to default
2

Yes, it is possible and this may or may not be desirable. The database theory has a concept of isolation, which is about transaction visibility to other processes. MySql's documentation about set transaction has a discussion about product specific syntax.

Improve this answer
2
  • just for sure, is reading a single record always an atomic operation?
    – user40602
    Sep 18, 2014 at 5:58
  • InnoDB claims full ACID compliance, so no partial insert should be able to mess data consistency. That being said, with right isolation level, a transaction that won't be committed might mess up your data anyway. So, please study carefully the transaction isolation theory and documentation.
    – vonPryz
    Sep 22, 2014 at 9:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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