MySQL中都有哪些鎖

為什么需要鎖

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

按訪問方式分類

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

共享鎖

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

排他鎖

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

兼用性	X 鎖	S 鎖
X 鎖	不兼容	不兼容
S 鎖	不兼容	兼容

按鎖的粒度分類

在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 Lock	IS Lock	X Lock	IX Lock
S Lock	兼容	兼容	不兼容	不兼容
IS Lock	兼容	兼容	不兼容	兼容
X Lock	不兼容	不兼容	不兼容	不兼容
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的值進行如下總結如下：

`LOCK_MODE`值	IS	IX	S	X	S,GAP	X,GAP	S,REC_NOT_GAP	X,REC_NOT_GAP
加鎖情況	意向共享鎖	意向排他鎖	臨鍵鎖S鎖	臨鍵鎖X鎖	間隙鎖S鎖	間隙鎖X鎖	行級S鎖	行鎖X鎖

參考

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

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

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/555227.html

標籤：其他

上一篇：2種GaussDB(DWS)查看作業運行資訊方式

下一篇：返回列表

MySQL中都有哪些鎖？