技術文章 » 一起聊聊MYSQL中鎖的各種模式與型別

一起聊聊MYSQL中鎖的各種模式與型別

2022-01-29 07:00:15
本篇文章給大家帶來了關於mysql中鎖的各種型別與模式的相關知識，希望對大家有幫助。

在日常開發工作中，我們幾乎需要天天與資料庫打交道，作為一名只會CRUD的SQL BOY，除了每天用mybatis-generator自動生成DAO層程式碼之外，我們幾乎不用去care資料庫中如何處理並行請求，但是突然某一天MYSQL資料庫告警了，出現了死鎖，我們的內心慌的一匹，不禁想問：這不就是個普通查詢嗎，咋還鎖起來了？

為了避免慌亂的表情被主管捕捉到，我們需要提前瞭解一下資料庫中到底有哪些鎖。

在MySQL中，其實將鎖分成了兩類：鎖型別（lock_type）和鎖模式（lock_mode）。

鎖型別描述的鎖的粒度，也就是把鎖具體加在什麼地方；而鎖模式描述的是到底加的是什麼鎖，是讀鎖還是寫鎖。鎖模式通常和鎖型別結合使用。

按鎖的模式分

讀鎖

讀鎖，又叫共用鎖/S鎖/share locks。

讀鎖是某個事務（比如事務A）在進行讀取操作（比如讀一張表或者讀取某一行）時建立出來的鎖，其他的事務可以並行地讀取這些資料（被加了鎖的），但是不能修改這些資料（除非持有鎖的使用者已經釋放鎖）。

事務A對資料加上讀鎖之後，其他事務依然可以對其新增讀鎖（共用），但是不能新增寫鎖。

在記錄上加讀鎖

InnoDB支援表鎖和行鎖，在行（也就是記錄）上加鎖，並不是鎖住該條記錄，而是在記錄對應的索引上加鎖。如果where條件中不走索引，則會對所有的記錄加鎖。

顯式加鎖語句為：

01.png

注意：這裡所說的讀，是指當前讀，快照讀是無需加鎖的。普通select讀一般都是快照讀，除了select...lock in share mode這樣的顯式加鎖語句下會變成當前讀，在InnoDB引擎的serializable級別下，普通select讀也會變成快照讀。

另外需要注意，對於行鎖的加鎖過程分析，要根據事務隔離級別、是否使用索引（哪種型別的索引）、記錄是否存在等因素結合分析，才能判斷在哪裡加上了鎖。

innodb引擎中的加讀鎖的幾種情形

普通查詢在隔離級別為 serializable 會給記錄加S鎖。但這也取決於場景：非事務讀（auto-commit）在 Serializable 隔離級別下，無需加鎖；

Serializable隔離級別時：如果查詢條件為唯一索引且是唯一等值查詢時：是在該條記錄上加S鎖；非唯一條件查詢（查詢會掃描到多條記錄時）：記錄本身+記錄的間隙（需要具體分析間隙的範圍），加S鎖；

select … in share mode，會給記錄加S鎖，但是根據隔離級別的不同，加鎖的行為有所不同：

RC隔離級別：是在記錄上加S鎖。RR/Serializable隔離級別：如果查詢條件為唯一索引且是唯一等值查詢時：是在該條記錄上加S鎖；非唯一條件查詢（查詢會掃描到多條記錄時）：記錄本身+記錄的間隙（需要具體分析間隙的範圍），加S鎖；

通常insert操作是不加鎖的，但如果在插入或更新記錄時，檢查到 duplicate key（或者有一個被標記刪除的duplicate key），對於普通的insert/update，會加S鎖，而對於類似replace into或者insert … on duplicate 這樣的SQL語句加的是X鎖。

insert … select 插入資料時，會對 select 的表上掃描到的資料加S鎖；

外來鍵檢查：當我們刪除一條父表上的記錄時，需要去檢查是否有參照約束，這時候會掃描子表上對應的記錄，並加上S鎖。

在表上加讀鎖

表鎖由 MySQL伺服器實現，無論儲存引擎是什麼，都可以使用表鎖。一般在執行 DDL 語句時，譬如 ALTER TABLE 時就會對整個表進行加鎖。在執行 SQL 語句時，也可以明確對某個表加鎖。

