ORACLE VS. MYSQL TRANSACTION ISOLATION

转自http://www.tomlauren.com/weblog/archives/000019.html

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

Of course, in a real J2EE application, you wouldn't explicitly run these commands. The database/application server would take care of this for you.

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.