When we have to use several different SELECT queries to get data from various locations in the database, how to make sure they are isolated from queries such as INSERT, UPDATE or DELETE so that we can be sure that data selected are consistent with each other and not changed in between? I know I can use transaction to achieve the same effect for INSERT, UPDATE and DELETE so changes are applied as a whole or not. But does it isolate SELECTs as well? For example, 3 SELECT queries return 3 different values, either: 1, 3, 5 Or: 2, 4, 8 A update query is constantly switching the values. How to make sure I get either: 1, 3, 5 Or: 2, 4, 8 But not anything in between such as: 2, 3, 5, nor 2, 4, 5, etc. I know I can get those updates in a transaction but I want double check. So would it work if I put those 3 SELECTs in a transaction?
you have to execute it in one transaction thats right. in 3 diffenrent transcation you cant guarantee consistancy!
Not sure if i understand your question... so i'll try to answer both bits that i can deduce. When you update multiple rows in 1 query, it updates all of those rows. What i am understanding, you are worried about if you run an update and a select query at the same time, in which case, mysql might return mixed results between old and new data. The solution would be to use the MyISAM DB Engine. MyISAM performs table-level locking, which would help solve your problem. Vs InnoDB which performs row-level locking.