ORACLE VS. MYSQL TRANSACTION ISOLATION
来源:互联网 发布:水果产地调查表软件 编辑:程序博客网 时间:2024/06/05 16:22
转自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: |
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. |
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. |
- ORACLE VS. MYSQL TRANSACTION ISOLATION
- mysql事务隔离性(mysql transaction isolation level)
- Oracle 数据隔离级别(Transaction Isolation Levels) 说明
- Oracle 数据隔离级别(Transaction Isolation Levels) 说明
- MySQL数据库事务隔离级别(Transaction Isolation Level)
- MySQL数据库事务隔离级别(Transaction Isolation Level)
- MySQL数据库事务隔离级别(Transaction Isolation Level)
- MySQL数据库事务隔离级别(Transaction Isolation Level)
- MySQL数据库事务隔离级别(Transaction Isolation Level)
- MySQL数据库事务隔离级别(Transaction Isolation Level)
- MySQL数据库事务隔离级别(Transaction Isolation Level)
- MySQL数据库事务隔离级别(Transaction Isolation Level)
- MySQL数据库事务隔离级别(Transaction Isolation Level)
- MySQL数据库事务隔离级别(Transaction Isolation Level)
- MySQL数据库事务隔离级别(Transaction Isolation Level)
- MySQL数据库事务隔离级别(Transaction Isolation Level)
- MySQL数据库事务隔离级别(Transaction Isolation Level)
- MySQL数据库事务隔离级别(Transaction Isolation Level)
- Hibernate配置文件中映射元素详解
- TD无法发送邮件问题的解决
- cookie使用方法小结
- 给Conlose程序穿个马甲
- new、delete表达式
- ORACLE VS. MYSQL TRANSACTION ISOLATION
- Linux VI
- 软件项目管理Follow Me--如何进行项目估算
- Oracle序列(sequence)创建失败,无法取值(.nextval),无法删除(drop)解决办法
- An Essay on Endian Order
- 关于linux 下的编程
- 远程会议概述及其技术演变(电话会议,视频会议,网络会议)
- ActiveMovie属性说明
- Java框架介绍:Quartz从入门到进阶(图)