淺析MySQL InnoDB的隔離級別

pjmike_pj發表於2019-01-15
MySql

前言

還是老規矩，首先提出兩個待解決的問題：

  • MySQL InnoDB儲存引擎中事務的隔離級別有哪些？
  • 對應隔離級別的實現機制是什麼？

本文就將對上面這兩個問題進行解答，分析事務的隔離級別以及相關鎖機制。

隔離性簡介

隔離性主要是指資料庫系統提供一定的隔離機制，保證事務在不受外部併發操作影響的"獨立"環境執行，意思就是多個事務併發執行時，一個事務的執行不應影響其它事務的執行。

4種隔離級別介紹

在SQL標準中定義了4種隔離級別，分別是：

  • Read uncommitted: 未提交讀，事務中的修改，即使沒有提交，對其他事務也是可見的。存在髒讀
  • Read committed: 提交讀，大多數資料庫系統的預設隔離級別(MySQL不是), 一個事務從開始到提交之前，所做的修改對其他事務不可見。解決髒讀，存在幻讀和不可重複讀
  • repeatable read: 可重複讀，該級別保證在同一事務中多次讀取同樣記錄的結果是一致的。解決髒讀和不可重複讀，理論上存在幻讀，但是在InnoDB引擎中解決了幻讀
  • Serializable：可序列化，強制事務序列執行。

上面4種隔離級別是SQL標準定義的，但是在不同的儲存引擎中，實現的隔離級別不盡相同。本文主要介紹MySQL InnoDB 儲存引擎中的隔離級別，在InnoDB儲存引擎中，Repeatable Read 是預設的事務隔離級別，同時該引擎的實現基於多版本的併發控制協議——MVCC (Multi-Version Concurrency Control)，解決了幻讀問題，當然 髒讀和不可重複讀也是不存在的。MVCC最大的好處就在於讀不加鎖，讀寫不衝突，這樣極大的增加了系統的併發效能

Read uncommitted

未提交讀，這種情況下，一個事務A可以看到另一個事務B未提交的資料，如果此時事務B發生回滾，那麼事務A拿到的就是髒資料，這也就是髒讀的含義。此隔離級別在MySQL InnoDB一般不會使用，不做過多說明。

Read Committed

提交讀，一個事務從開始直到提交之前，所做的任何修改對其他事務都是不可見的。解決了髒讀問題，但是存在幻讀現象。

所謂幻讀，指的是在同一事務下，連續執行兩次同樣的SQL語句可能導致不同的結果，第二次的SQL語句可能會返回之前不存在的行，也就是"幻行"。

比如下面這個例子：

  1. 首先建立一張表,