給表顯式加鎖語句為：

02.png

在使用MYISAM引擎時，通常我們不需要手動加鎖，因為MYISAM引擎會針對我們的sql語句自動進行加鎖，整個過程不需要使用者干預：

  • 查詢語句（select）：會自動給涉及的表加讀鎖；

  • 更新語句（update、delete、insert）：會自動給涉及的表加寫鎖。

寫鎖

寫鎖，排他鎖/X鎖/exclusive locks。寫鎖的阻塞性比讀鎖要嚴格的多，一個事務對資料新增寫鎖之後，其他的事務對該資料，既不能讀取也不能更改。

與讀鎖加鎖的範圍相同，寫鎖既可以加在記錄上，也可以加在表上。

在記錄上加寫鎖

在記錄上加寫鎖，引擎需要使用InnoDB。

通常普通的select語句是不會加鎖的（隔離級別為Serializable除外），想要在查詢時新增排他鎖需要使用以下語句：

查詢時加寫鎖：

03.png

與加讀鎖相同，寫鎖也是加在索引上的。

更新時加寫鎖：

04.png

在表上加寫鎖

顯式給表加寫鎖的語句為：

05.png

當引擎選擇myisam時，insert/update/delete語句，會自動給該表加上排他鎖。

讀寫鎖相容性：

  • 讀鎖是共用的，它不會阻塞其他讀鎖，但會阻塞其他的寫鎖；

  • 寫鎖是排他的，它會阻塞其他讀鎖和寫鎖；

  • 總結：讀讀不互斥，讀寫互斥，寫寫互斥

意向鎖

意向鎖是一種不與行級鎖衝突的表級鎖，表示表中的記錄所需要的鎖(S鎖或X鎖)的型別（其實就是告訴你，這張表中已經存在了行鎖（行鎖的型別），所以叫意向鎖）。InnoDB支援多種粒度的鎖，允許行級鎖和表級鎖的共存。

意向鎖分為：

意向共用鎖(IS鎖)：IS鎖表示當前事務意圖在表中的行上設定共用鎖

下面語句執行時會首先獲取IS鎖，因為這個操作在獲取S鎖：獲取S鎖：select ... lock in share mode

意向排它鎖(IX鎖)：IX鎖表示當前事務意圖在表中的行上設定排它鎖

下面語句執行時會首先獲取IX鎖，因為這個操作在獲取X鎖：獲取X鎖：select ... for update

事務要獲取某個表上的S鎖和X鎖之前，必須先分別獲取對應的IS鎖和IX鎖。

意向鎖有什麼作用呢：

如果另一個事務試圖在該表級別的共用鎖或排它鎖，則受到由第一個事務控制的表級別意向鎖的阻塞。第二個事務在鎖定該表前不必檢查各個頁或行鎖，而只需檢查表上的意向鎖。

範例：表test_user：

06.png

07.png

事務 A 獲取了某一行的排他鎖，並未提交；

事務 B 想要獲取 test_user 表的表共用鎖；

因為共用鎖與排他鎖互斥，所以事務 B 在試圖對 test_user 表加共用鎖的時候，必須保證：

  • 當前沒有其他事務持有 users 表的排他鎖（表排他鎖）。

  • 當前沒有其他事務持有 users 表中任意一行的排他鎖（行排他鎖）。

為了檢測是否滿足第二個條件，事務 B 必須在確保 test_user表不存在任何排他鎖的前提下，去檢測表中的每一行是否存在排他鎖。很明顯這是一個效率很差的做法，但是有了意向鎖之後，情況就不一樣了：

因為此時事務A獲取了兩把鎖：users 表上的意向排他鎖與 id 為 28 的資料行上的排他鎖。

事務 B 想要獲取 test_user 表的共用鎖：

事務 B 只需要檢測事務 A 是否持有 test_user 表的意向排他鎖，就可以得知事務 A 必然持有該表中某些資料行的排他鎖，那麼事務 B 對 test_users 表的加鎖請求就會被排斥（阻塞），從而無需去檢測表中的每一行資料是否存在排他鎖。

