如果查詢條件沒有索引字段的話，是加「行鎖」還是加「表鎖」？

作者：小林coding 2022-12-18 16:56:07

如果 select … for update 語句的查詢條件沒有索引字段的話，整張表都無法進(jìn)行增刪改了，從這個(gè)現(xiàn)象看，好像是把表鎖起來了，但是并不是因?yàn)樯厦孢@兩個(gè)表級(jí)鎖的原因。

大家好，我是小林。

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

如果你做過這個(gè)實(shí)驗(yàn)的話，你會(huì)發(fā)現(xiàn)執(zhí)行 select ... for update 語句的時(shí)候，如果查詢條件沒有索引字段的話，整張表都無法進(jìn)行增刪改了，從這個(gè)現(xiàn)象看，好像是把表鎖起來了，那難道是因?yàn)楸礞i的原因嗎？

先不著急說結(jié)論。

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

做好準(zhǔn)備

為了方便后續(xù)故事的展開，先創(chuàng)建一張 t_user 表。

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

一條 select 語句會(huì)加什么鎖？

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

相信大家都知道普通的 select 查詢（快照讀）語句是不會(huì)加行級(jí)鎖（Innodb 層的鎖），因?yàn)樗峭ㄟ^ MVCC 技術(shù)實(shí)現(xiàn)的無鎖查詢。

要驗(yàn)證這個(gè)結(jié)論也很簡單，在 MySQL 8.0 以上的版本，可以執(zhí)行 select * from performance_schema.data_locks\G; 這條語句，查看 Innodb 存儲(chǔ)引擎為事務(wù)加了什么鎖。

假設(shè)事務(wù) a 執(zhí)行了這條普通 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;? 這條語句，輸出結(jié)果如下：

可以看到，輸出結(jié)果是空，說明普通 select 的查詢語句， Innodb 存儲(chǔ)引擎不會(huì)為事務(wù)加任何鎖。

那難道什么鎖都不加嗎？

當(dāng)然不是的。

當(dāng)我們對(duì)數(shù)據(jù)庫表進(jìn)行 DML 和 DDL 操作的時(shí)候，MySQL 會(huì)給這個(gè)表加上 MDL 鎖，即元數(shù)據(jù)鎖，MDL 鎖是 server 層實(shí)現(xiàn)的表級(jí)鎖，適用于所有存儲(chǔ)引擎。

對(duì)一張表進(jìn)行增刪查改操作（DML 操作）的時(shí)候，加的是MDL 讀鎖；
對(duì)一張表進(jìn)行表結(jié)構(gòu)變更操作（DDL 操作）的時(shí)候，加的是MDL 寫鎖；

之所以需要 MDL 鎖，就是因?yàn)槭聞?wù)執(zhí)行的時(shí)候，不能發(fā)生表結(jié)構(gòu)的改變，否則就會(huì)導(dǎo)致同一個(gè)事務(wù)中，出現(xiàn)混亂的現(xiàn)象，如果當(dāng)前有事務(wù)持有 MDL 讀鎖，DDL 操作就不能申請(qǐng) MDL 寫鎖，從而保證表元數(shù)據(jù)的數(shù)據(jù)一致性。

MDL 的讀鎖與寫鎖滿足讀讀共享，讀寫互斥，寫寫互斥的關(guān)系，比如：

讀讀共享：MDL 讀鎖和 MDL 讀鎖之間不會(huì)產(chǎn)生阻塞，就是說增刪改查不會(huì)因?yàn)?MDL 讀鎖產(chǎn)生而阻塞，可以并發(fā)執(zhí)行，如果不是這樣，數(shù)據(jù)庫就是串行操作了；
讀寫互斥：MDL 讀鎖和 MDL 寫鎖之間相互阻塞，即同一個(gè)表上的 DML 和 DDL 之間互相阻塞；
寫寫互斥：MDL 寫鎖和 MDL 寫鎖之間互相阻塞，即兩個(gè) session 不能同時(shí)對(duì)一張表結(jié)構(gòu)做變更操作，需要串行操作；

如果在工作中，發(fā)現(xiàn)很多會(huì)話執(zhí)行的 SQL 提示”Waiting for table metadata lock”的等待，這時(shí)候就是因?yàn)?MDL 的讀鎖與寫鎖發(fā)生沖突了，如果要應(yīng)急解決問題，這時(shí)候就要考慮 kill 掉持有 MDL 鎖的事務(wù)了，因?yàn)?nbsp;MDL 鎖是在事務(wù)提交后才會(huì)釋放，這意味著事務(wù)執(zhí)行期間，MDL 鎖是一直持有的。

