資料庫的隔離等級，資料庫隔離等級

資料庫的隔離等級，資料庫隔離等級

資料庫事務的隔離等級有4個，由低到高依次為Read uncommitted、Read committed、Repeatable read、Serializable，這四個層級可以逐個解決髒讀、不可重複讀取、幻讀這幾類問題。

√: 可能出現    ×: 不會出現

	髒讀	不可重複讀取	幻讀
Read uncommitted	√	√	√
Read committed	×	√	√
Repeatable read	×	×	√
Serializable	×	×	×

 

注意：我們討論隔離等級的情境，主要是在多個事務並發的情況下，因此，接下來的講解都圍繞事務並發。

Read uncommitted 讀未提交

公司發工資了，領導把5000元打到singo的帳號上，但是該事務並未提交，而singo正好去查看賬戶，發現工資已經到賬，是5000元整，非常高興。可是不幸的是，領導發現發給singo的工資金額不對，是2000元，於是迅速復原了事務，修改金額後，將事務提交，最後singo實際的工資只有2000元，singo空歡喜一場。

 

出現上述情況，即我們所說的髒讀，兩個並發的事務，“事務A：領導給singo發工資”、“事務B：singo查詢工資賬戶”，事務B讀取了事務A尚未提交的資料。

當隔離等級設定為Read uncommitted時，就可能出現髒讀，如何避免髒讀，請看下一個隔離等級。

Read committed 讀提交

singo拿著工資卡去消費，系統讀取到卡裡確實有2000元，而此時她的老婆也正好在網上轉賬，把singo工資卡的2000元轉到另一賬戶，並在singo之前提交了事務，當singo扣款時，系統檢查到singo的工資卡已經沒有錢，扣款失敗，singo十分納悶，明明卡裡有錢，為何......

出現上述情況，即我們所說的不可重複讀取，兩個並發的事務，“事務A：singo消費”、“事務B：singo的老婆網上轉賬”，事務A事先讀取了資料，事務B緊接了更新了資料，並提交了事務，而事務A再次讀取該資料時，資料已經發生了改變。

當隔離等級設定為Read committed時，避免了髒讀，但是可能會造成不可重複讀取。

大多數資料庫的預設層級就是Read committed，比如Sql Server , Oracle。如何解決不可重複讀取這一問題，請看下一個隔離等級。

Repeatable read 重複讀

當隔離等級設定為Repeatable read時，可以避免不可重複讀取。當singo拿著工資卡去消費時，一旦系統開始讀取工資卡資訊（即事務開始），singo的老婆就不可能對該記錄進行修改，也就是singo的老婆不能在此時轉賬。

雖然Repeatable read避免了不可重複讀取，但還有可能出現幻讀。

singo的老婆工作在銀行部門，她時常通過銀行內部系統查看singo的信用卡消費記錄。有一天，她正在查詢到singo當月信用卡的總消費金額（select sum(amount) from transaction where month = 本月）為80元，而singo此時正好在外面胡吃海塞後在收銀台買單，消費1000元，即新增了一條1000元的消費記錄（insert transaction ... ），並提交了事務，隨後singo的老婆將singo當月信用卡消費的明細列印到A4紙上，卻發現消費總額為1080元，singo的老婆很詫異，以為出現了幻覺，幻讀就這樣產生了。

註：Mysql的預設隔離等級就是Repeatable read。

Serializable 序列化

Serializable是最高的交易隔離等級，同時代價也花費最高，效能很低，一般很少使用，在該層級下，事務順序執行，不僅可以避免髒讀、不可重複讀取，還避免了幻像讀。

[sql] view plaincopy

