- 未提交读（read uncommitted）
- 提交读（read committed）
- 重复读（repeatabe read）
- 更新丢失（ost update）：当系统允许两个事务同时更新同一数据是，发生更新丢失。
- 脏读（dirty read）：当一个事务读取另一个事务尚未提交的修改时，产生脏读。
- 非重复读（nonrepeatabe read）：同一查询在同一事务中多次进行，由于其他提交事务所做的修改或删除，每次返回不同的结果集，此时发生非重复读。(A transaction rereads data it has previousy read and finds that another committed transaction has modified or deeted the data. )
- 幻像（phantom read）：同一查询在同一事务中多次进行，由于其他提交事务所做的插入操作，每次返回不同的结果集，此时发生幻像读。(A transaction reexecutes a query returning a set of rows that satisfies a search condition and finds that another committed transaction has inserted additiona rows that satisfy the condition. )
| ||Dirty Read ||NonRepeatabe Read ||Phantom Read |
|Read uncommitted ||Possible ||Possible ||Possible|
|Read committed ||not possible ||Possible ||Possible|
|Repeatabe read ||not possible ||not possible ||Possible|
|Seriaizabe ||not possible ||not possible ||not possible|
If a serializable transaction contains data manipulation language (DML) that attempts to update any resource that may have been updated in a transaction uncommitted at the start of the serializable transaction, （并且修改在后来被提交而没有回滚），then the DML statement fails. 返回的错误是ORA-08177: Cannot serialize access for this transaction。
Oracle permits a serializable transaction to modify a data row only if it can determine that prior changes to the row were made by transactions that had committed when the serializable transaction began.
To make this determination efficiently, Oracle uses control information stored in the data block that indicates which rows in the block contain committed and uncommitted changes. In a sense, the block contains a recent history of transactions that affected each row in the block. The amount of history that is retained is controlled by the INITRANS parameter of CREATE TABLE and ALTER TABLE. Under some circumstances, Oracle may have insufficient history information to determine whether a row has been updated by a "too recent" transaction. This can occur when many transactions concurrently modify the same data block, or do so in a very short period. You can avoid this situation by setting higher values of INITRANS for tables that will experience many transactions updating the same blocks. Doing so will enable Oracle to allocate sufficient storage in each block to record the history of recent transactions that accessed the block.
The INITRANS Parameter：Oracle stores control information in each data block to manage access by concurrent transactions. Therefore, if you set the transaction isolation level to serializable, you must use the ALTER TABLE command to set INITRANS to at least 3. This parameter will cause Oracle to allocate sufficient storage in each block to record the history of recent transactions that accessed the block. Higher values should be used for tables that will undergo many transactions updating the same blocks.
- read ony是seriaizabe的子集。它们都避免了非重复读和幻像。区别是在read ony中是只读；而在seriaizabe中可以进行DM操作。
- Export with CONSISTENT = Y sets the transaction to read-ony.
事务1先于事务2开始，并保持未提交状态。事务2想要修改正被事务1修改的行。事务2等待。如果事务1回滚，则事务2（不论是read committed还是seriaizabe方式）进行它想要做的修改。如果事务1提交，则当事务2是read committed方式时，进行它想要做的修改；当事务2是seriaizabe方式时，失败并报错“Cannot seriaize access”，因为事务2看不见事务1提交的修改，且事务2想在事务1修改的基础上再做修改。
Both read committed and serializable transactions use row-level locking, and both will wait if they try to change a row updated by an uncommitted concurrent transaction. The second transaction that tries to update a given row waits for the other transaction to commit or roll back and release its lock. If that other transaction rolls back, the waiting transaction (regardless of its isolation mode) can proceed to change the previously locked row, as if the other transaction had not existed. However, if the other (blocking) transaction commits and releases its locks, a read committed transaction proceeds with its intended update. A serializable transaction, however, fails with the error "Cannot serialize access", because the other transaction has committed a change that was made since the serializable transaction began.
关于SET TRANSACTION READ WRITE：read write和read committed 应该是一样的。在读方面，它们都避免了脏读，但都无法实现重复读。虽然没有文档说明read write在写方面与read committed一致，但显然它在写的时候会加排他锁以避免更新丢失。在加锁的过程中，如果遇到待锁定资源无法锁定，应该是等待而不是放弃。这与read committed一致。
- SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
- SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
- SET TRANSACTION READ ONLY;
- ATER SESSION SET ISOLATION_LEVE SERIALIZABLE;
- ATER SESSION SET ISOLATION_LEVE READ COMMITTED;
Choice of Isolation Level
Application designers and developers should choose an isolation level based on application performance and consistency needs as well as application coding requirements.
For environments with many concurrent users rapidly submitting transactions, designers must assess transaction performance requirements in terms of the expected transaction arrival rate and response time demands. Frequently, for high-performance environments, the choice of isolation levels involves a trade-off between consistency and concurrency.
Read Committed Isolation
For many applications, read committed is the most appropriate isolation level. Read committed isolation can provide considerably more concurrency with a somewhat increased risk of inconsistent results due to phantoms and non-repeatable reads for some transactions.
Many high-performance environments with high transaction arrival rates require more throughput and faster response times than can be achieved with serializable isolation. Other environments that supports users with a very low transaction arrival rate also face very low risk of incorrect results due to phantoms and nonrepeatable reads. Read committed isolation is suitable for both of these environments.
Oracle read committed isolation provides transaction set consistency for every query. That is, every query sees data in a consistent state. Therefore, read committed isolation will suffice for many applications that might require a higher degree of isolation if run on other database management systems that do not use multiversion concurrency control.
Read committed isolation mode does not require application logic to trap the "Cannot serialize access" error and loop back to restart a transaction. In most applications, few transactions have a functional need to issue the same query twice, so for many applications protection against phantoms and non-repeatable reads is not important. Therefore many developers choose read committed to avoid the need to write such error checking and retry code in each transaction.
Oracle's serializable isolation is suitable for environments where there is a relatively low chance that two concurrent transactions will modify the same rows and the long-running transactions are primarily read-only. It is most suitable for environments with large databases and short transactions that update only a few rows.
Serializable isolation mode provides somewhat more consistency by protecting against phantoms and nonrepeatable reads and can be important where a read/write transaction executes a query more than once.
Unlike other implementations of serializable isolation, which lock blocks for read as well as write, Oracle provides nonblocking queries and the fine granularity of row-level locking, both of which reduce write/write contention. For applications that experience mostly read/write contention, Oracle serializable isolation can provide significantly more throughput than other systems. Therefore, some applications might be suitable for serializable isolation on Oracle but not on other systems.
All queries in an Oracle serializable transaction see the database as of a single point in time, so this isolation level is suitable where multiple consistent queries must be issued in a read/write transaction. A report-writing application that generates summary data and stores it in the database might use serializable mode because it provides the consistency that a
ONLY transaction provides, but also allows
G System Engineer Oracle DBA
G Fuji Xerox China Limited (Shanghai)