事務 C 也想獲取 users 表中某一行的排他鎖：

  • 事務 C 檢測到事務 A 持有 test_user 表的意向排他鎖；

  • 意向鎖之間並不互斥，所以事務 C 獲取到了 test_user 表的意向排他鎖；

  • 因為id 為 31 的資料行上不存在任何排他鎖，最終事務 C 成功獲取到了該資料行上的排他鎖。

意向鎖與意向鎖之間是不互斥的，但是意向鎖與其他表鎖之間存在一定的相容互斥，具體如下：

意向鎖之間的相容互斥性：

08.png

意向鎖與普通的排他 / 共用鎖互斥性：

09.png

自增鎖

我們在設計表結構的時候，通常會把主鍵設定成自增長（思考一下為什麼？）。

在InnoDB儲存引擎中，針對每個自增長的欄位都設定了一個自增長的計數器。我們可以執行下面的語句來得到這個計數器的當前值：

10.png

當我們進行插入操作的時候，該操作會根據這個自增長的計數器的當前值進行+1操作，並賦予自增長的列，這個操作我們稱之為auto-inc Locking，也就是自增長鎖，這種鎖其實採用的是特殊的表鎖機制，如果insert操作出現在一個事務中，這個鎖是在insert操作完成之後立即釋放，而不是等待事務提交。

按鎖的型別分

全域性鎖

所謂全域性鎖，其實就是給整個資料庫範例加鎖。

資料庫範例與資料庫是有所區別的：

資料庫，就是儲存資料的倉庫，具體到mysql中，資料庫其實是一系列資料檔案集合（也就是我們通常所說的database，比如建立資料庫語句就是 create database...）。

資料庫範例，是指存取資料庫的應用程式，在Mysql中，就是mysqld程序了。

簡單來理解，資料庫範例中包含了你建立的各種資料庫。

如果給資料庫範例加全域性鎖會導致整個庫處於唯讀狀態（這是非常危險的）。

一般來說，全域性鎖的典型使用場景是用於全庫備份，即把資料庫中所有的表都select出來。但是要注意，讓整個庫都處於唯讀狀態，會導致一些嚴重的問題：

  • 在主庫上加全域性鎖，在加鎖期間，不能執行任何更新操作，業務基本上很多功能都不可用了；

  • 在從庫上加全域性鎖，在加鎖期間，不能執行主從同步，會導致主從同步延遲。

全域性鎖的加鎖語句是：

11.png

解除全域性鎖的方法是：

  • 斷開執行全域性鎖的session即可；

  • 執行解鎖sql語句：unlock tables；

如果需要個資料庫備份的話，可以使用官方自帶的邏輯備份工具mysqldump。

既然已經有了dump工具，為什麼還需要 FTWRL 呢？一致性讀是好，但前提是引擎要支援這個隔離級別。比如，MyISAM 這種不支援事務的引擎。這時，我們就需要使用 FTWRL 命令了。

FTWRL 前有讀寫的話，FTWRL 都會等待讀寫執行完畢後才執行。

FTWRL 執行的時候要刷髒頁的資料到磁碟，因為要保持資料的一致性 ，所以執行FTWRL時候是所有事務都提交完畢的時候。

全域性鎖的實現還是依賴於後設資料鎖的。

後設資料鎖

後設資料鎖（MetaData Lock），也叫MDL鎖，是用來保護後設資料資訊，系統級的鎖無法主動控制。在MySQL5.5版本，開始引入MDL鎖，主要是為了在並行環境下對DDL、DML同時操作下保持後設資料的一致性。比如下面這種情況：

隔離級別：RR

12.png

如果沒有後設資料鎖的保護，那麼事務2可以直接執行DDL操作，導致事務1出錯。MYSQL5.5版本的時候加入 MDL 鎖，是為了保護這種情況的發生。由於事務1開啟了查詢，那麼獲得了後設資料鎖，鎖的模式為MDL讀鎖，事務2要執行DDL，則需獲得 MDL 寫鎖，由於讀寫鎖互斥，所以事務2需要等待事務1釋放掉讀鎖才能執行。

  • 對錶中的記錄進行增刪改查（DML操作）的時候，自動加MDL讀鎖；

  • 對錶的結構（DDL操作）進行修改的時候，自動加MDL寫鎖。