1. 對於同時啟動並執行多個事務, 當這些事務訪問資料庫中相同的資料時, 如果沒有採取必要的隔離機制, 就會導致各種並發問題:  
2. •   髒讀: 對於兩個事物 T1, T2, T1 讀取了已經被 T2 更新但還沒有被提交的欄位. 之後, 若 T2 復原, T1讀取的內容就是臨時且無效的.  
3. •   不可重複讀取: 對於兩個事物 T1, T2, T1 讀取了一個欄位, 然後 T2 更新了該欄位. 之後, T1再次讀取同一個欄位, 值就不同了.  
4. •   幻讀: 對於兩個事物 T1, T2, T1 從一個表中讀取了一個欄位, 然後 T2 在該表中插入了一些新的行. 之後, 如果 T1 再次讀取同一個表, 就會多出幾行.  
5. 資料庫事務的隔離性: 資料庫系統必須具有隔離並發運行各個事務的能力, 使它們不會相互影響, 避免各種並發問題.   
6. 一個事務與其他事務隔離的程度稱為隔離等級. 資料庫規定了多種交易隔離等級, 不同隔離等級對應不同的幹擾程度, 隔離等級越高, 資料一致性就越好, 但並發性越弱  
7. 資料庫提供了4中隔離等級：  
8. 隔離等級    描述  
9. READ UNCOMMITTED(讀未提交資料)    允許事務讀取未被其他事務提交的變更，髒讀、不可重複讀取和幻讀的問題都會出現  
10. READ COMMITED(讀已提交資料)   只允許事務讀取已經被其他事務提交的變更，可以避免髒讀，但不可重複讀取和幻讀問題仍然會出現  
11. REPEATABLE READ(可重複讀)   確保事務可以多次從一個欄位中讀取相同的值，在這個事務持續期間，禁止其他事務對這個欄位進行更新，可以避免髒讀和不可重複讀取，但幻讀的問題依然存在  
12.   
13. SERIALIZABLE(序列化)   確保事務可以從一個表中讀取相同的行，在這個事務持續期間，禁止其他事務對該表執行插入、更新和刪除操作，所有並發問題都可以避免，但效能十分低  
14. Oracle 支援的 2 種交易隔離等級：READ COMMITED, SERIALIZABLE. Oracle 預設的交易隔離等級為: READ COMMITED   
15. Mysql 支援 4 中交易隔離等級. Mysql 預設的交易隔離等級為: REPEATABLE READ  

事務（transaction）是資料庫管理系統的執行單位，可以是一個資料庫操作（如Select操作）或者是一組操作序列。事務ACID屬性，即原子性（Atomicity）、一致性(Consistency)、隔離性（Isolation）、持久性（Durability）。

原子性：保證事務中的所有操作全部執行或全部不執行。例如執行轉賬事務，要麼轉賬成功，要麼失敗。成功，則金額從轉出帳戶轉入到目的帳戶，並且兩個帳戶金額將發生相應的變化；失敗，則兩個賬戶的金額都不變。不會出現轉出帳戶扣了錢，而目的帳戶沒有收到錢的情況。

一致性：保證資料庫始終保持資料的一致性——事務操作之前是一致的，事務操作之後也是一致的，不管事務成功與否。如上面的例子，轉賬之前和之後資料庫都保持資料上的一致性。

隔 離性：多個事務並發執行的話，結果應該與多個事務串列執行效果是一樣的。顯然最簡單的隔離就是將所有事務都串列執行：先來先執行，一個事務執行完了才允許 執行下一個。但這樣資料庫的效率低下，如：兩個不同的事務只是讀取同一批資料，這樣完全可以並發進行。為了控制並發執行的效果就有了不同的隔離等級。下面 將詳細介紹。

持久性：持久性表示事物操作完成之後，對資料庫的影響是持久的，即使資料庫因故障而受到破壞，資料庫也應該能夠恢複。通常的實現方式是採用日誌。

 

交易隔離等級（transaction isolation levels）：隔離等級就是對對事務並發控制的等級。ANSI/ ISO SQL將其分為序列化（SERIALIZABLE）、可重複讀（REPEATABLE READ）、讀已提交（READ COMMITED）、讀未提交（READ UNCOMMITED）四個等級。為了實現隔離等級通常資料庫採用鎖（Lock）。一般在編程的時候只需要設定隔離等級，至於具體採用什麼鎖則由資料庫來設定。首先介紹四種等級，然後舉例解釋後面三個等級（可重複讀、讀已提交、讀未提交）中會出現的並發問題。

