Oracle vs. MySQL Transaction Isolation
Most (all) database management systems allow you to have control over the isolation level of your transactions. However, the database's behavior at each isolation level is, unfortunately, vendor-specific. Also, every vendor does not support all transaction isolation levels. Here I compare the transaction isolation behavior of Oracle to that of MySQL.
As a reminder, there are four standard transaction isolation levels:
Transaction Isolation Levels
| Isolation Level | Dirty read | Unrepeatable read | Phantom read |
| Read Uncommitted | Yes | Yes | Yes |
| Read Committed | No | Yes | Yes |
| Repeatable Read | No | No | Yes |
| Serializable | No | No | No |
According to the table, the Serializable isolation level prevents dirty reads, unrepeatable reads, and phantom reads. The Read Uncommitted isolation level allows all three concurrency hazards.
Here's a summary of these concurrency hazards:
Dirty Read: Occurs when the intermediate (uncommitted) results of one transaction are made visible to another transaction.
Unrepeatable Read: Occurs when one transaction reads a data item and subsequently rereads the same item and sees a different value.
Phantom Read: Occurs when one transaction performs a query that returns multiple rows, and later executes the same query again and sees additional rows that were not present the first time the query was executed.
Now let's compare the behaviors of Oracle and MySQL with respect to transaction isolation.
Here are some helpful hints on how to experiment with transaction isolation levels in the client programs of Oracle and MySQL.
Controlling transaction isolation behavior with the database's client program
| Transaction Info | Oracle | MySQL |
| Client program | sqlplus | mysql |
| Default isolation level | Read Committed | Repeatable Read |
| How to manually set the isolation level | SET TRANSACTION ISOLATION LEVEL {READ COMMITTED | SERIALIZABLE} | SET TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE} |
| How to query the isolation level | Not supported? | SELECT @@tx_isolation |
| How to start a transaction | autocommit is off by default, so a new transaction is started after each COMMIT or ROLLBACK | START TRANSACTION |
| How to end a transaction | COMMIT or ROLLBACK | COMMIT or ROLLBACK |
| How to control the default transaction isolation level | Rely on app server-specific deployment descriptor? (Weblogic supports specifying the isolation level, JBoss does not.) | Put a line like the following in the [mysqld] section of my.cnf: tranaction-isolation = REPEATABLE-READ |
Transaction isolation behavior differences
The following statement is true for both Oracle and MySQL:
For all transaction isolation levels, if one transaction performs an update on a row, all other transactions are blocked if they attempt to update that same row, until the first transaction ends.
| Isolation Level | Oracle | MySQL |
| Read Uncommitted | Not supported | The intermediate (uncommitted) results of one transaction are made visible to all other transactions. |
| Read Committed | When one transaction commits, those changes are immediately visible in all other currently running transactions. | When one transaction commits, those changes are immediately visible in all other currently running transactions. |
| Repeatable Read | Not supported | The first time that a read is performed on a table results in that view of the table being preserved throughout that transaction. Any changes made on that table committed by other transactions after that point are not seen. However, if a different transaction commits a change between the time that you start your transaction and the time that you perform your first read on that table, you will see the changes made by that other transaction. Phantom reads are prevented, even though officially phantom reads are supposed to be allowed at this isolation level. |
| Serializable | Phantom reads are prevented, but transactions are not blocked on reads like in MySQL. | If one transaction performs an update on a row, all other transactions are blocked if they attempt to update or even read that row, until the first transaction ends. Thus phantom reads are prevented. |
