MySQL中都有哪些鎖

為什麼需要鎖

在電腦系統中，鎖（Lock）是一種同步機制，用於控制對共用資源的訪問。它確保在任何給定時間內只有一個線程能夠訪問受保護的共用資源，從而避免了由併發訪問導致的數據競爭和不一致問題。
同樣，在資料庫系統中，鎖也扮演著重要角色，是其與文件系統不同的關鍵特性之一。資料庫中存儲的數據也是一種供多用戶訪問的共用資源。為了在多用戶（多線程）訪問的場景下保證數據的一致性、事務的隔離性以及提高資料庫的併發性能等，MySQL實現了各種不同的鎖機制。接下來，讓我們來詳細聊一聊這些鎖。

按訪問方式分類

假設我們對資料庫的操作全是讀操作，在併發訪問下也不會出現數據一致性問題。出現問題的原因是我們對資料庫還有另一個重要的操作，那就是寫。正是由於寫操作會改變數據，才會導致一系列問題的產生。但是如果我們不加以區分，對於所有的操作都加“互斥鎖”，那麼原先那些可以併發執行的讀-讀操作就會被阻塞。影響資料庫併發訪問性能。
基於此，MySQL中實現了一種由兩種類型的鎖組成的鎖系統，即讀寫鎖。讀寫鎖允許多個線程同時獲取讀鎖，以實現讀操作的併發執行，而對於寫操作則會獨占地獲取寫鎖。

共用鎖

共用鎖(Shared Lock)，又稱為讀鎖或S鎖；它允許多個事務同時獲取鎖並讀取同一份數據。當一個事務獲取共用鎖後，其他事務也可以獲取相同的共用鎖。
共用鎖之間是相容的，多個事務可以併發地持有共用鎖，在進行讀取操作時也不會對其他事務造成阻塞。

排他鎖

排他鎖(Exclusive Lock)，又稱為寫鎖、獨占鎖或X鎖；它只允許一個事務獲取並持有該鎖。當一事務獲取到X鎖後，其他事務無法同時獲取X鎖或者S鎖，必須等待X鎖的釋放。
X鎖可以防止併發修改操作引起的數據衝突問題。
依據共用鎖和排他鎖的特性，我們可以得出兩者之間的相容性列表：

按鎖的粒度分類

在MySQL中，根據數據所在的維度，可以大致分為資料庫級別、表級別和行級別。在這些維度上加鎖會有很大的區別，MySQL在這些維度上分別提供了不同的鎖實現。

全局鎖

全局鎖的加鎖和解鎖

全局鎖，也就是對整個資料庫實例進行加鎖，MySQL提供了一個加全局讀鎖的命令：

flush tables with read lock

也就是 FTWRL的全稱。當執行這條命令後，整個MySQL資料庫就處於只讀狀態。此時對於任何數據的寫操作，或者表結構的修改操作都會被阻塞。在這個狀態下只允許查詢操作。
我們可以執行如下命令來手動釋放全局鎖；

unlock tables

或者直接斷開會話，全局鎖也會被自動釋放。

全局鎖的使用場景

全局鎖的典型使用場景只有一種，那就是做全庫的邏輯備份。因為在加全局鎖期間，全庫數據和表結構不會被修改，也就保證了備份數據的一致性。
但是使用全局鎖來做全庫備份也存在一些問題：

• 如果我們備份時間很長，那麼資料庫就會有很長一段時間內不能更新數據，這將會嚴重影響業務。
• 如果我們在從庫上做備份，同樣的我們在備份期間就不能及時處理主庫同步過來的binlog，這將會導致主從延遲。

有些人會想到，既然做全庫邏輯備份，只要將資料庫設置為只讀就行，那為什麼不直接 set global readonly = true，讓整個資料庫實例處於只讀模式。的確，這樣也能做全庫備份，但是這和 FTWRL沒有實質區別，對業務的影響還是很大。而且在某些情況下，還會存在用戶許可權不夠、或者readonly值用來做其他主從庫區分等情況。所以一般也不建議使用這種方式。
那麼我們一般怎樣來做全庫邏輯備份呢？
MySQL官方提供一個邏輯備份工具，叫作mysqldump。我們可以在其後加一個--single-transaction參數來指定做全庫邏輯備份時，使用一致性快照讀。這樣就避免了加全局鎖的操作。例如：

