重新學習MySQL資料庫9：Innodb中的事務隔離級別和鎖的關係

• 2019 年 11 月 29 日
• 筆記

本文轉自互聯網

本系列文章將整理到我在GitHub上的《Java面試指南》倉庫，更多精彩內容請到我的倉庫里查看

https://github.com/h2pl/Java-Tutorial

喜歡的話麻煩點下Star哈

文章也將同步到我的個人部落格：

www.how2playlife.com

本文是微信公眾號【Java技術江湖】的《重新學習MySQL資料庫》其中一篇，本文部分內容來源於網路，為了把本文主題講得清晰透徹，也整合了很多我認為不錯的技術部落格內容，引用其中了一些比較好的部落格文章，如有侵權，請聯繫作者。

該系列博文會告訴你如何從入門到進階，從sql基本的使用方法，從MySQL執行引擎再到索引、事務等知識，一步步地學習MySQL相關技術的實現原理，更好地了解如何基於這些知識來優化sql，減少SQL執行時間，通過執行計劃對SQL性能進行分析，再到MySQL的主從複製、主備部署等內容，以便讓你更完整地了解整個MySQL方面的技術體系，形成自己的知識框架。

如果對本系列文章有什麼建議，或者是有什麼疑問的話，也可以關注公眾號【Java技術江湖】聯繫作者，歡迎你參與本系列博文的創作和修訂。

Innodb中的事務隔離級別和鎖的關係

前言: 我們都知道事務的幾種性質，資料庫為了維護這些性質，尤其是一致性和隔離性，一般使用加鎖這種方式。同時資料庫又是個高並發的應用，同一時間會有大量的並發訪問，如果加鎖過度，會極大的降低並發處理能力。所以對於加鎖的處理，可以說就是資料庫對於事務處理的精髓所在。這裡通過分析MySQL中InnoDB引擎的加鎖機制，來拋磚引玉，讓讀者更好的理解，在事務處理中資料庫到底做了什麼。

一次封鎖or兩段鎖？ 因為有大量的並發訪問，為了預防死鎖，一般應用中推薦使用一次封鎖法，就是在方法的開始階段，已經預先知道會用到哪些數據，然後全部鎖住，在方法運行之後，再全部解鎖。這種方式可以有效的避免循環死鎖，但在資料庫中卻不適用，因為在事務開始階段，資料庫並不知道會用到哪些數據。 資料庫遵循的是兩段鎖協議，將事務分成兩個階段，加鎖階段和解鎖階段（所以叫兩段鎖）

• 加鎖階段：在該階段可以進行加鎖操作。在對任何數據進行讀操作之前要申請並獲得S鎖（共享鎖，其它事務可以繼續加共享鎖，但不能加排它鎖），在進行寫操作之前要申請並獲得X鎖（排它鎖，其它事務不能再獲得任何鎖）。加鎖不成功，則事務進入等待狀態，直到加鎖成功才繼續執行。
• 解鎖階段：當事務釋放了一個封鎖以後，事務進入解鎖階段，在該階段只能進行解鎖操作不能再進行加鎖操作。

這種方式雖然無法避免死鎖，但是兩段鎖協議可以保證事務的並發調度是串列化（串列化很重要，尤其是在數據恢復和備份的時候）的。

事務中的加鎖方式

事務的四種隔離級別 在資料庫操作中，為了有效保證並發讀取數據的正確性，提出的事務隔離級別。我們的資料庫鎖，也是為了構建這些隔離級別存在的。

• 未提交讀(Read Uncommitted)：允許臟讀，也就是可能讀取到其他會話中未提交事務修改的數據
• 提交讀(Read Committed)：只能讀取到已經提交的數據。Oracle等多數資料庫默認都是該級別 (不重複讀)
• 可重複讀(Repeated Read)：可重複讀。在同一個事務內的查詢都是事務開始時刻一致的，InnoDB默認級別。在SQL標準中，該隔離級別消除了不可重複讀，但是還存在幻讀
• 串列讀(Serializable)：完全串列化的讀，每次讀都需要獲得表級共享鎖，讀寫相互都會阻塞

Read Uncommitted這種級別，資料庫一般都不會用，而且任何操作都不會加鎖，這裡就不討論了。

MySQL中鎖的種類

MySQL中鎖的種類很多，有常見的表鎖和行鎖，也有新加入的Metadata Lock等等,表鎖是對一整張表加鎖，雖然可分為讀鎖和寫鎖，但畢竟是鎖住整張表，會導致並發能力下降，一般是做ddl處理時使用。

