ORACLE的隔離級別

ORACLE的隔離級別[@more@]

ORACLE的隔離級別

作者: Parrotao

隔離級別（isolation level）

l 隔離級別定義了事務與事務之間的隔離程度。

l 隔離級別與併發性是互為矛盾的：隔離程度越高，資料庫的併發性越差；隔離程度越低，資料庫的併發性越好。

l ANSI/ISO SQL92標準定義了一些資料庫操作的隔離級別：

l 未提交讀（read uncommitted）

l 提交讀（read committed）

l 重複讀（repeatable read）

l 序列化（serializable）

l 透過一些現象，可以反映出隔離級別的效果。這些現象有：

l 更新丟失（lost update）：當系統允許兩個事務同時更新同一資料是，發生更新丟失。

l 髒讀（dirty read）：當一個事務讀取另一個事務尚未提交的修改時，產生髒讀。

l 非重複讀（nonrepeatable read）：同一查詢在同一事務中多次進行，由於其他提交事務所做的修改或刪除，每次返回不同的結果集，此時發生非重複讀。(A transaction rereads data it has previously read and finds that another committed transaction has modified or deleted the data. )

l 幻像（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 additional rows that satisfy the condition. )

l 下面是隔離級別及其對應的可能出現或不可能出現的現象

	Dirty Read	NonRepeatable Read	Phantom Read
Read uncommitted	Possible	Possible	Possible
Read committed	Not possible	Possible	Possible
Repeatable read	Not possible	Not possible	Possible
Serializable	Not possible	Not possible	Not possible

ORACLE的隔離級別

l ORACLE提供了SQL92標準中的read committed和serializable，同時提供了非SQL92標準的read-only。

l read committed：

l 這是ORACLE預設的事務隔離級別。

l 事務中的每一條語句都遵從語句級的讀一致性。

l 保證不會髒讀；但可能出現非重複讀和幻像。

l serializable：

l 簡單地說，serializable就是使事務看起來象是一個接著一個地順序地執行。

l 僅僅能看見在本事務開始前由其它事務提交的更改和在本事務中所做的更改。

l 保證不會出現非重複讀和幻像。

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

l 如果有在serializable事務開始時未提交的事務在serializable事務結束之前修改了serializable事務將要修改的行並進行了提交，則serializable事務不會讀到這些變更，因此發生無法序列化訪問的錯誤。（換一種解釋方法：只要在serializable事務開始到結束之間有其他事務對serializable事務要修改的東西進行了修改並提交了修改，則發生無法序列化訪問的錯誤。）

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, （並且修改在後來被提交而沒有回滾），then the DML statement fails. 返回的錯誤是ORA-08177: Cannot serialize access for this transaction。

l ORACLE在資料塊中記錄最近對資料行執行修改操作的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 遵從事務級的讀一致性，僅僅能看見在本事務開始前由其它事務提交的更改。

l 不允許在本事務中進行DML操作。

l read only是serializable的子集。它們都避免了非重複讀和幻像。區別是在read only中是隻讀；而在serializable中可以進行DML操作。

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

l read committed和serializable的區別和聯絡：

l 事務1先於事務2開始，並保持未提交狀態。事務2想要修改正被事務1修改的行。事務2等待。如果事務1回滾，則事務2（不論是read committed還是serializable方式）進行它想要做的修改。如果事務1提交，則當事務2是read committed方式時，進行它想要做的修改；當事務2是serializable方式時，失敗並報錯“Cannot serialize access”，因為事務2看不見事務1提交的修改，且事務2想在事務一修改的基礎上再做修改。具體見英文：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並行伺服器中使用。

l 關於SET TRANSACTION READ WRITE：read write和read committed 應該是一樣的。在讀方面，它們都避免了髒讀，但都無法實現重複讀。雖然沒有文件說明read write在寫方面與read committed一致，但顯然它在寫的時候會加排他鎖以避免更新丟失。在加鎖的過程中，如果遇到待鎖定資源無法鎖定，應該是等待而不是放棄。這與read committed一致。

l 語句級的讀一致性

l ORACLE保證語句級的讀一致性，即一個語句所處理的資料集是在單一時間點上的資料集，這個時間點是這個語句開始的時間。

l 一個語句看不見在它開始執行後提交的修改。

l 對於DML語句，它看不見由自己所做的修改，即DML語句看見的是它本身開始執行以前存在的資料。

l 事務級的讀一致性

l 事務級的讀一致性保證了可重複讀，並保證不會出現幻像。

l 設定隔離級別

l 設定一個事務的隔離級別

l SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

l SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

l SET TRANSACTION READ ONLY;

l 設定增個會話的隔離級別

l ALTER SESSION SET ISOLATION_LEVEL SERIALIZABLE;

l ALTER SESSION SET ISOLATION_LEVEL READ COMMITTED;