大家好，我是小林。

昨天在群里看到大家在討論一個 MySQL 鎖的問題，就是執行 select ... for update 語句，如果查詢條件沒有索引字段的話，是加「行鎖」還是加「表鎖」？

如果你做過這個實驗的話，你會發現執行 select ... for update 語句的時候，如果查詢條件沒有索引字段的話，整張表都無法進行增刪改了，從這個現象看，好像是把表鎖起來了，那難道是因為表鎖的原因嗎？

先不著急說結論。

MySQL 有提供分析數據表加了什么鎖的命令，我們就通過這種方式來看看具體加的是什么鎖，才導致整張表都無法進行增刪改了。

做好準備

為了方便后續故事的展開，先創建一張 t_user 表。

表里有一個主鍵索引（id 字段），其他字段都不是索引字段，而是普通字段，表里面有下面這三條記錄。

一條 select 語句會加什么鎖？

不知道大家有沒有好奇過，執行一條 select 查詢語句會加什么鎖呢？

相信大家都知道普通的 select 查詢（快照讀）語句是不會加行級鎖（Innodb 層的鎖），因為它是通過 MVCC 技術實現的無鎖查詢。

要驗證這個結論也很簡單，在 MySQL 8.0 以上的版本，可以執行 select * from performance_schema.data_locks\G; 這條語句，查看 Innodb 存儲引擎為事務加了什么鎖。

假設事務 a 執行了這條普通 select 的查詢語句：

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

mysql> select * from t_user where age < 20;
+----+--------+-----+------------+
| id | name   | age | reward     |
+----+--------+-----+------------+
|  1 | 路飛   |  19 | 3000000000 |
+----+--------+-----+------------+
1 row in set (0.00 sec)

mysql> 

select * from performance_schema.data_locks\G;? 這條語句，輸出結果如下：

可以看到，輸出結果是空，說明普通 select 的查詢語句， Innodb 存儲引擎不會為事務加任何鎖。

那難道什么鎖都不加嗎？

當然不是的。

當我們對數據庫表進行 DML 和 DDL 操作的時候，MySQL 會給這個表加上 MDL 鎖，即元數據鎖，MDL 鎖是 server 層實現的表級鎖，適用于所有存儲引擎。

• 對一張表進行增刪查改操作（DML 操作）的時候，加的是MDL 讀鎖；
• 對一張表進行表結構變更操作（DDL 操作）的時候，加的是MDL 寫鎖；

之所以需要 MDL 鎖，就是因為事務執行的時候，不能發生表結構的改變，否則就會導致同一個事務中，出現混亂的現象，如果當前有事務持有 MDL 讀鎖，DDL 操作就不能申請 MDL 寫鎖，從而保證表元數據的數據一致性。

MDL 的讀鎖與寫鎖滿足讀讀共享，讀寫互斥，寫寫互斥的關系，比如：

• 讀讀共享：MDL 讀鎖和 MDL 讀鎖之間不會產生阻塞，就是說增刪改查不會因為 MDL 讀鎖產生而阻塞，可以并發執行，如果不是這樣，數據庫就是串行操作了；
• 讀寫互斥：MDL 讀鎖和 MDL 寫鎖之間相互阻塞，即同一個表上的 DML 和 DDL 之間互相阻塞；
• 寫寫互斥：MDL 寫鎖和 MDL 寫鎖之間互相阻塞，即兩個 session 不能同時對一張表結構做變更操作，需要串行操作；

如果在工作中，發現很多會話執行的 SQL 提示”Waiting for table metadata lock”的等待，這時候就是因為 MDL 的讀鎖與寫鎖發生沖突了，如果要應急解決問題，這時候就要考慮 kill 掉持有 MDL 鎖的事務了，因為 MDL 鎖是在事務提交后才會釋放，這意味著事務執行期間，MDL 鎖是一直持有的。

如何查看事務是否持有 MDL 鎖？

在前面，我們的事物 A 執行了普通 select 查詢語句，如果要看該事務持有的 MDL 鎖，可以通過這條命令 select * from performance_schema.metadata_locks;。

可以看到，事務 A 此時持有一個表級別的 MDL 鎖，鎖的類型是 SHARED_READ，也就是 MDL 讀鎖。

對于，增刪改操作，申請的  MDL 鎖的類型是 SHARED_WRITE，它也屬于 MDL 讀鎖，因為 SHARED_WRITE 與 SHARED_READ 這兩個鎖的類型是相互兼容的。

因此，我們常說的普通查詢不加鎖，其實指的是不加 Innodb 的行級鎖，但實際上是需要持有 MDL 鎖的。

一條 select ... for update 會加什么鎖？

select ... for update 語句屬于鎖定讀語句，它會對表的記錄加 X 型的行級鎖。

不同隔離級別下，行級鎖的種類是不同的。

在讀已提交隔離級別下，行級鎖的種類只有記錄鎖，也就是僅僅把一條記錄鎖上。

在可重復讀隔離級別下，行級鎖的種類除了有記錄鎖，還有間隙鎖（目的是為了避免幻讀），所以行級鎖的種類主要有三類：

