資料庫隔離級別—MySQL的預設隔離級別就是Repeatable,Oracle預設Read committed,最高階別Serializable

資料庫隔離級別—MySQL的預設隔離級別就是Repeatable,Oracle預設Read committed,最高階別Serializable

資料庫事務的隔離級別有4個,由低到高依次為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是最高的事務隔離級別,同時代價也花費最高,效能很低,一般很少使用,在該級別下,事務順序執行,不僅可以避免髒讀、不可重複讀,還避免了幻像讀。

………

READ UNCOMMITTED

READ UNCOMMITTED是限制性最弱的隔離級別,因為該級別忽略其他事務放置的鎖。使用READ UNCOMMITTED級別執行的事務,可以讀取尚未由其他事務提交的修改後的資料值,這些行為稱為“髒”讀。這是因為在Read Uncommitted級別下,讀取資料不需要加S鎖,這樣就不會跟被修改的資料上的X鎖衝突。比如,事務1修改一行,事務2在事務1提交之前讀取了這一行。如果事務1回滾,事務2就讀取了一行沒有提交的資料,這樣的資料我們認為是不存在的。

READ COMMITTED

READ COMMITTED(Nonrepeatable reads)是SQL Server預設的隔離級別。該級別通過指定語句不能讀取其他事務已修改但是尚未提交的資料值,禁止執行髒讀。在當前事務中的各個語句執行之間,其他事務仍可以修改、插入或刪除資料,從而產生無法重複的讀操作,或“影子”資料。比如,事務1讀取了一行,事務2修改或者刪除這一行並且提交。如果事務1想再一次讀取這一行,它將獲得修改後的資料或者發現這一樣已經被刪除,因此事務的第二次讀取結果與第一次讀取結果不同,因此也叫不可重複讀。

實驗1
query1:事務1

–step1:建立實驗資料
select * into Employee from AdventureWorks.HumanResources.Employee
alter table Employee add constraint pk_Employee_EmployeeID primary key(EmployeeID)
–step2:設定隔離級別,這是資料庫的預設隔離界別
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
–step3:開啟第一個事務
BEGIN TRAN tran1
–step4:執行select操作,檢視VacationHours,對查詢的記錄加S鎖,在語句執行完以後自動釋放S鎖
SELECT EmployeeID, VacationHours
FROM Employee
WHERE EmployeeID = 4;
–step5:檢視當前加鎖情況,沒有發現在Employee表上面有鎖,這是因為當前的隔離界別是READ COMMITTED
–在執行完step2以後馬上釋放了S鎖.
SELECT request_session_id, resource_type, resource_associated_entity_id,
request_status, request_mode, resource_description
FROM sys.dm_tran_locks
檢視鎖的情況如下圖所示,我們發現在只有在資料庫級別的S鎖,而沒有在表級別或者更低階別的鎖,這是因為在Read Committed級別下,S鎖在語句執行完以後就被釋放。

query2:事務2

–step6:開啟第二個事務
BEGIN TRAN tran2;
–step7:修改VacationHours,需要獲得排它鎖X,在VacationHours上沒有有S鎖
UPDATE Employee
SET VacationHours = VacationHours – 8
WHERE EmployeeID = 4;
–step8:檢視當前加鎖情況
SELECT request_session_id, resource_type, resource_associated_entity_id,
request_status, request_mode, resource_description
FROM sys.dm_tran_locks
在開啟另外一個update事務以後,我們再去檢視當前的鎖狀況,如下圖所示,我們發現在表(Object)級別上加了IX鎖,在這張表所在的Page上也加了IX鎖,因為表加了聚集索引,所以在葉子結點上加了X鎖,這個鎖的型別是KEY。

然後我們回到事務1當中再次執行查詢語句,我們會發現查詢被阻塞,我們新建一個查詢query3來檢視這個時候的鎖狀況,其查詢結果如下,我們可以發現查詢操作需要在KEY級別上申請S鎖,在Page和表(Object)上面申請IS鎖,但是因為Key上面原先有了X鎖,與當前讀操作申請的S鎖衝突,所以這一步處於WAIT狀態。

如果此時提交事務2的update操作,那麼事務1的select操作不再被阻塞,得到查詢結果,但是我們發現此時得到的查詢結果與第一次得到的查詢結果不同,這也是為什麼將read committed稱為不可重複讀,因為同一個事物內的兩次相同的查詢操作的結果可能不同。

REPEATABLE READ