序列化（SERIALIZABLE）：所有事務都一個接一個地串列執行，這樣可以避免幻讀（phantom reads）。對於基於鎖來實現並發控制的資料庫來說，序列化要求在執行範圍查詢（如選取年齡在10到30之間的使用者）的時候，需要擷取範圍鎖（range lock）。如果不是基於鎖實現並發控制的資料庫，則檢查到有違反串列操作的事務時，需要滾回該事務。

可重複讀（REPEATABLE READ）：所有被Select擷取的資料都不能被修改，這樣就可以避免一個事務前後讀取資料不一致的情況。但是卻沒有辦法控制幻讀，因為這個時候其他事務不能更改所選的資料，但是可以增加資料，因為前一個事務沒有範圍鎖。

讀已提交（READ COMMITED）：被讀取的資料可以被其他事務修改。這樣就可能導致不可重複讀取。也就是說，事務的讀取資料的時候擷取讀鎖，但是讀完之後立即釋放（不需要等到事務結束），而寫鎖則是事務提交之後才釋放。釋放讀鎖之後，就可能被其他事物修改資料。該等級也是SQL Server預設的隔離等級。

讀未提交（READ UNCOMMITED）：這是最低的隔離等級，允許其他事務看到沒有提交的資料。這種等級會導致髒讀（Dirty Read）。

 

       例子：下面考察後面三種隔離等級對應的並發問題。假設有兩個事務。事務1執行查詢1，然後事務2執行查詢2，然後提交，接下來事務1中的查詢1再執行一次。查詢基於以下表進行：

users
id	name	age
1	Joe	20
2	Jill	25

可重複讀(幻讀，phantom reads)

一個事務中先後各執行一次同一個查詢，但是返回的結果集卻不一樣。發生這種情況是因為在執行Select操作的時候沒有擷取範圍鎖（Range Lock），導致其他事務仍然可以插入新的資料。

Transaction 1	Transaction 2
SELECT * FROM users WHERE age BETWEEN 10 AND 30;
	INSERT INTO users VALUES ( 3, 'Bob', 27 ); COMMIT;
SELECT * FROM users WHERE age BETWEEN 10 AND 30;

注意transaction 1對同一個查詢語句（Query 1）執行了兩次。 如果採用更進階別的隔離等級（即序列化）的話，那麼前後兩次查詢應該返回同樣的結果集。但是在可重複讀隔離等級中卻前後兩次結果集不一樣。但是為什麼叫做可重複讀等級呢？那是因為該等級解決了下面的不可重複讀取問題。

讀已提交（不可重複讀取，Non-repeatable reads）

在採用鎖來實現並發控制的資料庫系統中，不可重複讀取是因為在執行Select操作的時候沒有加讀鎖（read lock）。

Transaction 1	Transaction 2
SELECT * FROM users WHERE id = 1;
	UPDATE users SET age = 21 WHERE id = 1; COMMIT;
SELECT * FROM users WHERE id = 1;

在這個例子當中，Transaction 2提交成功,所以Transaction 1第二次將擷取一個不同的age 值.在SERIALIZABLE和REPEATABLE READ隔離等級中,資料庫應該返回同一個值。而在READ COMMITTED和READ UNCOMMITTED層級中資料庫返回更新的值。這樣就出現了不可重複讀取。

讀未提交 (髒讀，dirty reads)

如果一個事務2讀取了另一個事務1修改的值，但是最後事務1滾回了，那麼事務2就讀取了一個髒資料，這也就是所謂的髒讀。發生這種情況就是允許事務讀取未提交的更新。

Transaction 1	Transaction 2
SELECT * FROM users WHERE id = 1;
	UPDATE users SET age = 21 WHERE id = 1;
SELECT * FROM users WHERE id = 1;
	RollBack

 

綜上述，可以等到下面的表格：

隔離等級	髒讀	不可重複讀取	幻讀
讀未提交	YES	YES	YES
讀已提交	NO	YES	YES
可重複讀	NO	NO	YES
序列化	NO	NO	NO

著作權聲明：本文為博主原創文章，未經博主允許不得轉載。