-- 使用一致性快照讀的方式，邏輯備份 userDb 資料庫到 userBackup.sql
mysqldump -u root -p --single-transaction userDb > userBackup.sql

但是有一點要註意的是，這種全庫邏輯備份的方式只適用於資料庫中所有表的存儲引擎都是InnoDB的。

表級鎖

在MySQL中，表級別的鎖大概有四種，每種使用的場景都不一樣。

表鎖

表鎖(Table Lock)，也就是對某張表加鎖。具體來說，表鎖按訪問方式，可以分為共用表鎖(S鎖) 和排他表鎖(X鎖)。
假設我們要加鎖的表是user，那麼就可以使用下麵的語句來加鎖：

-- 給 user 表加 共用讀鎖
lock tables user read

-- 給 user 表加 獨占寫鎖
lock tables user write

與FTWRL一樣，解鎖使用的也是unlock tables語句來釋放當前會話下所有的表鎖。另外如果退出會話的話，表鎖也會被自動釋放。
在沒有出現更細粒度的鎖之前，MySQL一般都是使用表鎖來處理併發。而現在，我們不推薦使用表鎖，因為InndoDB存儲引擎提供了更加細粒度的行鎖支持，處理併發時性能更好。

元數據鎖(MDL)

假設我們在訪問數據的同時，另一個用戶對錶結構進行了修改，新增了一列，我們查詢出來的數據不包含這一列，這肯定是不行的。元數據鎖(Metadata Lock) 正是用來處理這一類問題。
元數據鎖不需要我們顯示的進行聲明和釋放，當訪問一張表時，它會被自動加上。具體來說：

• 當我們對錶數據進行CRUD時，會自動加上元數據讀鎖(S鎖)
• 當我們對錶結構進行修改時，會自動加上元數據寫鎖(X鎖)

讀鎖和寫鎖的相容性和前面表格中列的一樣。需要註意的時，元數據鎖在語句執行完之後不會立馬釋放，而是等到事務提交之後，才會釋放。
雖然說元數據鎖不需要用戶手動來操作申請和釋放，但是在某些場景下，也會導致問題發生。假設某個表有比較頻繁的查詢請求，並且有超時重試機制，在中途如果存在表結構的修改操作，那麼很有可能會出現元數據寫鎖與元數據讀鎖相互等待，而造成查詢阻塞的現象。

意向鎖

MySQL的InnoDB存儲引擎是支持多粒度鎖定的，也就是說支持行級鎖和表級鎖共存。為了實現這一特性，InnoDB設計了意向鎖(Intention Lock)這一表級鎖。其作用就是為了指明在當前這個事務中，接下來會對這個表中的一些行加什麼鎖。意向鎖也分為兩類：

• 意向共用鎖（IS Lock）：當事務想要獲取一張表中某幾行的行級共用鎖(S鎖)時，MySQL會先自動獲取該表的意向共用鎖。
• 意向排他鎖（IX Lock）：當事務想要獲取一張表中某幾行的行級排他鎖(X鎖)時，MySQL會先自動獲取該表的意向排他鎖。

首先，我們要理解MySQL中的行鎖和表鎖為什麼不能共存，怎樣才能共存？我們知道，如果對一張表加上了表級寫鎖，那麼我們就能對該表中的所有行進行修改。如果此時在另外一個事務中，還能對該表中的某幾行加行級寫鎖，這是不被允許的。因為如果同時操作這幾行數據，就有可能出現數據一致性問題。
那麼，在給表加表級X鎖之前，如何知道這個表中某些行是否已經加上了行級鎖呢 ，一個簡單的解決方法是遍歷表中的所有行，一行行去判斷。但是這種方法只適用表數據少情況，如果表數據量非常大，遍歷一遍全表數據效率很低。
給表加意向鎖之後，就能很好的解決這個問題：

• 在事務獲取表中行級S鎖之前，必須先獲取該表的意向共用鎖(IS Lock)或者更強級別的鎖
• 在事務獲取表中行級X鎖之前，必須先獲取該表的意向排他鎖(IX Lock)

意向鎖與意向鎖之間是不衝突的，也就是說互相相容，但是意向鎖和表鎖之間會存在不相容問題，具體的相容性如下表：