MDL鎖的粒度

MDL鎖是Mysql伺服器層面中實現的，而不是在儲存引擎外掛中實現。按照鎖定的範圍，MDL鎖可以分為以下幾類：

13.png

MDL鎖的模式

14.png

頁級鎖

MySQL中鎖定粒度介於行級鎖和表級鎖中間的一種鎖。表級鎖速度快，但衝突多，行級衝突少，但速度慢。所以取了折衷的頁級，一次鎖定相鄰的一組記錄。不同的儲存引擎支援不同的鎖機制。根據不同的儲存引擎，MySQL中鎖的特性可以大致歸納如下：

15.png

頁級鎖是MySQL中比較獨特的一種鎖定級別，應用於BDB引擎，並行度一般，頁級鎖定的特點是鎖定顆粒度介於行級鎖定與表級鎖之間，所以獲取鎖定所需要的資源開銷，以及所能提供的並行處理能力也同樣是介於上面二者之間。另外，頁級鎖定和行級鎖定一樣，會發生死鎖。

鎖定粒度大小比較：表級鎖 > 頁級鎖 > 行級鎖

表級鎖

表鎖在上文我們已經介紹過，相比於行鎖的細粒度加鎖，表鎖是對整張表加鎖。由於是對整張表加鎖，就沒有行鎖的加鎖方式那麼複雜，所以加鎖比行鎖快，而且不會出現死鎖的情況（因為事務是一次性獲取想要加的表表鎖），但是表鎖也存在一些問題：鎖的範圍過大，在並行比較高的情況下，會導致搶鎖的衝突概率變高，這樣並行效能就大打折扣了。

表鎖的加鎖方式

引擎選擇MYISAM時

MYISAM引擎只支援表鎖，不支援行鎖。

手動新增表級鎖的語句如下：

16.png

在使用MYISAM引擎時，通常我們不需要手動加鎖，因為MYISAM引擎會針對我們的sql語句自動進行加鎖，整個過程不需要使用者干預：

  • 查詢語句（select）：會自動給涉及的表加讀鎖；

  • 更新語句（update、delete、insert）：會自動給涉及的表加寫鎖

引擎選擇InnoDB時

InnoDB引擎同時支援行級鎖和表級鎖，預設為行級鎖。

給InnoDB引擎的表手動加鎖，也同樣使用 lock table {tableName} read/write 語句進行讀/寫鎖的新增。

除此之外，innodb還支援一種表級鎖：意向鎖（上文已經介紹過）。

總的來說，InnoDB引擎的表級鎖包含五種鎖模式：

  • LOCK_IS：表意向讀鎖

  • LOCK_IX：表意向寫鎖

  • LOCK_S：表讀鎖

  • LOCK_X：表寫鎖

  • LOCK_AUTO_INC：自增鎖

行級鎖

在編寫業務程式碼的過程中，我們接觸最多的就是行級鎖了（表級鎖由於效能問題，一般不推薦使用）。相比於表級鎖，行級鎖具有明顯的效能優勢：

  • 衝突少：多執行緒中存取不同的記錄時只存在少量鎖定衝突；

  • 鎖的粒度小：可以長時間鎖定單一的行，對其他的行沒有影響，所以並行度是最高的；

但是使用行鎖時，一旦稍不注意，是非常容易出現死鎖的（表鎖就不存在死鎖現象），所以使用行鎖需要注意加鎖的順序和鎖定的範圍。

InnoDB的行鎖是通過對索引項加鎖實現的，這表示只有通過索引查詢記錄時才會使用行鎖，如果不走索引查詢資料將使用表鎖，則效能會大打折扣。

需要記住：行鎖也叫記錄鎖，記錄鎖都是加在索引上的。

  • where條件指定的是主鍵索引：則在主鍵索引上加鎖；

  • wehre條件指定的是二級索引：記錄鎖不僅會加在這個二級索引上，還會加在這個二級索引所對應的聚簇索引上；

  • where條件如果無法走索引：MySQL會給整張表所有資料行加記錄鎖，儲存引擎層將所有記錄返回由MySQL伺服器端進行過濾。

記錄鎖：LOCK_REC_NOT_GAP（只鎖記錄）

