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.

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 | |
  • just for sure, is reading a single record always an atomic operation? – user40602 Sep 18 '14 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 '14 at 9:41

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.