依據上面的相容性，我們就能保證行鎖和表鎖能夠安全的共存。例如，當一個事務在申請一張表的某幾行的行級S鎖之前，會先申請該表的意向共用鎖(IS Lock)。如果另外一個事務想要申請該表的表級S鎖，因為和 IS Lock是相容的，所以會獲取鎖成功，兩者共存。但是如果想要申請的是該表的表級X鎖，就會因為不相容而被迫阻塞。
也就是說，通過意向鎖，能夠非常快速的判斷表中的行加了什麼鎖。

自增鎖

我們在創建表時，ID這一列通常會聲明 AUTO_INCREMENT屬性，表示該列是自動遞增的。之後我們在進行插入時，可以不用指定ID列的值，MySQL會自動且遞增的給該列賦值。
對於MySQL提供的這一功能，我們應該會有如下一些疑問：

• 自增的值保存在什麼地方？
• 一定能保證連續遞增嗎，會不會出現不連續情況？
• 自增是如何實現的，如何保證值不會重覆？

自增的值保存在什麼地方？我們應該能想到的是，在每次插入數據時，MySQL能夠自動進行賦值和自增，緩存在記憶體中的概率性最大。
的確如此，在 MySQL 7 及之前，自增值保存記憶體裡面，並且沒有進行持久化。這也就產生一個問題，當資料庫重啟後，第一次打開表時，MySQL會找到這個表中自增列的當前最大值maxId，然後將maxId + 1作為這個表的自增值。但是這個自增值不一定和重啟之前值一樣。
舉例來說，假設在重啟之前，將這個表中自增列為25的最大一條記錄刪除了，當我們進行插入時，自增的值並不會回退到25，而是使用26。但是在重啟之後，因為查詢到自增列的當前最大值maxId = 24，自增值回退到了25。
在 MySQL 8 版本後，自增值增加了持久化能力，記錄在undo_log裡面，重啟後，靠undo_log進行恢復，也就不會出現之前的問題了。
自增的值會不會出現不連續的現象？要回答這個問題，首先要知道MySQL是如何給一條未指定自增列的插入SQL自動賦值和遞增自增值的。具體來說分為下麵幾步：

• 當MySQL發現插入SQL未指定自增列的值時，先從記憶體獲取當前的自增值 inc
• 修改插入SQL，指定自增列的值為inc
• 將記憶體中當前的自增值進行+1操作
• 繼續執行SQL，進行插入

假設在最後一步執行SQL，進行插入時出現了Duplicate key error。那麼事務就會進行回滾。該行插入失敗。但是我們發現自增列的值inc卻已經進行了+1操作。下一次再進行插入時，獲取到的自增列的值和資料庫中已經存在的自增列的值就會不連續。因為上一次的事務插入的行因為失敗回滾了。
為什麼在事務回滾時，不一起把自增列的值一起回退了。回退之後不就能保證自增值遞增且連續了。我們可以想一下，如果回退了，是不是就會更有可能出現Duplicate key error問題，因為你不能保證自增之後，其他事務是否已經使用了自增之後的值。而且解決這個問題的成本也比較高，所以MySQL中的自增值，只保證了自增，沒有保證連續。
前面說了這麼多，還有最後一個關鍵問題：自增是如何實現的，如何保證值不會重覆？其實在 MySQL InnoDB 存儲引擎的記憶體結構裡面，對於每張包含自增列的表，都維護了一個自增長計數器(auto-increment counter)，每當進行插入時，就會對這個計數器進行+1操作，而這個操作則是由AUTO-INC鎖，也就是自增鎖來實現的。
自增鎖它是一種特殊的表鎖。在對計數器進行+1操作前加上，這樣就保證了併發自增的安全性，不會出現重覆現象。為了提供插入的性能，自增鎖並不會等到事務結束才會釋放，而是在插入完成之後就立即釋放了。
但是自增鎖在批量插入時，會存在一定程度的性能問題，所以 MySQL在 5.1.22 版本中引入了一個新策略，新增參數innodb_autoinc_lock_mode來切換自增長的實現，這個參數有 3 個取值：

