oracle中鎖的類型：
然后再讀時不需要加鎖，這一點Oracle的共享鎖的實現(xiàn)與上一篇中的共享鎖原理有點不同，

今天在查看oracle官方文檔lock的那一部分的時候發(fā)現(xiàn)一個新的概念，isolation level (數(shù)據(jù)隔離級別)，雖然以前學(xué)過，但是忘的差不多了。
隔離級別（isoation eve）

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

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

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

• 未提交讀（read uncommitted）
• 提交讀（read committed）  
• 重復(fù)讀（repeatabe read）  
• 序列化（seriaizabe）

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

• 更新丟失（ost update）：當(dāng)系統(tǒng)允許兩個事務(wù)同時更新同一數(shù)據(jù)是，發(fā)生更新丟失。  
• 臟讀（dirty read）：當(dāng)一個事務(wù)讀取另一個事務(wù)尚未提交的修改時，產(chǎn)生臟讀。
• 非 重復(fù)讀（nonrepeatabe read）：同一查詢在同一事務(wù)中多次進(jìn)行，由于其他提交事務(wù)所做的修改或刪除，每次返回不同的結(jié)果集，此時發(fā)生非重復(fù)讀。(A transaction rereads data it has previousy read and finds that another committed transaction has modified or deeted the data. )
• 幻 像（phantom read）：同一查詢在同一事務(wù)中多次進(jìn)行，由于其他提交事務(wù)所做的插入操作，每次返回不同的結(jié)果集，此時發(fā)生幻像讀。(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. )

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

	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

 

ORACE的隔離級別

ORACE提供了SQ92標(biāo)準(zhǔn)中的read committed和seriaizabe，同時提供了非SQ92標(biāo)準(zhǔn)的read-ony。

read committed：

• 這是ORACE缺省的事務(wù)隔離級別。
• 事務(wù)中的每一條語句都遵從語句級的讀一致性。
• 保證不會臟讀；但可能出現(xiàn)非重復(fù)讀和幻像。

seriaizabe：（串行執(zhí)行事務(wù)，并發(fā)性最小）

• 簡單地說，seriaizabe就是使事務(wù)看起來象是一個接著一個地順序地執(zhí)行。
• 僅僅能看見在本事務(wù)開始前由其它事務(wù)提交的更改和在本事務(wù)中所做的更改。
• 保證不會出現(xiàn)非重復(fù)讀和幻像。
• Seriaizabe隔離級別提供了read-ony事務(wù)所提供的讀一致性（事務(wù)級的讀一致性），同時又允許DM操作。

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

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。

ORACE在數(shù)據(jù)塊中記錄最近對數(shù)據(jù)行執(zhí)行修改操作的N個事務(wù)的信息，目的是確定本事務(wù)開始時，是否存在未提交的事務(wù)修改了本事務(wù)將要修改的行。具體見英文：

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：

• 遵從事務(wù)級的讀一致性，僅僅能看見在本事務(wù)開始前由其它事務(wù)提交的更改。
• 不允許在本事務(wù)中進(jìn)行DM操作。
• read ony是seriaizabe的子集。它們都避免了非重復(fù)讀和幻像。區(qū)別是在read ony中是只讀；而在seriaizabe中可以進(jìn)行DM操作。
• Export with CONSISTENT = Y sets the transaction to read-ony.

 

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

事務(wù)1先于事務(wù)2開始，并保持未提交狀態(tài)。事務(wù)2想要修改正被事務(wù)1修改的行。事務(wù)2等待。如果事務(wù)1回滾，則事務(wù)2（不論是read committed還是seriaizabe方式）進(jìn)行它想要做的修改。如果事務(wù)1提交，則當(dāng)事務(wù)2是read committed方式時，進(jìn)行它想要做的修改；當(dāng)事務(wù)2是seriaizabe方式時，失敗并報錯“Cannot seriaize access”，因為事務(wù)2看不見事務(wù)1提交的修改，且事務(wù)2想在事務(wù)1修改的基礎(chǔ)上再做修改。

即seriaizabe不允許存在事務(wù)嵌套

具體見英文：

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并行服務(wù)器中使用。

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

語句級的讀一致性

• ORACE保證語句級的讀一致性，即一個語句所處理的數(shù)據(jù)集是在單一時間點上的數(shù)據(jù)集，這個時間點是這個語句開始的時間。
• 一個語句看不見在它開始執(zhí)行后提交的修改。
• 對于DM語句，它看不見由自己所做的修改，即DM語句看見的是它本身開始執(zhí)行以前存在的數(shù)據(jù)。

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

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

設(shè)置隔離級別

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

• SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
• SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
• SET TRANSACTION READ ONLY;

設(shè)置增個會話的隔離級別

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

兩種情況：（1）在事務(wù)量大、高性能的計算環(huán)境，需要更高的吞吐量和響應(yīng)時間；（2）事務(wù)數(shù)少，并且發(fā)生幻影和不可重復(fù)讀的幾率的比較低

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.

Serializable Isolation

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.

（1）適合于很少存在兩個事務(wù)同時修改同一條記錄的情況

（2）長事務(wù)以只讀為主

（3）大型數(shù)據(jù)庫并且每個短事務(wù)只修改很少的記錄

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 READ ONLY transaction provides, but also allows INSERT, UPDATE, and DELETE.