oracel的隔离级别简析(二)

www.2cto.com

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：

遵从事务级的读一致性，仅仅能看见在本事务开始前由其它事务提交的更改。

不允许在本事务中进行DM操作。

read ony是seriaizabe的子集。它们都避免了非重复读和幻像。区别是在read ony中是只读；而在seriaizabe中可以进行DM操作。

Export with CONSISTENT = Y sets the transaction to read-ony.

read committed和seriaizabe的区别和联系：

事务1先于事务2开始，并保持未提交状态。事务2想要修改正被事务1修改的行。事务2等待。如果事务1回滚，则事务2（不论是read committed还是seriaizabe方式）进行它想要做的修改。如果事务1提交，则当事务2是read committed方式时，进行它想要做的修改；当事务2是seriaizabe方式时，失败并报错“Cannot seriaize access”，因为事务2看不见事务1提交的修改，且事务2想在事务1修改的基础上再做修改。

即seriaizabe不允许存在事务嵌套

具体见英文：

www.2cto.com

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.

read committed和seriaizabe可以在ORACE并行服务器中使用。

关于SET TRANSACTION READ WRITE：read write和read committed 应该是一样的。在读方面，它们都避免了脏读，但都无法实现重复读。虽然没有文档说明read write在写方面与read committed一致，但显然它在写的时候会加排他锁以避免更新丢失。在加锁的过程中，如果遇到待锁定资源无法锁定，应该是等待而不是放弃。这与read committed一致。

语句级的读一致性

ORACE保证语句级的读一致性，即一个语句所处理的数据集是在单一时间点上的数据集，这个时间点是这个语句开始的时间。

一个语句看不见在它开始执行后提交的修改。

对于DM语句，它看不见由自己所做的修改，即DM语句看见的是它本身开始执行以前存在的数据。

事务级的读一致性

www.2cto.com

事务级的读一致性保证了可重复读，并保证不会出现幻像。

设置隔离级别

设置一个事务的隔离级别

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 app