資料庫的四種隔離級別

AlbenXie發表於2018-08-20

前言

首先要推薦這個資料庫的文章,感覺寫的真好: 
如果有人問你資料庫的原理,叫他看這篇文章


簡介

引用原文中關於資料庫四種隔離級別的介紹。

現代資料庫不會使用純粹的隔離作為預設模式,因為它會帶來巨大的效能消耗。SQL一般定義4個隔離級別:

  • 序列化(Serializable,SQLite預設模式):最高階別的隔離。兩個同時發生的事務100%隔離,每個事務有自己的『世界』。

  • 可重複讀(Repeatable read,MySQL預設模式):每個事務有自己的『世界』,除了一種情況。如果一個事務成功執行並且新增了新資料,這些資料對其他正在執行的事務是可見的。但是如果事務成功修改了一條資料,修改結果對正在執行的事務不可見。所以,事務之間只是在新資料方面突破了隔離,對已存在的資料仍舊隔離。 
    舉個例子,如果事務A執行”SELECT count(1) from TABLE_X” ,然後事務B在 TABLE_X 加入一條新資料並提交,當事務A再執行一次 count(1)結果不會是一樣的。 
    這叫幻讀(phantom read)。

  • 讀取已提交(Read committed,Oracle、PostgreSQL、SQL Server預設模式):可重複讀+新的隔離突破。如果事務A讀取了資料D,然後資料D被事務B修改(或刪除)並提交,事務A再次讀取資料D時資料的變化(或刪除)是可見的。 
    這叫不可重複讀(non-repeatable read)。

  • 讀取未提交(Read uncommitted):最低階別的隔離,是讀取已提交+新的隔離突破。如果事務A讀取了資料D,然後資料D被事務B修改(但並未提交,事務B仍在執行中),事務A再次讀取資料D時,資料修改是可見的。如果事務B回滾,那麼事務A第二次讀取的資料D是無意義的,因為那是事務B所做的從未發生的修改(已經回滾了嘛)。 
    這叫髒讀(dirty read)。

多數資料庫新增了自定義的隔離級別(比如 PostgreSQL、Oracle、SQL Server的快照隔離),而且並沒有實現SQL規範裡的所有級別(尤其是讀取未提交級別)。

預設的隔離級別可以由使用者/開發者在建立連線時覆蓋(只需要增加很簡單的一行程式碼)。


Oracle支援兩種事務隔離級別:READ COMMITTED(預設事務隔離級別),SERIALIZABLE。

MySQL支援四種事務隔離級別,其中REPEATABLE READ為預設事務隔離級別。

 

實驗

看到這個介紹簡直也是一頭霧水。直接做實驗來理解吧。


0#準備工作

建立一個balance表,裡面有一列資料money,我們們就玩id=1這一行的money,初始值為2960。

然後開啟兩個遠端登入的視窗,都開啟MySQL資料庫,開始實驗。

從隔離級別低到高,分別是:讀取未提交、讀取已提交、可重複讀、序列化。


1#讀取未提交

  • 檢視視窗1的會話隔離級別,發現MySQL的預設隔離級別是Repeatable read。
select @@tx_isolation;
  •  

MySQL預設隔離級別

  • 設定會話隔離級別為實驗的read uncommitted:
set session transaction isolation level read uncommitted;

設定隔離級別

  • 在視窗1開始一個事務並檢視money。
-- 視窗1
begin;
select money from balance where id = 1;

這裡寫圖片描述

  • 然後在視窗2開始一個事務並修改money。
-- 視窗2
begin;
update balance set money = money - 1000 where id = 1;
select money from balance where id = 1;

這裡寫圖片描述

可見在視窗2這個事務中money已經被修改,但是還沒提交。

  • 回到視窗1,再檢視money
-- 視窗1
select money from balance where id = 1;

這裡寫圖片描述

可見,雖然視窗2的事務還沒提交,但是視窗1的事務已經可以讀到還沒提交的資料,所以這就叫做 讀取未提交 。可以看到,兩個事務的隔離性很低,這是四種隔離級別中最低的級別。

  • 那麼,如果視窗2的事務發生錯誤,將資料回滾,money變回原來的值,實際上money不應該發生變化,可是我們們的視窗1的事務還是讀到了 錯誤 的 回滾前 的1960,這就叫 髒讀 。