行鎖則是鎖住數據行，這種加鎖方法比較複雜，但是由於只鎖住有限的數據，對於其它數據不加限制，所以並發能力強，MySQL一般都是用行鎖來處理並發事務。這裡主要討論的也就是行鎖。

Read Committed（讀取提交內容）

在RC級別中，數據的讀取都是不加鎖的，但是數據的寫入、修改和刪除是需要加鎖的。效果如下

MySQL> show create table class_teacher G        Table: class_teacher        Create Table: CREATE TABLE `class_teacher` (         `id` int(11) NOT NULL AUTO_INCREMENT,         `class_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,         `teacher_id` int(11) NOT NULL,         PRIMARY KEY (`id`),         KEY `idx_teacher_id` (`teacher_id`)        ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci        1 row in set (0.02 sec)        MySQL> select * from class_teacher;        +----+--------------+------------+        | id | class_name   | teacher_id |        +----+--------------+------------+        |  1 | 初三一班     |          1 |        |  3 | 初二一班     |          2 |        |  4 | 初二二班     |          2 |        +----+--------------+------------+

由於MySQL的InnoDB默認是使用的RR級別，所以我們先要將該session開啟成RC級別，並且設置binlog的模式

SET session transaction isolation level read committed;        SET SESSION binlog_format = 'ROW';（或者是MIXED）

為了防止並發過程中的修改衝突，事務A中MySQL給teacher_id=1的數據行加鎖，並一直不commit（釋放鎖），那麼事務B也就一直拿不到該行鎖，wait直到超時。

這時我們要注意到，teacherid是有索引的，如果是沒有索引的classname呢？update classteacher set teacherid=3 where classname = '初三一班'; 那麼MySQL會給整張表的所有數據行的加行鎖。這裡聽起來有點不可思議，但是當sql運行的過程中，MySQL並不知道哪些數據行是 classname = '初三一班'的（沒有索引嘛），如果一個條件無法通過索引快速過濾，存儲引擎層面就會將所有記錄加鎖後返回，再由MySQL Server層進行過濾。

但在實際使用過程當中，MySQL做了一些改進，在MySQL Server過濾條件，發現不滿足後，會調用unlock_row方法，把不滿足條件的記錄釋放鎖 (違背了二段鎖協議的約束)。這樣做，保證了最後只會持有滿足條件記錄上的鎖，但是每條記錄的加鎖操作還是不能省略的。可見即使是MySQL，為了效率也是會違反規範的。（參見《高性能MySQL》中文第三版p181）

這種情況同樣適用於MySQL的默認隔離級別RR。所以對一個數據量很大的表做批量修改的時候，如果無法使用相應的索引，MySQL Server過濾數據的的時候特別慢，就會出現雖然沒有修改某些行的數據，但是它們還是被鎖住了的現象。

Repeatable Read（可重讀）

這是MySQL中InnoDB默認的隔離級別。我們姑且分「讀」和「寫」兩個模組來講解。

讀 讀就是可重讀，可重讀這個概念是一事務的多個實例在並發讀取數據時，會看到同樣的數據行，有點抽象，我們來看一下效果。

RC（不可重讀）模式下的展現

事務B修改id=1的數據提交之後，事務A同樣的查詢，後一次和前一次的結果不一樣，這就是不可重讀（重新讀取產生的結果不一樣）。這就很可能帶來一些問題，那麼我們來看看在RR級別中MySQL的表現：

我們注意到，當teacher_id=1時，事務A先做了一次讀取，事務B中間修改了id=1的數據，並commit之後，事務A第二次讀到的數據和第一次完全相同。所以說它是可重讀的。那麼MySQL是怎麼做到的呢？這裡姑且賣個關子，我們往下看。

不可重複讀和幻讀的區別

很多人容易搞混不可重複讀和幻讀，確實這兩者有些相似。但不可重複讀重點在於update和delete，而幻讀的重點在於insert。

如果使用鎖機制來實現這兩種隔離級別，在可重複讀中，該sql第一次讀取到數據後，就將這些數據加鎖，其它事務無法修改這些數據，就可以實現可重複讀了。但這種方法卻無法鎖住insert的數據，所以當事務A先前讀取了數據，或者修改了全部數據，事務B還是可以insert數據提交，這時事務A就會發現莫名其妙多了一條之前沒有的數據，這就是幻讀，不能通過行鎖來避免。需要Serializable隔離級別 ，讀用讀鎖，寫用寫鎖，讀鎖和寫鎖互斥，這麼做可以有效的避免幻讀、不可重複讀、臟讀等問題，但會極大的降低資料庫的並發能力。

所以說不可重複讀和幻讀最大的區別，就在於如何通過鎖機制來解決他們產生的問題。

上文說的，是使用悲觀鎖機制來處理這兩種問題，但是MySQL、ORACLE、PostgreSQL等成熟的資料庫，出於性能考慮，都是使用了以樂觀鎖為理論基礎的MVCC（多版本並發控制）來避免這兩種問題。

悲觀鎖和樂觀鎖

• 悲觀鎖

正如其名，它指的是對數據被外界（包括本系統當前的其他事務，以及來自外部系統的事務處理）修改持保守態度，因此，在整個數據處理過程中，將數據處於鎖定狀態。悲觀鎖的實現，往往依靠資料庫提供的鎖機制（也只有資料庫層提供的鎖機制才能真正保證數據訪問的排他性，否則，即使在本系統中實現了加鎖機制，也無法保證外部系統不會修改數據）。

在悲觀鎖的情況下，為了保證事務的隔離性，就需要一致性鎖定讀。讀取數據時給加鎖，其它事務無法修改這些數據。修改刪除數據時也要加鎖，其它事務無法讀取這些數據。

• 樂觀鎖

相對悲觀鎖而言，樂觀鎖機制採取了更加寬鬆的加鎖機制。悲觀鎖大多數情況下依靠資料庫的鎖機制實現，以保證操作最大程度的獨佔性。但隨之而來的就是資料庫性能的大量開銷，特別是對長事務而言，這樣的開銷往往無法承受。

而樂觀鎖機制在一定程度上解決了這個問題。樂觀鎖，大多是基於數據版本（ Version ）記錄機制實現。何謂數據版本？即為數據增加一個版本標識，在基於資料庫表的版本解決方案中，一般是通過為資料庫表增加一個 「version」 欄位來實現。讀取出數據時，將此版本號一同讀出，之後更新時，對此版本號加一。此時，將提交數據的版本數據與資料庫表對應記錄的當前版本資訊進行比對，如果提交的數據版本號大於資料庫表當前版本號，則予以更新，否則認為是過期數據。

要說明的是，MVCC的實現沒有固定的規範，每個資料庫都會有不同的實現方式，這裡討論的是InnoDB的MVCC。

MVCC在MySQL的InnoDB中的實現

在InnoDB中，會在每行數據後添加兩個額外的隱藏的值來實現MVCC，這兩個值一個記錄這行數據何時被創建，另外一個記錄這行數據何時過期（或者被刪除）。在實際操作中，存儲的並不是時間，而是事務的版本號，每開啟一個新事務，事務的版本號就會遞增。在可重讀Repeatable reads事務隔離級別下：

• SELECT時，讀取創建版本號<=當前事務版本號，刪除版本號為空或>當前事務版本號。
• INSERT時，保存當前事務版本號為行的創建版本號
• DELETE時，保存當前事務版本號為行的刪除版本號
• UPDATE時，插入一條新紀錄，保存當前事務版本號為行創建版本號，同時保存當前事務版本號到原來刪除的行

通過MVCC，雖然每行記錄都需要額外的存儲空間，更多的行檢查工作以及一些額外的維護工作，但可以減少鎖的使用，大多數讀操作都不用加鎖，讀數據操作很簡單，性能很好，並且也能保證只會讀取到符合標準的行，也只鎖住必要行。

我們不管從資料庫方面的教課書中學到，還是從網路上看到，大都是上文中事務的四種隔離級別這一模組列出的意思，RR級別是可重複讀的，但無法解決幻讀，而只有在Serializable級別才能解決幻讀。於是我就加了一個事務C來展示效果。在事務C中添加了一條teacherid=1的數據commit，RR級別中應該會有幻讀現象，事務A在查詢teacherid=1的數據時會讀到事務C新加的數據。但是測試後發現，在MySQL中是不存在這種情況的，在事務C提交後，事務A還是不會讀到這條數據。可見在MySQL的RR級別中，是解決了幻讀的讀問題的。參見下圖

讀問題解決了，根據MVCC的定義，並發提交數據時會出現衝突，那麼衝突時如何解決呢？我們再來看看InnoDB中RR級別對於寫數據的處理。

「讀」與「讀」的區別

可能有讀者會疑惑，事務的隔離級別其實都是對於讀數據的定義，但到了這裡，就被拆成了讀和寫兩個模組來講解。這主要是因為MySQL中的讀，和事務隔離級別中的讀，是不一樣的。

我們且看，在RR級別中，通過MVCC機制，雖然讓數據變得可重複讀，但我們讀到的數據可能是歷史數據，是不及時的數據，不是資料庫當前的數據！這在一些對於數據的時效特別敏感的業務中，就很可能出問題。

對於這種讀取歷史數據的方式，我們叫它快照讀 (snapshot read)，而讀取資料庫當前版本數據的方式，叫當前讀 (current read)。很顯然，在MVCC中：

• 快照讀：就是select
  • select * from table ….;
• 當前讀：特殊的讀操作，插入/更新/刪除操作，屬於當前讀，處理的都是當前的數據，需要加鎖。
  • select * from table where ? lock in share mode;
  • select * from table where ? for update;
  • insert;
  • update ;
  • delete;

事務的隔離級別實際上都是定義了當前讀的級別，MySQL為了減少鎖處理（包括等待其它鎖）的時間，提升並發能力，引入了快照讀的概念，使得select不用加鎖。而update、insert這些「當前讀」，就需要另外的模組來解決了。

寫（"當前讀"） 事務的隔離級別中雖然只定義了讀數據的要求，實際上這也可以說是寫數據的要求。上文的「讀」，實際是講的快照讀；而這裡說的「寫」就是當前讀了。 為了解決當前讀中的幻讀問題，MySQL事務使用了Next-Key鎖。

Next-Key鎖 Next-Key鎖是行鎖和GAP（間隙鎖）的合併，行鎖上文已經介紹了，接下來說下GAP間隙鎖。

行鎖可以防止不同事務版本的數據修改提交時造成數據衝突的情況。但如何避免別的事務插入數據就成了問題。我們可以看看RR級別和RC級別的對比

RC級別：

RR級別：

通過對比我們可以發現，在RC級別中，事務A修改了所有teacherid=30的數據，但是當事務B insert進新數據後，事務A發現莫名其妙多了一行teacherid=30的數據，而且沒有被之前的update語句所修改，這就是「當前讀」的幻讀。

RR級別中，事務A在update後加鎖，事務B無法插入新數據，這樣事務A在update前後讀的數據保持一致，避免了幻讀。這個鎖，就是Gap鎖。

MySQL是這麼實現的：

在classteacher這張表中，teacherid是個索引，那麼它就會維護一套B+樹的數據關係，為了簡化，我們用鏈表結構來表達（實際上是個樹形結構，但原理相同）

如圖所示，InnoDB使用的是聚集索引，teacher_id身為二級索引，就要維護一個索引欄位和主鍵id的樹狀結構（這裡用鏈表形式表現），並保持順序排列。

Innodb將這段數據分成幾個個區間

• (negative infinity, 5],
• (5,30],
• (30,positive infinity)；

update classteacher set classname='初三四班' where teacher_id=30;不僅用行鎖，鎖住了相應的數據行；同時也在兩邊的區間，（5,30]和（30，positive infinity），都加入了gap鎖。這樣事務B就無法在這個兩個區間insert進新數據。

受限於這種實現方式，Innodb很多時候會鎖住不需要鎖的區間。如下所示：

update的teacher_id=20是在(5，30]區間，即使沒有修改任何數據，Innodb也會在這個區間加gap鎖，而其它區間不會影響，事務C正常插入。

如果使用的是沒有索引的欄位，比如update classteacher set teacherid=7 where class_name='初三八班（即使沒有匹配到任何數據）',那麼會給全表加入gap鎖。同時，它不能像上文中行鎖一樣經過MySQL Server過濾自動解除不滿足條件的鎖，因為沒有索引，則這些欄位也就沒有排序，也就沒有區間。除非該事務提交，否則其它事務無法插入任何數據。

行鎖防止別的事務修改或刪除，GAP鎖防止別的事務新增，行鎖和GAP鎖結合形成的的Next-Key鎖共同解決了RR級別在寫數據時的幻讀問題。

Serializable 這個級別很簡單，讀加共享鎖，寫加排他鎖，讀寫互斥。使用的悲觀鎖的理論，實現簡單，數據更加安全，但是並發能力非常差。如果你的業務並發的特別少或者沒有並發，同時又要求數據及時可靠的話，可以使用這種模式。

這裡要吐槽一句，不要看到select就說不會加鎖了，在Serializable這個級別，還是會加鎖的！