Transactions - Oracle vs. MySQL

druidhill

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.

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

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

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.

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.