記錄鎖是最簡單的行鎖。比如在RR隔離級別時，執行 select * from t_user where id = 1 for update 語句時，實際上是對 id = 1 (這裡id為主鍵)這條記錄上鎖（鎖加在聚簇索引上）。

記錄鎖永遠都是加在索引上的，就算一個表沒有建索引，資料庫也會隱式的建立一個索引。如果 WHERE 條件中指定的列是個二級索引，那麼記錄鎖不僅會加在這個二級索引上，還會加在這個二級索引所對應的聚簇索引上。

注意，如果 SQL 語句無法使用索引時會走主索引實現全表掃描，這個時候 MySQL 會給整張表的所有資料行加記錄鎖。

如果一個 WHERE 條件無法通過索引快速過濾，儲存引擎層面就會將所有記錄加鎖後返回，再由 MySQL Server 層進行過濾。在沒有索引時，不僅會消耗大量的鎖資源，增加資料庫的開銷，而且極大的降低了資料庫的並行效能，所以說，更新操作一定要記得走索引（因為更新操作會加X鎖）。

行級鎖的幾種型別：

間隙鎖：LOCK_GAP（只鎖間隙）

間隙鎖是一種區間鎖。鎖加在不存在的空閒空間上，或者兩個索引記錄之間，或者第一個索引記錄，或者最後一個索引之後的空間，用來表示只鎖住一段範圍（一般在進行範圍查詢時且隔離級別在RR或Serializable隔時）。

一般在RR隔離級別下會使用到GAP鎖。使用GAP鎖，主要是為了防止幻讀產生，在被GAP鎖鎖住的區間，不允許插入資料或者更新資料。

間隙鎖的產生條件：innodb的隔離級別為 Repeatable Read 或者 Serializable。

間隙鎖的作用範圍說明：

隔離級別：RR

17.png

以Student表作為樣例資料，id為主鍵，stu_code為學生編號，新增普通索引。

間隙鎖區域定義：

根據檢索條件向左尋找最靠近的值A，作為左區間，向右尋找最靠近的值B，作為右區間，間隙鎖為（A，B）

向左找不到最近的值A，也是就無窮小，作為左區間，向右尋找最靠近的值B，作為右區間，間隙鎖為（無窮小，B）

向左找到最近的值A，作為左區間，向右尋找不到最近的值B，也就是無窮大，作為右區間，間隙鎖為（A，無窮大）

區間（A,B）範例：

事務1：

select * from student where stu_code = 4 for update

事務2：

insert into student vaues(2, 2, 'A')；
insert into student values(4, 5, 'B');

根據事務1的sql語句分析，間隙鎖的範圍是：stu_code = 4記錄是存在的，所以左區間為最近的索引值為stu_code = 3,右區間為最近的索引值為stu_code =7，所以間隙範圍為：（3，7），因此事務2的兩個insert 語句，一個在範圍外，一個在範圍內，在範圍外的能插入，而範圍內的則阻塞，所以（2，2, 'A'）能插入成功；（4，5, 'B'）插入阻塞。

區間（無窮小,B）範例：

事務1:

select * from student where stu_code = 1 for update

事務2:

insert into student vaues(2, 0, 'c')；
insert into student vaues(2, 2, 'r')；
insert into student vaues(5, 2, 'o')；

根據事務1的sql語句分析，間隙鎖的範圍是：stu_code = 1 是存在的，左邊最近沒有記錄，所以是左邊的無窮小，右邊最近的索引值為 stu_code = 3，所以間隙鎖範圍為：（無窮小，3）。所以事務2的第一個和第二個insert sql語句執行被阻塞，是在間隙鎖範圍內的。第三個insert sql語句能執行成功，不在間隙鎖範圍內。

區間（A,無窮大）範例：

事務1:

select * from student where stu_code = 7 for update

事務2:

insert into student vaues(2, 2, 'm')；
insert into student vaues(20, 22, 'j')；

根據事務1的sql語句分析，間隙鎖的範圍是：stu_code = 7 是存在的，左邊最近的索引值為 stu_code = 4，而右邊是沒有索引值的，所以間隙鎖的範圍為：（4，無窮大），第一個inset語句能執行成功，不在間隙範圍內；第二個insert語句執行被阻塞，是在間隙鎖範圍內的。