• 0：MySQL 5.1.22 版本之前的實現方式，採用AUTO-INC這種表鎖的方式來實現自增列的自增長。
• 1：MySQL 7 及之前的預設值，對於普通insert操作，採用更加輕量級的互斥量(mutex)來實現計數器的自增。而對於insert ... select這種批量插入，還是採用 AUTO-INC鎖來實現。
• 2：MySQL 8 的預設值(在binlog_format使用預設值row時)，對於所有的insert操作，都採用更加輕量級的互斥量(mutex)來實現計數器的自增。

最後，對於自增還有一個要說的點是：自增的規則是什麼？假設有一張表user, 其中id欄位是自增的，一般我們會使用如下方式來進行插入：

insert into user (id, name, age) values ('Dmego', 25);

也就是說如果我們不寫id這一列值，MySQL會預設給賦上。除了這樣寫，其實還有幾種方式：

-- 指定 null，表示該列值使用自增值
insert into user (id, name, age) values (null, 'Dmego', 25);
-- 指定 0，表示該列值使用自增值
insert into user (id, name, age) values (0, 'Dmego', 25);

其中指定值 0還有一個特殊的情況需要註意一下，就是不能在啟用了NO_AUTO_VALUE_ON_ZEROSQL 模式下使用。具體可以參考MySQL的官方手冊說明。
在id列自增的前提下，手動指定id列的值行嗎？是可以的，但是有些區別。假設目前的自增值是inc，手動指定的id列值是Y，有如下規則：

• 如果Y < inc ，則id還是會使用自增值inc。
• 如果Y >= inc，則 id會使用手動指定的值Y，並且自增值inc會變成Y + 1。

行級鎖

MyISAM存儲引擎只有表鎖，是不支持行級鎖的，而InnoDB存儲引擎不僅支持事務，還支持更高效和細粒度的行級鎖。總的來說，共有三種重要的行級鎖機制。

行鎖(Record Lock)

我們知道，MySQL使用多版本併發控制(MVCC) 解決了不可重覆讀問題。並且保證了讀-寫不會產生衝突，也沒有使用鎖。對於普通的 select ... 操作，使用的就是 MVCC，這種讀取也叫做“快照讀”或者“一致性讀”；也就是說，讀取的數據來自於一致性視圖，也就是歷史數據。
如果查詢都是這樣，不就不需要行級鎖了嗎？其實，在很多業務場景下，“快照讀”並不能滿足需求，並且也不能解決丟失更新、幻讀等事務類問題。此時就需要讀取最新的數據併進行加鎖後再處理。這種讀取也被稱為“鎖定讀”。
在InnoDB存儲引擎中，對某一行加的鎖被稱為行鎖(Record Lock),根據訪問方式不同，行鎖有S鎖和X鎖之分，從具體的查詢語句來看：

-- 對查詢的記錄加 S 鎖
select ... lock in share mode
-- 對查詢的記錄加 X 鎖
select ... for update

另外，當我們在執行UPDATE、DELETE等操作帶WHERE查詢條件時，在內部其實也會使用“鎖定讀”的方式先將需要的行記錄查詢出來，再加上X鎖，最後才進行修改操作。
行鎖在需要的時候就會被加上，但是不是語句執行完後就立馬釋放了，而是等到事務提交之後才會被釋放。這也就是兩階段鎖協議(2PL)。依據這個特性，我們可以有得出下麵一條使用經驗：如果事務中需要鎖多個行，要把最可能造成鎖衝突、最可能影響併發度的鎖儘量往後放。

間隙鎖(Gap Lock)

在介紹間隙鎖之前，首先得說一下為什麼需要間隙鎖？在事務中，我們知道會有幻讀這個問題，簡單來講，就是在一個事務中，在不同時間段，對於同一範圍內的查詢，查詢到的數據條數不一樣。好像出現“幻覺”一樣。而間隙鎖正是為了避免幻讀問題而出現的。
舉個例子，有一條範圍查詢的SQL語句是這樣寫：

select * from user where id <= 5 and id >= 10 for update;

也就是使用“鎖定讀”的方式查詢user表中id列在[5, 10]區間內的數據。如果我們只單單鎖住id = 5和 id = 10這兩條行記錄，是不行的，因為其他事務有可能會插入id = 7這樣的數據行，當我們再次使用“鎖定讀”來查詢時，就能查到id = 7的記錄。也就是說我們沒法對錶中並不存在的數據行進行鎖定。
間隙鎖(Gap Lock)如同其名稱一樣，它鎖定的並不是某行記錄，而是行與行之間的某個間隙。能夠保證鎖定這個間隙之後，其他事務不能在這個間隙里插入任何行記錄。