• Record Lock，記錄鎖，也就是僅僅把一條記錄鎖上；
• Gap Lock，間隙鎖，鎖定一個范圍，但是不包含記錄本身；
• Next-Key Lock：Record Lock + Gap Lock 的組合，鎖定一個范圍，并且鎖定記錄本身。

行級鎖加鎖規則比較復雜，不同的場景，加鎖的形式是不同的。

加鎖的對象是索引，加鎖的基本單位是 next-key lock，它是由記錄鎖和間隙鎖組合而成的，next-key lock 是前開后閉區間，而間隙鎖是前開后開區間。

但是，next-key lock 在一些場景下會退化成記錄鎖或間隙鎖。

那到底是什么場景呢？總結一句，在能使用記錄鎖或者間隙鎖就能避免幻讀現象的場景下， next-key lock 就會退化成記錄鎖或間隙鎖。

這次我們只討論，執行 select ... for update 語句，如果查詢條件沒有索引字段的話，會加什么鎖？

現在假設事務 A 執行了下面這條語句，查詢條件中 age 不是索引字段。

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

mysql> select * from t_user where age < 20 for update;
+----+-----------+-----+------------+
| id | name      | age | reward     |
+----+-----------+-----+------------+
|  1 |  路飛   |  19 | 3000000000 |
+----+-----------+-----+------------+
1 rows in set (0.00 sec)

這時候有其他事務對這張表進行增刪改，都會發生阻塞。

先來看看，事務 A 持有什么類型的 MDL 鎖？

可以執行 select * from performance_schema.metadata_locks\G; 這條語句，查看事務 A 此時持有了有什么類型的 MDL 鎖。

執行結果如下：

可以看到，事務 A 此時持有一個表級別的 MDL 鎖，鎖的類型是 SHARED_WRITE，屬于 MDL 讀鎖。

而在前面我提到過，當事務對表進行增刪查改操作的時候，事務會申請 MDL 讀鎖，而 MDL 讀鎖之間是相互兼容的。

所以，當事務 A 執行了查詢條件沒有索引字段的 select ... for update 語句后，不可能是因為事務 A 持 MDL 讀鎖，才導致其他事務無法進行增刪改操作。

再來看看，事務  A 持有哪些行級鎖？

可以執行 select * from performance_schema.data_locks\G; 這條語句，查看事務 A 此時持有了哪些行級鎖。

輸出結果如下，我刪減了不必要的信息：

從上圖可以看到，共加了兩種類型的鎖，分別是：

• 1 個表級鎖：X 類型的意向鎖（表級別的鎖）；
• 4 個行級鎖：X 類型的行級鎖；

什么是意向鎖？

在 InnoDB 存引擎中，當事務執行鎖定讀、插入、更新、刪除操作后，需要先對表加上「意向鎖」，然后再對記錄加「行級鎖」。

之所以要設計「意向鎖」，目的是為了快速判斷表里是否有行級鎖，具體的說明參見：MySQL 全局鎖、表級鎖、行級鎖，你搞清楚了嗎？

意向鎖不會和行級鎖發生沖突，而且意向鎖之間也不會發生沖突，意向鎖只會和共享表鎖（lock tables ... read）和獨占表鎖（lock tables ... write）發生沖突。

所以，當事務 A 執行了查詢條件沒有索引字段的 select ... for update 語句后，不可能是因為事務 A 持有了意向鎖，才導致其他事務無法進行增刪改操作。

具體是哪 4 個行級鎖？

圖中 LOCK_TYPE 中的 RECORD 表示行級鎖，而不是記錄鎖的意思：

• 如果 LOCK_MODE 為X，說明是 X 型的 next-key 鎖；
• 如果 LOCK_MODE 為X, REC_NOT_GAP，說明是 X 型的記錄鎖；
• 如果 LOCK_MODE 為X, GAP，說明是 X 型的間隙鎖；

然后通過 LOCK_DATA 信息，可以確認 next-key 鎖的范圍，具體怎么確定呢？

根據我的經驗，如果 LOCK_MODE 是 next-key 鎖或者間隙鎖，那么 LOCK_DATA 就表示鎖的范圍最右值，而鎖范圍的最左值為 LOCK_DATA 的上一條記錄的值。

因此，此時事務 A 在主鍵索引（INDEX_NAME : PRIMARY）上加了 4 個 next-key 鎖，如下：

• X 型的 next-key 鎖，范圍：(-∞, 1]
• X 型的 next-key 鎖，范圍：(1, 2]
• X 型的 next-key 鎖，范圍：(2, 3]
• X 型的 next-key 鎖，范圍：(3, +∞]

這相當于把整個表給鎖住了，其他事務在對該表進行增、刪、改操作的時候 都會被阻塞。只有在事務 A 提交了事務，事務 A 執行過程中產生的鎖才會被釋放。

為什么因為事務 A 對表所有記錄加了 X 型的 next-key 鎖后，其他事務就無法進行增、刪、改操作了呢？