如何查看事務(wù)是否持有 MDL 鎖？

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

可以看到，事務(wù) A 此時(shí)持有一個(gè)表級(jí)別的 MDL 鎖，鎖的類型是 SHARED_READ，也就是 MDL 讀鎖。

對(duì)于，增刪改操作，申請(qǐng)的 MDL 鎖的類型是 SHARED_WRITE，它也屬于 MDL 讀鎖，因?yàn)?SHARED_WRITE 與 SHARED_READ 這兩個(gè)鎖的類型是相互兼容的。

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

一條 select ... for update 會(huì)加什么鎖？

select ... for update 語句屬于鎖定讀語句，它會(huì)對(duì)表的記錄加 X 型的行級(jí)鎖。

不同隔離級(jí)別下，行級(jí)鎖的種類是不同的。

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

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

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

行級(jí)鎖加鎖規(guī)則比較復(fù)雜，不同的場(chǎng)景，加鎖的形式是不同的。

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

但是，next-key lock 在一些場(chǎng)景下會(huì)退化成記錄鎖或間隙鎖。

那到底是什么場(chǎng)景呢？總結(jié)一句，在能使用記錄鎖或者間隙鎖就能避免幻讀現(xiàn)象的場(chǎng)景下， next-key lock 就會(huì)退化成記錄鎖或間隙鎖。

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

現(xiàn)在假設(shè)事務(wù) A 執(zhí)行了下面這條語句，查詢條件中 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)

這時(shí)候有其他事務(wù)對(duì)這張表進(jìn)行增刪改，都會(huì)發(fā)生阻塞。

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

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

執(zhí)行結(jié)果如下：

可以看到，事務(wù) A 此時(shí)持有一個(gè)表級(jí)別的 MDL 鎖，鎖的類型是 SHARED_WRITE，屬于 MDL 讀鎖。

而在前面我提到過，當(dāng)事務(wù)對(duì)表進(jìn)行增刪查改操作的時(shí)候，事務(wù)會(huì)申請(qǐng) MDL 讀鎖，而 MDL 讀鎖之間是相互兼容的。

所以，當(dāng)事務(wù) A 執(zhí)行了查詢條件沒有索引字段的 select ... for update 語句后，不可能是因?yàn)槭聞?wù) A 持 MDL 讀鎖，才導(dǎo)致其他事務(wù)無法進(jìn)行增刪改操作。

再來看看，事務(wù) A 持有哪些行級(jí)鎖？

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

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

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

1 個(gè)表級(jí)鎖：X 類型的意向鎖（表級(jí)別的鎖）；
4 個(gè)行級(jí)鎖：X 類型的行級(jí)鎖；

什么是意向鎖？

在 InnoDB 存引擎中，當(dāng)事務(wù)執(zhí)行鎖定讀、插入、更新、刪除操作后，需要先對(duì)表加上「意向鎖」，然后再對(duì)記錄加「行級(jí)鎖」。

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

意向鎖不會(huì)和行級(jí)鎖發(fā)生沖突，而且意向鎖之間也不會(huì)發(fā)生沖突，意向鎖只會(huì)和共享表鎖（lock tables ... read）和獨(dú)占表鎖（lock tables ... write）發(fā)生沖突。

所以，當(dāng)事務(wù) A 執(zhí)行了查詢條件沒有索引字段的 select ... for update 語句后，不可能是因?yàn)槭聞?wù) A 持有了意向鎖，才導(dǎo)致其他事務(wù)無法進(jìn)行增刪改操作。

具體是哪 4 個(gè)行級(jí)鎖？

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

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

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

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

因此，此時(shí)事務(wù) A 在主鍵索引（INDEX_NAME : PRIMARY）上加了 4 個(gè) next-key 鎖，如下：

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

這相當(dāng)于把整個(gè)表給鎖住了，其他事務(wù)在對(duì)該表進(jìn)行增、刪、改操作的時(shí)候都會(huì)被阻塞。只有在事務(wù) A 提交了事務(wù)，事務(wù) A 執(zhí)行過程中產(chǎn)生的鎖才會(huì)被釋放。

為什么因?yàn)槭聞?wù) A 對(duì)表所有記錄加了 X 型的 next-key 鎖后，其他事務(wù)就無法進(jìn)行增、刪、改操作了呢？