如上示意圖中，在id = 5和id = 10兩行記錄之間，存在區間(5, 10)，間隙鎖正是鎖住這個區間。其他事務無法在這個區間內插入任何行，一直到事務結束後，間隙鎖被釋放。
在上圖中，有些人可能會註意到，id列的首尾是兩個沒有值的列，這其實這是InnoDB存儲引擎在數據頁中插入的兩條偽記錄：

• Infimum記錄：表示該頁中最小的記錄
• Supremum記錄：表示該頁中最大的記錄

那麼這兩個記錄標出來有什麼用呢？其實是想說兩種特殊的範圍查詢情況：

-- 查詢 id 值小於 5 的所有記錄
select * from user where id < 5;
-- 查詢 id 值大於 25 的所有記錄
select * from user where id > 25;

在查詢id < 5的所有記錄時，查詢的區間是 (-∞，5)，在使用間隙鎖鎖定這個區間時，鎖定的範圍就是(infimum, 5)；同理，在查詢id > 25的所有記錄時，鎖定的範圍是(25, supermum)。標出這兩個偽記錄，也是為了更方便理解“間隙”這個概念。
間隙鎖是否有S鎖和X鎖之分呢？其實是有的，但是並沒有什麼區別，也沒有不相容的情況。因為我們要理解間隙鎖的目的：鎖定某個區間，其他事務不能在這個區間插入任何行記錄，避免幻讀。因此不管你在這個區間加多少個間隙鎖，其本質目的是一樣的，所以不會出現衝突。

臨鍵鎖(Next-key Lock)

臨鍵鎖(Next-Key Lock) 其實就是行鎖(Record Lock) 和間隙鎖(Gap Lock) 的組合。在鎖定一個區間的同時，會使用行鎖鎖定這個區間的右邊界行記錄，也就是說，Next-key Lock鎖定的範圍是一個左開右閉區間：(, ]。示意圖如下：

在MySQL中，加鎖的基本單位就是Next-Key Lock。後續在分析一條SQL執行到底加了什麼鎖時，再詳細說一下Next-Key Lock。

插入意向鎖(Insert Intention Lock)

在行級鎖裡面，其實還有一種鎖，叫作插入意向鎖，從名稱上看，應該屬於意向鎖一類，但它其實是一個行級鎖。那麼插入意向鎖有什麼作用呢？也沒什麼大用，只是因為InnoDB存儲引擎規定：在事務阻塞等待期間，必鬚生成鎖結構。所謂的鎖結構其實就是鎖在記憶體中的實體表現。
假設我們要在某個區間要插入一條記錄時，發現這個區間上正好被一個Gap Lock鎖住。此時這個插入操作就會被阻塞。在阻塞等待時，必須要生成一個鎖結構，這個就是插入意向鎖。
插入意向鎖也可以看作是一種特殊的間隙鎖，鎖住的是一個點。表明有事務想要在該區間的這個位置插入記錄，但是被該區間的Gap Lock阻塞了，現在處於等待狀態。

隱式鎖

在記憶體中，生成一個鎖結構並維護它並不容易，所以減少鎖結構的生成，對性能也有提升。“隱式鎖”就是這個目的。但是“隱式鎖”並不是真正的鎖。
當我們在進行插入操作前，如果沒有其他事務對這條記錄或者這條記錄所在的間隙加鎖，那麼就可以不用生成一個鎖結構。如果執行過程中，別的事務需要對這條記錄或者這條記錄所在間隙加鎖時，就會先給執行插入操作的事務生成一個鎖結構，然後再自己生成一個鎖結構，最後進入等待狀態。
能夠這樣做的原因是有事務ID(trx_id)這個隱藏列的存在。如果記錄上的trx_id和當前事務ID不一樣，那麼就說明需要阻塞等待，也就相當於加了一個隱藏的鎖。
通過上面的描述，我們可以看出，“隱式鎖”其實起到了延遲生成鎖結構的好處，在一些場景下，甚至可以避免生成鎖結構。