REPEATABLE READ是比READ COMMITTED限制性更強的隔離級別。該級別包括READ COMMITTED,並且另外指定了在當前事務提交之前,其他任何事務均不可以修改或刪除當前事務已讀取的資料。併發性低於 READ COMMITTED,因為已讀資料的共享鎖在整個事務期間持有,而不是在每個語句結束時釋放。比如,事務1讀取了一行,事務2想修改或者刪除這一行並且提交,但是因為事務1尚未提交,資料行中有事務1的鎖,事務2無法進行更新操作,因此事務2阻塞。如果這時候事務1想再一次讀取這一行,它讀取結果與第一次讀取結果相同,因此叫可重複讀。

實驗2
query1:事務1

–step1:建立實驗資料
select * into Employee from AdventureWorks.HumanResources.Employee
alter table Employee add constraint pk_Employee_EmployeeID primary key(EmployeeID)
–step2:設定隔離級別
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
–step3:開啟第一個事務
BEGIN TRAN tran1
–step4:執行select操作,檢視VacationHours
SELECT EmployeeID, VacationHours
FROM Employee
WHERE EmployeeID = 4;
–step5:檢視當前加鎖情況,發現在Employee表上面有S鎖,這是因為當前的隔離界別是REPEATABLE READ
–S鎖只有在事務執行完以後才會被釋放.
SELECT request_session_id, resource_type, resource_associated_entity_id,
request_status, request_mode, resource_description
FROM sys.dm_tran_locks
查詢鎖狀態的結果如下圖所示,我們發現在KEY上面加了S鎖,在Page和Object上面加了IS鎖,這是因為在Repeatable Read級別下S鎖要在事務執行完以後才會被釋放。

query2:事務2

–step6:開啟第二個事務
BEGIN TRAN tran2;
–step7:修改VacationHours,需要獲得排他鎖X,在VacationHours上有S鎖,出現衝突,所以update操作被阻塞
UPDATE Employee
SET VacationHours = VacationHours – 8
WHERE EmployeeID = 4;
執行上述update操作的時候發現該操作被阻塞,這是因為update操作要加排它鎖X,而因為原先的查詢操作的S鎖沒有釋放,所以兩者衝突。我們新建一個查詢3執行查詢鎖狀態操作,發現結果如下圖所示,我們可以發現是WAIT發生在對KEY加X鎖的操作上面。

此時再次執行查詢1中的select操作,我們發現查詢結果跟第一次相同,所以這個叫做可重複讀操作。但是可重複讀操作並不是特定指兩次讀取的資料一模一樣,Repeatable Read存在的一個問題是幻讀,就是第二次讀取的資料返回的條目數比第一次返回的條目數更多。

比如在Repeatable Read隔離級別下,事務1第一次執行查詢select id from users where id>1 and id <10,返回的結果是2,4,6,8。這個時候事務1沒有提交,那麼對2,4,6,8上面依然保持有S鎖。此時事務2執行一次插入操作insert into user(id) valuse(3),插入成功。此時再次執行事務1中的查詢,那麼返回結果就是2,3,4,6,8。這裡的3就是因為幻讀而出現的。因此可以得出結論:REPEATABLE READ隔離級別保證了在相同的查詢條件下,同一個事務中的兩個查詢,第二次讀取的內容肯定包換第一次讀到的內容。

SERIALIZABLE

SERIALIZABLE 是限制性最強的隔離級別,因為該級別鎖定整個範圍的鍵,並一直持有鎖,直到事務完成。該級別包括REPEATABLE READ,並增加了在事務完成之前,其他事務不能向事務已讀取的範圍插入新行的限制。比如,事務1讀取了一系列滿足搜尋條件的行。事務2在執行SQL statement產生一行或者多行滿足事務1搜尋條件的行時會衝突,則事務2回滾。這時事務1再次讀取了一系列滿足相同搜尋條件的行,第二次讀取的結果和第一次讀取的結果相同。

重複讀與幻讀

重複讀是為了保證在一個事務中,相同查詢條件下讀取的資料值不發生改變,但是不能保證下次同樣條件查詢,結果記錄數不會增加。

幻讀就是為了解決這個問題而存在的,他將這個查詢範圍都加鎖了,所以就不能再往這個範圍內插入資料,這就是SERIALIZABLE 隔離級別做的事情。

隔離級別與鎖的關係

在Read Uncommitted級別下,讀操作不加S鎖;
在Read Committed級別下,讀操作需要加S鎖,但是在語句執行完以後釋放S鎖;
在Repeatable Read級別下,讀操作需要加S鎖,但是在事務提交之前並不釋放S鎖,也就是必須等待事務執行完畢以後才釋放S鎖。
在Serialize級別下,會在Repeatable Read級別的基礎上,新增一個範圍鎖。保證一個事務內的兩次查詢結果完全一樣,而不會出現第一次查詢結果是第二次查詢結果的子集。

轉自:https://blog.csdn.net/baidu_37107022/article/details/77481670