Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

Transactions - Oracle vs. MySQL

Options
  • 01-08-2013 1:42am
    #1
    druidhill
    Registered Users Posts: 200 ✭✭


    Hi,

    I am trying to understand a little bit about how Oracle and MySQL differ in certain things.

    In terms of transaction processing - I have two user sessions for each (could be either 2 users or 1 user with 2 sessions). Default transaction levels used (or at least I have not explicitly set anything).

    In Oracle, if I do some sort of insert, update or delete and run a select statement before and after, the second session does not see the changes until after a COMMIT; by the first user/session (as expected).

    In MySQL, the second session does not see the change even after the commit, however, if I exit out and login again, the changes are now evident.

    I'm not sure if this is just a little quirk (existing sessions will not see changes) or is there a difference between Oracle and MySQL here - i.e. for this scenario, do Oracle and MySQL behave in the same manner?

    Appreciate it if anybody who has experience with both would explain further.

    Thanks.


Comments

  • Options
    #2
    CreepingDeath
    Closed Accounts Posts: 8,016 ✭✭✭CreepingDeath


    You can set the transaction isolation level you want in most databases.
    Here's the link for MySql.

    MySql transaction isolation level

    Sounds like you want the "read uncommitted" level


  • Options
    #3
    Giblet
    Registered Users Posts: 11,977 ✭✭✭✭Giblet


    Be wary of setting read uncommitted unless you want data to be read which might not actually be valid.

    What tool are you using for the MYSQL queries? Some tools have a habit of creating a session with repeatable read


  • Options
    #4
    stevenmu
    Moderators, Society & Culture Moderators Posts: 9,689 Mod ✭✭✭✭stevenmu


    Does the isolation level still affect it after the transaction has been committed?

    I would have thought it's possibly a caching issue, the second process is getting results from a cache which has not been updated after the transaction was committed.


  • Options
    #5
    druidhill
    Registered Users Posts: 200 ✭✭druidhill


    Thanks for the replies - I (eventually) found what I was looking for in the MySQL docs. Oracle SQL is read committed by default whereas MySQL is repeatable read, so this explains the difference in behaviour.

    Pretty confusing topic to get your head around this transaction level stuff, lots of very similar terms used.


Advertisement