如何查看加了哪些鎖

前面長篇大論都只是停留在理論上。在實際操作資料庫時，我們該如何查看和分析執行的SQL加了哪些鎖呢？下麵就通過例子來實踐一下。
以下是舉例說明所用的表結構和初始化語句，需要註意的是，測試基於的MySQL的版本是8.0.32，如果使用其他版本可能會有些差異。

CREATE TABLE user (
  id int NOT NULL,
  number int NOT NULL,
  age int DEFAULT NULL,
  score int DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY idx_number (number),
  KEY idx_age (age)
) ENGINE=InnoDB;

INSERT INTO user VALUES (1, 201, 19, 80);
INSERT INTO user VALUES (5, 206, 13, 95);
INSERT INTO user VALUES (10, 210, 15, 94);
INSERT INTO user VALUES (15, 214, 17, 98);
INSERT INTO user VALUES (20, 218, 21, 90);

查看表級鎖

表鎖

使用client連接到MySQL之後，我們可以在命令行執行lock tables user read 語句來給表加一個S鎖。然後可以通過下麵的操作來查詢出user表上是否存在表鎖：

-- 查看當前打開的表中，是否存在正在使用的。
show open tables where in_use > 0;

如果上面語句執行有返回記錄，例如返回如下信息，就說明user表正在使用，很有可能出現了鎖表的情況。

mysql> show open tables where in_use > 0 ;
+----------+-------+--------+-------------+
| Database | Table | In_use | Name_locked |
+----------+-------+--------+-------------+
| test     | user  |      1 |           0 |
+----------+-------+--------+-------------+
1 row in set (0.00 sec)

元數據鎖

在 Session A中，執行如下SQL給表中某些行加上行級X鎖：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user where id > 5;
+----+--------+------+-------+
| id | number | age  | score |
+----+--------+------+-------+
| 10 |    210 |   15 |    94 |
| 15 |    214 |   17 |    98 |
| 20 |    218 |   21 |    90 |
+----+--------+------+-------+
3 rows in set (0.00 sec)

在Session B中，我們使用alter命令試圖給user表加一個name欄位，但是我們會發現這個命令會被阻塞。

alter table user add column name varchar(32);

新開一個Session C命令行，使用show processlist可以看到類似下麵的返回：

mysql> show processlist\G;
*************************** 1. row ***************************
     Id: 5
   User: event_scheduler
   Host: localhost
     db: NULL
Command: Daemon
   Time: 48369
  State: Waiting on empty queue
   Info: NULL
*************************** 2. row ***************************
...
*************************** 7. row ***************************
     Id: 64
   User: root
   Host: localhost
     db: test
Command: Query
   Time: 2
  State: Waiting for table metadata lock
   Info: alter table user add column name varchar(32)
7 rows in set (0.00 sec)

可以看到，alter語句阻塞的原因是：Waiting for table metadata lock。也就是等待元數據鎖(MDL)釋放。為什麼會阻塞等待，其實我們前面已經說過了，總結來說就是：

• Session A先開啟了一個事務，然後進行select操作，此時MySQL預設給表user加上了元數據S鎖，並且事務未提交，元數據S鎖還沒被釋放。
• 在Session B中執行alter操作前，會先申請表user的元數據X鎖。但是S鎖和X鎖是不相容的，所以Session B出現了阻塞等待現象。

意向鎖

在Session A中執行執行如下SQL，給表中id = 10這行記錄加上S鎖：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user where id = 5 lock in share mode;
+----+--------+------+-------+
| id | number | age  | score |
+----+--------+------+-------+
|  5 |    206 |   13 |    95 |
+----+--------+------+-------+
1 row in set (0.00 sec)

在Session B中，執行如下命令，查詢當前資料庫中鎖情況：

mysql> select * from performance_schema.data_locks\G;
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140409843394792:1192:140410012859648
ENGINE_TRANSACTION_ID: 421884820105448
            THREAD_ID: 104
             EVENT_ID: 35
        OBJECT_SCHEMA: test
          OBJECT_NAME: user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140410012859648
            LOCK_TYPE: TABLE
            LOCK_MODE: IS
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
....
2 rows in set (0.00 sec)

