1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

How to make sure several SELECTs are isolated?

Discussion in 'MySQL' started by TheDataPlanet.com, Mar 4, 2015.

  1. #1
    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?
     
    TheDataPlanet.com, Mar 4, 2015 IP
  2. shureg

    shureg Active Member

    Messages:
    18
    Likes Received:
    2
    Best Answers:
    1
    Trophy Points:
    80
    #2
    you have to execute it in one transaction thats right.
    in 3 diffenrent transcation you cant guarantee consistancy!
     
    shureg, Mar 4, 2015 IP
  3. hotnoob

    hotnoob Member

    Messages:
    96
    Likes Received:
    2
    Best Answers:
    1
    Trophy Points:
    28
    #3
    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.
     
    hotnoob, Mar 11, 2015 IP