CREATE TABLE `t` (
  `a` int(11) NOT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB

insert into t(a) values(1);
insert into t(a) values(2);
insert into t(a) values(4);
複製程式碼
  1. 分別執行事務1和事務2：

read_committed

可以從上圖看出，Read Committed這種隔離級別存在幻讀現象。實際上，Read Committed還可能存在不可重複讀的問題，不可重複讀，指的是一個事務內根據同一條件對行記錄進行多次查詢，但是查詢出的資料結果不一致，原因就是查詢區間資料被其他事務修改了。

不可重複讀感覺和幻讀有點像，實際上，前者強調是同一行記錄資料結果不一樣，後者強調的時多次查詢返回的結果集不一樣，增加了或減少了。

Repeatable Read

可重複讀，該級別保證在同一事務中多次讀取同樣記錄的結果是一致的，在InnoDB儲存引擎中同時解決了幻讀和不可重複讀問題。至於InnoDB通過什麼方式解決幻讀和不可重複讀問題，後續內容揭曉。

Serializable (可序列化)

Serializable 是最高的隔離級別，它通過 強制事務序列執行，避免了幻讀的問題，但是 Serializable 會在讀取的每一行資料上都加鎖，所以可能導致大量的超時和鎖爭用的問題,因此併發度急劇下降，在MySQL InnoDB不被建議使用

Read Committed隔離級別下的加鎖分析

隔離級別的實現與鎖機制密不可分，所以需要引入鎖的概念，首先我們看下InnoDB儲存引擎提供的兩種標準的行級鎖：

  • 共享鎖(S Lock)：又稱為讀鎖，可以允許多個事務併發的讀取同一資源，互不干擾。即如果一個事務T對資料A加上共享鎖後，其他事務只能對A再加共享鎖，不能再加排他鎖，只能讀資料，不能修改資料
  • 排他鎖(X Lock): 又稱為寫鎖，如果事務T對資料A加上排他鎖後，其他事務不能再對A加上任何型別的鎖，獲取排他鎖的事務既能讀資料，也能修改資料。

注意： 共享鎖和排他鎖是不相容的。

MySQL InnoDB儲存引擎是使用多版本併發控制的，讀不加鎖，讀寫不衝突，除非特定場景下的顯示加讀鎖（這裡不去探究）。本小節主要分析Read Committed隔離級別下的加鎖情況，在MVCC的作用下，一般也就是寫操作加X鎖了。

加鎖操作是和索引緊密相關的，對一個SQL語句進行加鎖分析時，也要仔細考究其屬性列上的索引型別。假設有資料表t1，有兩個列，name列和id列，插入了幾條資料，沒有明確索引情況：

insert into t1(name,id) values("a",10);
insert into t1(name,id) values("b",11);
insert into t1(name,id) values("c",13);
insert into t1(name,id) values("d",20);
複製程式碼

下面執行 delete from t1 where id = 10 這條SQL語句，這裡的隔離級別設定為Read Committed，從這條SQL語句不能得知id列的索引情況，所以需要分情況討論：

  • id列是主鍵
  • id列是二級唯一索引
  • id列是二級非唯一索引
  • id列上沒有索引

下面是對以上幾種情況的加鎖情況進行歸納總結，更詳細的內容可以參閱資料庫大牛的文章：MySQL 加鎖處理分析

id列是主鍵

id是主鍵時，上述SQL只需要在id=10這條記錄上加X鎖即可

id列是二級唯一索引

若id列是唯一索引，而主鍵是name列，那麼SQL需要加上兩個X鎖，一個對應於id索引上的id=10的記錄，另一把鎖對應於主鍵索引上的[name="a",id=10]的記錄

id列是二級非唯一索引

若id列上有非唯一索引，那麼對應的所有滿足SQL查詢條件的記錄，都會被加鎖，同時，這些記錄在主鍵索引上的記錄也會被加鎖。

id列上沒有索引

若id列上沒有索引，SQL會走聚簇索引的全掃描進行過濾，由於過濾是由MySQL Sever層面進行的，因此每條記錄，無論是否滿足條件，都會被加上X鎖。

Repeatable Read隔離級別下的加鎖分析

前面說過，在Repeatable Read隔離級別下，InnoDB儲存引擎解決了幻讀和不可重複讀問題，具體的原理是怎麼樣的呢？

之前簡短的介紹了InnoDB中行鎖的知識，下面來看下行鎖的三種演算法：

  • Record Lock: 單個索引記錄上的鎖，即加X鎖
  • Gap Lock： 間隙鎖，鎖定一個範圍，但不包含記錄自身
  • Next-Key Lock: Gap Lock + Record Lock，鎖定一個範圍，並且鎖定本身。

Record Lock總是會去鎖住索引記錄，如果InnoDB儲存引擎在建表的時候沒有設定任何一個索引，那麼這時InnoDB會使用隱式的主鍵來進行鎖定。(表沒有定義主鍵的情況，InnoDB會預設新增一個隱式的主鍵索引)

Next-Key Lock是結合了Gap Lock和Record Lock的一種鎖定演算法，比如一個索引列有10,11,13和20這4個值，那麼該索引可能被Next-Key Locking的區間為：

  • ($-\infty$,10)
  • (10,11]
  • (11,13]
  • (13,20]
  • (20,$+\infty$)

需要注意一點的是，當查詢的索引含有唯一屬性時，即是主鍵索引或者唯一索引時，InnoDB儲存引擎會對Next-Key Lock進行優化，將其降級為Record Lock,即僅鎖住索引本身，一般加上X鎖。

Next-Key Lock機制設計的目的就是為了解決幻讀問題，主要針對查詢列索引為非唯一索引的時候。以下面這個例子進行說明：

  1. 首先建立測試表t1，name是主鍵索引，id為非唯一索引，即輔助索引
CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `name` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`name`),
  KEY `id_indx` (`id`)
) ENGINE=InnoDB

insert into t1(name,id) values("a",10);
insert into t1(name,id) values("b",11);
insert into t1(name,id) values("c",13);
insert into t1(name,id) values("d",20);
複製程式碼
  1. 執行 delete from t1 where id = 11,其加鎖情況如下圖所示

gap_lock

這條SQL通過索引列id進行刪除操作，該索引為非唯一索引，所以其使用傳統的Next-Key Locking 技術加鎖，並且由於有主鍵索引和輔助索引兩個，需要分別進行鎖定。對於主鍵索引（即聚集索引），其僅對列name = "b"的索引加上 Record Lock，實際上就是X鎖。

而對於非唯一索引，其加上的時Next-Key Lock，鎖定範圍是(10,11)，對其加上Gap Lock(間隙鎖)，GAP鎖實際上就是加在兩條邊界記錄之間的位置。還需要注意的是，InnoDB還會對輔助索引下一個鍵值加上gap lock，即看到在(11,13)之間加了一個GAP鎖。對於11值本身加上Record Lock，即X鎖。

若此時開啟另外一個事務執行下面的語句，就會阻塞：

1. select * from t1 where name = "b";
2. insert into t1(name,id) values("c",12);
複製程式碼

比如第一條語句不能執行，因為在開始的事務中已經對聚集索引中的列name="b"的值加上了X鎖。因此執行會被阻塞。而第二個SQL，同樣不能執行，插入的值12在鎖定範圍(11,13)中，需要阻塞等待。

所以，從上例就可以看出，GAP Lock的作用就是為了阻止多個事務將記錄插入到同一範圍內，這樣就有效的解決了幻讀問題。

隔離級別總結

下面總結下InnoDB儲存引擎下的各種隔離級別：

隔離級別 髒讀可能性 不可重複讀可能性 幻讀可能性 加鎖讀
Read Uncommitted Yes Yes Yes No
Read Committed No Yes Yes No
Repeatable Read No No No No
Serializable No No No Yes

參考資料 & 鳴謝

相關文章