面試突擊61:說一下MySQL事務隔離級別?

語言: CN / TW / HK

持續創作,加速成長!這是我參與「掘金日新計劃 · 6 月更文挑戰」的第13天,點選檢視活動詳情

MySQL 事務隔離級別是為了解決併發事務互相干擾的問題的,MySQL 事務隔離級別總共有以下 4 種:

  1. READ UNCOMMITTED:讀未提交。
  2. READ COMMITTED:讀已提交。
  3. REPEATABLE READ:可重複讀。
  4. SERIALIZABLE:序列化。

1.四種事務隔離級別

1.1 READ UNCOMMITTED

讀未提交,也叫未提交讀,該隔離級別的事務可以看到其他事務中未提交的資料。該隔離級別因為可以讀取到其他事務中未提交的資料,而未提交的資料可能會發生回滾,因此我們把該級別讀取到的資料稱之為髒資料,把這個問題稱之為髒讀。

1.2 READ COMMITTED

讀已提交,也叫提交讀,該隔離級別的事務能讀取到已經提交事務的資料,因此它不會有髒讀問題。但由於在事務的執行中可以讀取到其他事務提交的結果,所以在不同時間的相同 SQL 查詢中,可能會得到不同的結果,這種現象叫做不可重複讀。

1.3 REPEATABLE READ

可重複讀,MySQL 預設的事務隔離級別。可重複讀可以解決“不可重複讀”的問題,但還存在幻讀的問題。所謂的幻讀指的是,在同一事務的不同時間使用相同 SQL 查詢時,會產生不同的結果。例如,一個 SELECT 被執行了兩次,但是第二次返回了第一次沒有返回的一行,那麼這一行就是一個“幻像”行。

注意:幻讀和不可重複讀的側重點是不同的,不可重複讀側重於資料修改,兩次讀取到的同一行資料不一樣;而幻讀側重於新增或刪除,兩次查詢返回的資料行數不同。

1.4 SERIALIZABLE

序列化,事務最高隔離級別,它會強制事務排序,使之不會發生衝突,從而解決了髒讀、不可重複讀和幻讀問題,但因為執行效率低,所以真正使用的場景並不多。

1.5 小結

簡單總結一下,MySQL 中的事務隔離級別就是為了解決髒讀、不可重複讀和幻讀等問題的,這 4 種隔離級別與這 3 個問題之間的對應關係如下:

| 事務隔離級別 | 髒讀 | 不可重複讀 | 幻讀 | | ---------------------- | ------ | --------- | ------ | | 讀未提交(READ UNCOMMITTED) | √ | √ | √ | | 讀已提交(READ COMMITTED) | × | √ | √ | | 可重複讀(REPEATABLE READ) | × | × | √ | | 序列化(SERIALIZABLE) | × | × | × |

2.併發事務中的問題

併發事務中存在以下 3 個問題。

2.1 髒讀

一個事務讀取到了另一個事務為提交儲存的資料,之後此事務進行了回滾操作,從而導致第一個事務讀取了一個不存在的髒資料。

2.2 不可重複讀

在同一個事務中,同一個查詢在不同的時間得到了不同的結果。例如事務在 T1 讀取到了某一行資料,在 T2 時間重新讀取這一行時候,這一行的資料已經發生修改,所以再次讀取時得到了一個和 T1 查詢時不同的結果。

2.3 幻讀

MySQL 對幻讀的定義如下:

The so-called phantom problem occurs within a transaction when the same query produces different sets of rows at different times. For example, if a SELECT is executed twice, but returns a row the second time that was not returned the first time, the row is a “phantom” row.

官方文件:http://dev.mysql.com/doc/refman/5.7/en/innodb-next-key-locking.html 翻譯為中文是:同一個查詢在不同時間得到了不同的結果,這就是事務中的幻讀問題。例如,一個 SELECT 被執行了兩次,但是第二次返回了第一次沒有返回的一行,那麼這一行就是一個“幻像”行。

3.隔離級別實戰

3.1 查詢事務隔離級別

檢視全域性 MySQL 事務隔離級別和當前會話的事務隔離級別的 SQL 如下:

sql select @@global.tx_isolation,@@tx_isolation;

以上 SQL 執行結果如下圖所示:

3.2 設定事務隔離級別

每個連線到 MySQL 的客戶端可以單獨設定事務的隔離級別,MySQL 可以使用以下 SQL 設定當前連線(客戶端)的事務隔離級別:

sql set session transaction isolation level 事務隔離級別;

其中事務隔離級別有 4 個值:

  • READ UNCOMMITTED
  • READ COMMITTED
  • REPEATABLE READ
  • SERIALIZABLE

### 3.3 髒讀問題

一個事務讀取到了另一個事務為提交儲存的資料,之後此事務進行了回滾操作,從而導致第一個事務讀取了一個不存在的髒資料。接下來,我們使用 SQL 來演示一下髒讀問題。

正式開始之前,先建立一個測試表:

sql -- 建立一個城市表 drop table if exists city; create table city( id int primary key auto_increment, name varchar(250) not null );

髒讀的執行順序如下: image.png 髒讀的執行 SQL 和執行順序如下:

  1. 客戶端 A:set session transaction isolation level read uncommitted;
  2. 客戶端 A:start transaction;
  3. 客戶端 B:start transaction;
  4. 客戶端 B:insert into city(name) values('西安');
  5. 客戶端 A:select * from city;
  6. 客戶端 B:rollback;
  7. 客戶端 A:select * from city;

對應的執行結果如下圖所示: image.png 從上述結果可以看出,當把客戶端 A 設定為讀未提交的事務隔離級別後,客戶端 A 可以讀取到其他事務未提交的資料,當其他事務回滾之後,客戶端 A 讀取的資料就成了髒資料,這就是髒讀,也就是讀未提交的事務隔離級別中存在髒讀的問題

3.4 不可重複讀問題

在同一個事務中,同一個查詢在不同的時間得到了不同的結果。例如事務在 T1 讀取到了某一行資料,在 T2 時間重新讀取這一行時候,這一行的資料已經發生修改,所以再次讀取時得到了一個和 T1 查詢時不同的結果。

不可重複讀的執行順序如下: image.png 不可重複讀的執行 SQL 和執行順序如下:

  1. 客戶端 A:set session transaction isolation level read committed;
  2. 客戶端 A:start transaction;
  3. 客戶端 A:select * from city where id=1;
  4. 客戶端 B:start transaction;
  5. 客戶端 B:update city set name='長安' where id=1;
  6. 客戶端 B:commit;
  7. 客戶端 A:select * from city where id=1;

對應執行的結果如下圖所示: image.png 從上述結果中可以看出,客戶端 A 被設定了讀已提交的事務隔離級別之後,使用同樣的 SQL 兩次讀取到的同一條資料,內容是不一樣的,這就是不可重複讀。也就是讀已提交的事務隔離級別中,可能存在不可重複讀的問題

3.5 幻讀問題

同一個查詢在不同時間得到了不同的結果,這就是事務中的幻讀問題。例如,一個 SELECT 被執行了兩次,但是第二次返回了第一次沒有返回的一行,那麼這一行就是一個“幻像”行。

幻讀的執行順序如下: image.png 幻讀的執行 SQL 和執行順序如下: 客戶端 A:set session transaction isolation level repeatable read; 客戶端 A:start transaction; 客戶端 A:select * from city where id<5; --查詢出1條資料 客戶端 B:start transaction; 客戶端 B:insert into city(id,name) values(2,'北京'); 客戶端 B:commit; 客戶端 A:update city set name='京城' where id=2; 客戶端 A:select * from city where id<5; --查詢出2條資料 對應執行的結果如下圖所示: image.png 從上述結果可以看出,客戶端 A 被設定了可重複讀的事務隔離級別之後,使用相同的 SQL 卻查詢出了一樣的結果,第一次查詢出了一條資料,而第二次查詢出了兩條資料,多出來的這行資料就叫做“幻像”行,因此我們可以得出結果,在可重複讀中可能會存在幻讀的問題

總結

MySQL 中有 4 種事務隔離級別:讀未提交(存在髒讀/不可重複讀/幻讀問題)、讀已提交(存在不可重複讀/幻讀問題)、可重複讀(存在幻讀問題)和序列化,其中可重複讀是 MySQL 預設的事務隔離級別。髒讀是讀到了其他事務未提交的資料,不可重複讀是讀到了其他事務修改的資料,而幻讀則是讀取到了其他事務新增或刪除的“幻像”行資料。

是非審之於己,譭譽聽之於人,得失安之於數。

公眾號:Java面試真題解析

面試合集:http://gitee.com/mydb/interview