可以看到，第一行記錄中，OBJECT_NAME是user，LOCK_TYPE是TABLE，LOCK_MODE是IS，意思就是說，在user這張表上，存在表級鎖，具體來說是意向共用鎖(IS Lock)。
如果我們把Session A中的查詢語句換成for update，也就是給表中id = 10這行記錄加上X鎖,那麼在Session B中查詢出來的記錄的LOCK_MODE欄位值就會變成IX，也就是意向排他鎖(IX Lock)。

查看行級鎖

行鎖

和上一節查詢意向鎖操作一樣，其實在Session B裡面，查詢出來的記錄有2條，前面把第2條省略了，該記錄如下：

mysql> select * from performance_schema.data_locks\G;
*************************** 1. row ***************************
....(IS 鎖記錄，省略)
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140409843394792:130:4:3:140410014072352
ENGINE_TRANSACTION_ID: 421884820105448
            THREAD_ID: 104
             EVENT_ID: 35
        OBJECT_SCHEMA: test
          OBJECT_NAME: user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140410014072352
            LOCK_TYPE: RECORD
            LOCK_MODE: S,REC_NOT_GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 5
2 rows in set (0.00 sec)

可以看到，這裡的LOCK_TYPE變成了RECORD,也就是行；LOCK_MODE是S, REC_NOT_GAP，LOCK_DATA是5，這是什麼意思呢？其實這就表明對id = 5這一行記錄加了行級S鎖。同理，如果Session A的查詢換成for update。這裡的LOCK_MODE也會變成X,REC_NOT_GAP。

間隙鎖

我們在Session A中，執行如下SQL，使用“鎖定讀”的方法查詢id在(-∞, 1)範圍內的數據：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user where id < 1 for update;
Empty set (0.00 sec)

在Session B中，執行如下命令，查詢當前資料庫中鎖情況：

mysql> select * from performance_schema.data_locks\G;
*************************** 1. row ***************************
...(IX 鎖記錄，省略)
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140409843394792:130:4:2:140410014072352
ENGINE_TRANSACTION_ID: 115043
            THREAD_ID: 104
             EVENT_ID: 54
        OBJECT_SCHEMA: test
          OBJECT_NAME: user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140410014072352
            LOCK_TYPE: RECORD
            LOCK_MODE: X,GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 1
2 rows in set (0.00 sec)

我們可以看到在第2行記錄中，LOCK_MODE值為X,GAP，LOCK_DATA值為1。也就是區間(infimum, 1)被加上了間隙鎖(Gap Lock)。

臨鍵鎖

前面說過，臨鍵鎖(Next-Key Lock) 其實就是行鎖(Record Lock) 和間隙鎖(Gap Lock) 的組合。也就是不僅會鎖定一個區間間隙，還會鎖定該間隙的右邊界值。
在Session A中，執行如下SQL來查詢id值在(1，5] 範圍內的數據：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user where id > 1 and id <= 5 for update;
+----+--------+------+-------+
| id | number | age  | score |
+----+--------+------+-------+
|  5 |    206 |   13 |    95 |
+----+--------+------+-------+
1 row in set (0.00 sec)

在Session B中，我們執行如下命令，查詢當前資料庫中鎖情況：

mysql> select * from performance_schema.data_locks\G;
*************************** 1. row ***************************
...(IX 鎖記錄，省略)
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140409843394792:130:4:3:140410014072352
ENGINE_TRANSACTION_ID: 115046
            THREAD_ID: 104
             EVENT_ID: 69
        OBJECT_SCHEMA: test
          OBJECT_NAME: user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140410014072352
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 5
2 rows in set (0.00 sec)

我們可以看到第2行記錄中，LOCK_MODE值為X，LOCK_DATA值為5。也就是區間(1, 5]被加上了臨鍵鎖(Next-Key Lock)。
通過上面的實踐與分析，對於使用：

select * from performance_schema.data_locks\G;

語句來查看加了什麼鎖，我們可以根據LOCK_MODE的值進行如下總結如下：

參考

• 《MySQL技術內幕：innodb存儲引擎》第2版
• 《極客時間：MySQL實戰45講》
• 《MySQL是怎樣運行的：從根兒上理解MySQL》
• MySQL 8.0 Reference Manual

歡迎關註我的公眾號“Dmego”，原創技術文章第一時間推送。