其他事務(wù)在執(zhí)行「刪除或者更新操作」的時(shí)候，也會(huì)申請(qǐng) X 型的 next-key 鎖，next-key 鎖是包含記錄鎖和間隙鎖的，間隙鎖之間雖然是相互兼容的，但是記錄鎖之間存在 X 型和 S 型的關(guān)系，即讀讀共享、讀寫互斥、寫寫互斥的關(guān)系。

所以當(dāng)事務(wù) A 持有了 X 型的 next-key 鎖后，其他事務(wù)就無法申請(qǐng) X 型的 next-key 鎖，從而發(fā)生阻塞。

比如，前面的例子，事務(wù) B 在更新 id = 1 的記錄的時(shí)候，它會(huì)申請(qǐng) X 型的記錄鎖（唯一索引等值操作， next-key 鎖會(huì)退化為記錄鎖），但是因?yàn)槭聞?wù) A 持有了 X 型的 next-key 鎖，所以事務(wù) B 在申請(qǐng) X 型的記錄鎖的時(shí)候，會(huì)發(fā)生阻塞。

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

事務(wù) C 的刪除操作被阻塞的原因，也是這個(gè)原因。

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

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

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

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

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

這是因?yàn)槭聞?wù) A 的這條鎖定讀查詢語句，沒有使用索引列作為查詢條件，所以掃描的方式是全表掃描，行級(jí)鎖是在遍歷索引的時(shí)候加上的，并不是針對(duì)輸出的結(jié)果加行級(jí)鎖。

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

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

如果數(shù)據(jù)量很大，還是一樣的原因嗎？

前面我們結(jié)論得出，如果如果鎖定讀查詢語句，沒有使用索引列作為查詢條件，導(dǎo)致掃描是全表掃描。那么，每一條記錄的索引上都會(huì)加 X 型的 next-key 鎖（行級(jí)鎖）。正是因?yàn)檫@個(gè)原因，才導(dǎo)致其他事務(wù)，無法對(duì)該表進(jìn)行增刪改操作。

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

群里有小伙伴提出了這個(gè)說法，說如果 MySQL 認(rèn)為數(shù)據(jù)量太大時(shí)，自動(dòng)將行所升級(jí)到表鎖。

不著急說結(jié)論，我們直接做個(gè)實(shí)驗(yàn)。

我在 t_user 表插入了 300 多萬條數(shù)據(jù)。

現(xiàn)在有個(gè)事務(wù)執(zhí)行了這條查詢語句，查詢條件 age 字段不是索引字段。

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

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

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

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

所以，MySQL 認(rèn)為數(shù)據(jù)量太大時(shí)，自動(dòng)將行所升級(jí)到表鎖，這句話并不準(zhǔn)確。

總結(jié)

在執(zhí)行 select … for update 語句的時(shí)候，會(huì)有產(chǎn)生 2 個(gè)表級(jí)別的鎖：

一個(gè)是 Server 層表級(jí)別的鎖：MDL 鎖。事務(wù)在進(jìn)行增刪查改的時(shí)候，server 層申請(qǐng) MDL 鎖都是 MDL 讀鎖，而 MDL 讀鎖之間是相互兼容的，MDL 讀鎖只會(huì)和 MDL 寫鎖發(fā)生沖突，在對(duì)表結(jié)構(gòu)進(jìn)行變更操作的時(shí)候，才會(huì)申請(qǐng) MDL 寫鎖。

一個(gè)是 Inoodb 層表級(jí)別的鎖：意向鎖。事務(wù)在進(jìn)行增刪改和鎖定讀的時(shí)候，inoodb 層會(huì)申請(qǐng)意向鎖，意向鎖不會(huì)和行級(jí)鎖發(fā)生沖突，而且意向鎖之間也不會(huì)發(fā)生沖突，意向鎖只會(huì)和共享表鎖（lock tables ... read）和獨(dú)占表鎖（lock tables ... write）發(fā)生沖突。

而是因?yàn)槿绻i定讀查詢語句，沒有使用索引列作為查詢條件，導(dǎo)致掃描是全表掃描。那么，每一條記錄的索引上都會(huì)加 next-key 鎖（行級(jí)鎖），這樣就相當(dāng)于鎖住的全表，這時(shí)如果其他事務(wù)對(duì)該表進(jìn)行增、刪、改操作的時(shí)候，都會(huì)被阻塞。

責(zé)任編輯：武曉燕來源：小林coding

索引 MySQL 鎖