如果查詢語句在資料庫中沒有記錄，那該怎麼鎖呢？

以上是查詢是有記錄的，如果查詢語句在資料庫中沒有記錄，那該怎麼鎖呢？咱們繼續往下：

事務1：

update student set stu_name = '000' where stu_code = 10

事務2:

insert into student vaues(2, 2, 'm')；
insert into student vaues(20, 22, 'j')；

根據上面的執行語句是找不到記錄的，向左取最近的記錄（10，7，‘小明’）作為左區間，即間隙鎖的範圍是：（7， 無窮大），第一個insert語句不在區間範圍內，能執行成功；第二個insert執行語句在區間內被阻塞，執行失敗。如果事務1的where 條件是大於10，也是向左找最近的記錄值作為左區間，所以間隙鎖的範圍也是：（7， 無窮大）

總結：間隙鎖產生的條件

RR/Serializable隔離級別下：Select ... Where...For Update 時：

只使用唯一索引查詢，並且只鎖定一條記錄時，InnoDB會使用行鎖。

只使用唯一索引查詢，但是檢索條件是範圍檢索，或者是唯一檢索然而檢索結果不存在（試圖鎖住不存在的資料）時，會產生 Next-Key Lock。

使用普通索引檢索時，不管是何種查詢，只要加鎖，都會產生間隙鎖。

同時使用唯一索引和普通索引時，由於資料行是優先根據普通索引排序，再根據唯一索引排序，所以也會產生間隙鎖。

下一鍵鎖：LOCK_ORDINARY，也稱Next-Key Lock

Next-Key鎖是 record lock + gap lock 的組合。和間隙鎖一樣，在 RC 隔離級別下沒有 Next-key 鎖（除非通過修改設定強制開啟），只有 RR/Serializable隔離級別才有。

MySQL InnoDB工作在可重複讀隔離級別（RR）下，並且會以Next-Key Lock的方式對資料行進行加鎖，這樣可以有效防止幻讀的發生。Next-Key Lock是行鎖和間隙鎖的組合，當InnoDB掃描索引記錄的時候，會首先對索引記錄加上行鎖（Record Lock），再對索引記錄兩邊的間隙加上間隙鎖（Gap Lock）。加上間隙鎖之後，其他事務就不能在這個間隙修改或者插入記錄。

當查詢的索引含有唯一屬性（唯一索引，主鍵索引）時，Innodb儲存引擎會對next-key lock進行優化，將其降為record lock，即僅鎖住索引本身，而不是範圍。

插入意向鎖：LOCK_INSERT_INTENSION

插入意向鎖，插入記錄時使用，是一種特殊的間隙鎖。這個鎖表示插入的意向，只有在執行insert語句的時候才會有這個鎖。

假設有索引記錄的值分別是id = 1和id = 5（1到5之間沒有記錄），單獨的事務分別嘗試插入id = 2和 id = 3，在獲得插入行的排它鎖之前，每個事務都是用插入意圖鎖來鎖定1和5之間的空間，但是不會相互阻塞。因為插入意向鎖之間是不會衝突的。

插入意向鎖會跟間隙鎖或者Next-Key鎖衝突：間隙鎖的作用是鎖住區間防止其他事務插入資料導致幻讀。

在上面的場景中，假設提前有事務A獲取了id 在（1，5）區間的間隙鎖，那麼事務B嘗試插入 id = 2時，會先嚐試獲取插入意向鎖，但是由於插入意向鎖和間隙鎖衝突，導致插入失敗，也就避免了幻讀產生。

結語

MYSQL的鎖機制非常複雜，在實際的開發工作中，對於隔離級別的設定都需要非常謹慎，比如RR級別會比RC級別多出一個間隙鎖，這就可能導致嚴重的效能問題。本文從鎖的模式和鎖的範圍對MYSQL鎖的分類進行了簡單介紹，希望我們在面向資料庫開發的過程中，能夠仔細分析研究我們的SQL語句是否合理（尤其需要注意是否會產生死鎖等問題）！

推薦學習：

以上就是一起聊聊MYSQL中鎖的各種模式與型別的詳細內容，更多請關注TW511.COM其它相關文章！