其他事務在執行「刪除或者更新操作」的時候，也會申請 X 型的  next-key 鎖，next-key 鎖是包含記錄鎖和間隙鎖的，間隙鎖之間雖然是相互兼容的，但是記錄鎖之間存在 X 型和 S 型的關系，即讀讀共享、讀寫互斥、寫寫互斥的關系。

所以當事務 A 持有了  X 型的 next-key 鎖后，其他事務就無法申請 X 型的  next-key 鎖，從而發生阻塞。

比如，前面的例子，事務 B 在更新 id = 1 的記錄的時候，它會申請 X 型的記錄鎖（唯一索引等值操作，  next-key 鎖會退化為記錄鎖），但是因為事務 A 持有了 X 型的 next-key 鎖，所以事務 B 在申請 X 型的記錄鎖的時候，會發生阻塞。

我們也可以通過   select * from performance_schema.data_locks\G;? 這條語句得知。

事務 C 的刪除操作被阻塞的原因，也是這個原因。

事務 D 的插入操作被阻塞的原因，跟事務 B 和事務 C 的原因不同。

插入語句在插入一條記錄之前，需要先定位到該記錄在 B+樹 的位置，如果插入的位置的下一條記錄的索引上有間隙鎖，如果已加間隙鎖，此時會生成一個插入意向鎖，然后鎖的狀態設置為等待狀態，現象就是插入語句會被阻塞。

事務 D 插入了一條 id = 10 的新記錄，在主鍵索引樹上定位到插入的位置，而該位置的下一條記錄是 supremum pseudo-record，該記錄是一個特殊的記錄，用來標識最后一條記錄，而該特殊記錄上正好持有了間隙鎖（next-key 鎖包含間隙鎖），所以這條插入語句會發生阻塞。

我們也可以通過   select * from performance_schema.data_locks\G; 這條語句得知。

為什么只是查詢年齡 20 歲以下的行記錄，而把整個表給鎖住了呢？

這是因為事務 A 的這條鎖定讀查詢語句，沒有使用索引列作為查詢條件，所以掃描的方式是全表掃描，行級鎖是在遍歷索引的時候加上的，并不是針對輸出的結果加行級鎖。

不只是鎖定讀查詢語句不加索引才會導致這種情況，update 和 delete 語句如果查詢條件不加索引，那么由于掃描的方式是全表掃描，于是就會對每一條記錄的索引上都會加 next-key 鎖，這樣就相當于鎖住的全表。

因此，在線上在執行 update、delete、select ... for update 等具有加鎖性質的語句，一定要檢查語句是否走了索引，如果是全表掃描的話，會對每一個索引加 next-key 鎖，相當于把整個表鎖住了，這是挺嚴重的問題。

如果數據量很大，還是一樣的原因嗎？

前面我們結論得出，如果如果鎖定讀查詢語句，沒有使用索引列作為查詢條件，導致掃描是全表掃描。那么，每一條記錄的索引上都會加 X 型的 next-key 鎖（行級鎖）。正是因為這個原因，才導致其他事務，無法對該表進行增刪改操作。

那如果一張表的數據量超過幾百萬行，還是一樣對每一條記錄的索引上都會加 X 型的 next-key 鎖嗎？

群里有小伙伴提出了這個說法，說如果 MySQL 認為數據量太大時，自動將行所升級到表鎖。

不著急說結論，我們直接做個實驗。

我在 t_user 表插入了 300 多萬條數據。

現在有個事務執行了這條查詢語句，查詢條件 age 字段不是索引字段。

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

mysql> select * from t_user where age < 20 for update;

然后，我們執行 select * from performance_schema.data_locks\G;? 這條語句（我執行了好長時間，至少有幾十分鐘）。

可以看到，每一條記錄的索引上都會加 X 型的 next-key 鎖（行級鎖）。

所以，MySQL 認為數據量太大時，自動將行所升級到表鎖 ，這句話并不準確。

總結

在執行 select … for update 語句的時候，會有產生 2 個表級別的鎖：

一個是 Server 層表級別的鎖：MDL 鎖。事務在進行增刪查改的時候，server 層申請 MDL 鎖都是 MDL 讀鎖，而 MDL 讀鎖之間是相互兼容的，MDL 讀鎖只會和 MDL 寫鎖發生沖突，在對表結構進行變更操作的時候，才會申請  MDL 寫鎖。

一個是 Inoodb 層表級別的鎖：意向鎖。事務在進行增刪改和鎖定讀的時候，inoodb 層會申請意向鎖，意向鎖不會和行級鎖發生沖突，而且意向鎖之間也不會發生沖突，意向鎖只會和共享表鎖（lock tables ... read）和獨占表鎖（lock tables ... write）發生沖突。

如果 select … for update 語句的查詢條件沒有索引字段的話，整張表都無法進行增刪改了，從這個現象看，好像是把表鎖起來了，但是并不是因為上面這兩個表級鎖的原因。

而是因為如果鎖定讀查詢語句，沒有使用索引列作為查詢條件，導致掃描是全表掃描。那么，每一條記錄的索引上都會加 next-key 鎖（行級鎖），這樣就相當于鎖住的全表，這時如果其他事務對該表進行增、刪、改操作的時候，都會被阻塞。