Todd

隔離級(jí)別（isolation level）

l         隔離級(jí)別定義了事務(wù)與事務(wù)之間的隔離程度。

l         隔離級(jí)別與并發(fā)性是互為矛盾的：隔離程度越高，數(shù)據(jù)庫(kù)的并發(fā)性越差；隔離程度越低，數(shù)據(jù)庫(kù)的并發(fā)性越好。

l         ANSI/ISO SQL92標(biāo)準(zhǔn)定義了一些數(shù)據(jù)庫(kù)操作的隔離級(jí)別：

l          未提交讀（read uncommitted）

l          提交讀（read committed）

l          重復(fù)讀（repeatable read）

l          序列化（serializable）

l         通過(guò)一些現(xiàn)象，可以反映出隔離級(jí)別的效果。這些現(xiàn)象有：

l          更新丟失（lost update）：當(dāng)系統(tǒng)允許兩個(gè)事務(wù)同時(shí)更新同一數(shù)據(jù)是，發(fā)生更新丟失。

l          臟讀（dirty read）：當(dāng)一個(gè)事務(wù)讀取另一個(gè)事務(wù)尚未提交的修改時(shí)，產(chǎn)生臟讀。

l          非重復(fù)讀（nonrepeatable read）：同一查詢(xún)?cè)谕皇聞?wù)中多次進(jìn)行，由于其他提交事務(wù)所做的修改或刪除，每次返回不同的結(jié)果集，此時(shí)發(fā)生非重復(fù)讀。(A transaction rereads data it has previously read and finds that another committed transaction has modified or deleted the data.  )

l          幻像（phantom read）：同一查詢(xún)?cè)谕皇聞?wù)中多次進(jìn)行，由于其他提交事務(wù)所做的插入操作，每次返回不同的結(jié)果集，此時(shí)發(fā)生幻像讀。(A transaction reexecutes a query returning a set of rows that satisfies a search condition and finds that another committed transaction has inserted additional rows that satisfy the condition.  )

l         下面是隔離級(jí)別及其對(duì)應(yīng)的可能出現(xiàn)或不可能出現(xiàn)的現(xiàn)象

ORACLE的隔離級(jí)別

l         ORACLE提供了SQL92標(biāo)準(zhǔn)中的read committed和serializable，同時(shí)提供了非SQL92標(biāo)準(zhǔn)的read-only。

l          read committed：

l         這是ORACLE缺省的事務(wù)隔離級(jí)別。

l         事務(wù)中的每一條語(yǔ)句都遵從語(yǔ)句級(jí)的讀一致性。

l         保證不會(huì)臟讀；但可能出現(xiàn)非重復(fù)讀和幻像。

l          serializable：

l         簡(jiǎn)單地說(shuō)，serializable就是使事務(wù)看起來(lái)象是一個(gè)接著一個(gè)地順序地執(zhí)行。

l         僅僅能看見(jiàn)在本事務(wù)開(kāi)始前由其它事務(wù)提交的更改和在本事務(wù)中所做的更改。

l         保證不會(huì)出現(xiàn)非重復(fù)讀和幻像。

l         Serializable隔離級(jí)別提供了read-only事務(wù)所提供的讀一致性（事務(wù)級(jí)的讀一致性），同時(shí)又允許DML操作。

l         如果有在serializable事務(wù)開(kāi)始時(shí)未提交的事務(wù)在serializable事務(wù)結(jié)束之前修改了serializable事務(wù)將要修改的行并進(jìn)行了提交，則serializable事務(wù)不會(huì)讀到這些變更，因此發(fā)生無(wú)法序列化訪問(wèn)的錯(cuò)誤。（換一種解釋方法：只要在serializable事務(wù)開(kāi)始到結(jié)束之間有其他事務(wù)對(duì)serializable事務(wù)要修改的東西進(jìn)行了修改并提交了修改，則發(fā)生無(wú)法序列化訪問(wèn)的錯(cuò)誤。）