-- 視窗2
rollback;
select money from balance where id = 1;
commit;

這裡寫圖片描述

money變回2960了。

  • 所以,如果視窗2的事務代表轉賬,money從2960轉走1000變為1960,然後轉賬出錯,回滾回2960。
  • 資料庫的隔離級別如果是read uncommitted的話,其他的事務(視窗1 的事務)就有可能再中間讀到1960這個錯誤值。這就叫 髒讀啊親。

2#讀取已提交

  • 先將視窗1的事務結束掉(commit),然後設定隔離級別為read committed。再開始一個新事務,讀取money。
-- 視窗1
commit;
set session transaction isolation level read committed;
begin;
select money from balance where id = 1;

這裡寫圖片描述

  • 視窗2開始新事務修改money
-- 視窗2
begin;
update balance set money = money - 1000;
select money from balance where id = 1;

這裡寫圖片描述

  • 回到視窗1,再讀money
-- 視窗1
select money from balance where id = 1;

這裡寫圖片描述

  • 哈哈,這回視窗1中的money沒被修改了吧。

  • 然後將視窗2的修改提交。

-- 視窗2
commit;
select money from balance where id = 1;

這裡寫圖片描述

  • 回到視窗1再讀money,可以讀到已提交的money了。
-- 視窗1
select money from balance where id = 1;
commit;

這裡寫圖片描述

  • 視窗1的事務可以讀到視窗2的已提交的事務,這就叫 讀取已提交 。

3#可重複讀

  • 所以如果要再提高隔離性,那是怎麼樣呢?那就是視窗2的事務就算提交了資料修改,我視窗1的事務也不管,還是讀取到原來的資料。
-- 視窗1
-- 設定隔離級別為repeatable read
set session transaction isolation level repeatable read;
begin;
select money from balance where id = 1;

這裡寫圖片描述

-- 視窗2
-- 修改money並提交
begin;
update balance set money = money - 1000;
select money from balance where id = 1;
commit;

這裡寫圖片描述

-- 視窗1
-- 視窗1的事務還是視而不見。
select money from balance where id = 1;

這裡寫圖片描述

  • 視窗2的事務已經提交了,資料庫的資料money已經真正被修改了,可是視窗1的事務還是視而不見,仍然隔離了,讀取的數值仍然不變,重複讀的數值不會變,這就是 可重複讀 。

4#序列化

  • 難道還有更變態的更強的隔離級別,答案是肯定的,那就是 序列化 。序列化是怎麼再增強隔離性的呢?回到一開始文章中對 可重複讀 和 序列化 的解釋。
  • 可重複讀:

    每個事務有自己的『世界』,除了一種情況。如果一個事務成功執行並且新增了新資料,這些資料對其他正在執行的事務是可見的。但是如果事務成功修改了一條資料,修改結果對正在執行的事務不可見。所以,事務之間只是在新資料方面突破了隔離,對已存在的資料仍舊隔離。

  • 序列化:

    最高階別的隔離。兩個同時發生的事務100%隔離,每個事務有自己的『世界』。

  • 也就是說,如果是在可重複讀的情況下,插入新資料這個事情是沒有被隔離的,但是在序列化的情況下,插入新資料也被隔離了。好吧,還是很抽象,還是實驗最好。

-- 視窗1
-- 先是繼續用可重複讀的隔離級別。
begin;
select count(*) from balance;

這裡寫圖片描述

好的,表裡有579條資料。然後我們在視窗2的事務中插入新資料。

-- 視窗2
-- 在視窗2的事務中插入新資料。
begin;
insert into balance (money) values (999);
commit;
select count(*) from balance;

這裡寫圖片描述

好了,資料表中的記錄數已經達到了580條。那麼回到視窗1,按照可重複讀的定義,應該是580條記錄,然後序列化的設定才是579條記錄。可是! 
這裡寫圖片描述

  • 真是萬萬沒想到啊,這不是打自己臉嗎,啪啪響。上網搜了一下,找到這麼一句話:

REPEATABLE READ:在mysql中,不會出現幻讀。mysql的實現和標準定義的RR隔離級別有差別。

好吧有興趣的同學可以看看這位大神的詳細解釋:MySQL_REPEATABLE-READ事務隔離級別 && 幻讀

相關文章