l         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, （并且修改在后來(lái)被提交而沒(méi)有回滾），then the DML statement fails. 返回的錯(cuò)誤是ORA-08177: Cannot serialize access for this transaction。

l         ORACLE在數(shù)據(jù)塊中記錄最近對(duì)數(shù)據(jù)行執(zhí)行修改操作的N個(gè)事務(wù)的信息，目的是確定是否有在本事務(wù)開(kāi)始時(shí)未提交的事務(wù)修改了本事務(wù)將要修改的行。具體見(jiàn)英文：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.

l          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.

l          read-only：

l         遵從事務(wù)級(jí)的讀一致性，僅僅能看見(jiàn)在本事務(wù)開(kāi)始前由其它事務(wù)提交的更改。

l         不允許在本事務(wù)中進(jìn)行DML操作。

l         read only是serializable的子集。它們都避免了非重復(fù)讀和幻像。區(qū)別是在read only中是只讀；而在serializable中可以進(jìn)行DML操作。

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

l          read committed和serializable的區(qū)別和聯(lián)系：

l         事務(wù)1先于事務(wù)2開(kāi)始，并保持未提交狀態(tài)。事務(wù)2想要修改正被事務(wù)1修改的行。事務(wù)2等待。如果事務(wù)1回滾，則事務(wù)2（不論是read committed還是serializable方式）進(jìn)行它想要做的修改。如果事務(wù)1提交，則當(dāng)事務(wù)2是read committed方式時(shí)，進(jìn)行它想要做的修改；當(dāng)事務(wù)2是serializable方式時(shí)，失敗并報(bào)錯(cuò)“Cannot serialize access”，因?yàn)槭聞?wù)2看不見(jiàn)事務(wù)1提交的修改，且事務(wù)2想在事務(wù)一修改的基礎(chǔ)上再做修改。具體見(jiàn)英文：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.

l         read committed和serializable可以在ORACLE并行服務(wù)器中使用。

l          關(guān)于SET TRANSACTION READ WRITE：read write和read committed 應(yīng)該是一樣的。在讀方面，它們都避免了臟讀，但都無(wú)法實(shí)現(xiàn)重復(fù)讀。雖然沒(méi)有文檔說(shuō)明read write在寫(xiě)方面與read committed一致，但顯然它在寫(xiě)的時(shí)候會(huì)加排他鎖以避免更新丟失。在加鎖的過(guò)程中，如果遇到待鎖定資源無(wú)法鎖定，應(yīng)該是等待而不是放棄。這與read committed一致。

l         語(yǔ)句級(jí)的讀一致性

l          ORACLE保證語(yǔ)句級(jí)的讀一致性，即一個(gè)語(yǔ)句所處理的數(shù)據(jù)集是在單一時(shí)間點(diǎn)上的數(shù)據(jù)集，這個(gè)時(shí)間點(diǎn)是這個(gè)語(yǔ)句開(kāi)始的時(shí)間。

l          一個(gè)語(yǔ)句看不見(jiàn)在它開(kāi)始執(zhí)行后提交的修改。

l          對(duì)于DML語(yǔ)句，它看不見(jiàn)由自己所做的修改，即DML語(yǔ)句看見(jiàn)的是它本身開(kāi)始執(zhí)行以前存在的數(shù)據(jù)。

l         事務(wù)級(jí)的讀一致性

l          事務(wù)級(jí)的讀一致性保證了可重復(fù)讀，并保證不會(huì)出現(xiàn)幻像。

l         設(shè)置隔離級(jí)別

l          設(shè)置一個(gè)事務(wù)的隔離級(jí)別

l         SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

l         SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

l         SET TRANSACTION READ ONLY;

l         設(shè)置增個(gè)會(huì)話的隔離級(jí)別

l         ALTER SESSION SET ISOLATION_LEVEL SERIALIZABLE;

l         ALTER SESSION SET ISOLATION_LEVEL READ COMMITTED;