长生界辰东小说,我欲封天

新聞中心

這里有您想知道的互聯(lián)網(wǎng)營銷解決方案

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

大家好，我是小林。

在徐匯等地區(qū)，都構(gòu)建了全面的區(qū)域性戰(zhàn)略布局，加強發(fā)展的系統(tǒng)性、市場前瞻性、產(chǎn)品創(chuàng)新能力，以專注、極致的服務(wù)理念，為客戶提供做網(wǎng)站、成都做網(wǎng)站網(wǎng)站設(shè)計制作按需策劃,公司網(wǎng)站建設(shè),企業(yè)網(wǎng)站建設(shè),高端網(wǎng)站設(shè)計,成都營銷網(wǎng)站建設(shè),外貿(mào)網(wǎng)站建設(shè),徐匯網(wǎng)站建設(shè)費用合理。

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

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

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

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

做好準備

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

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

一條 select 語句會加什么鎖？

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

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

要驗證這個結(jié)論也很簡單，在 MySQL 8.0 以上的版本，可以執(zhí)行 select * from performance_schema.data_locks\G; 這條語句，查看 Innodb 存儲引擎為事務(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 存儲引擎不會為事務(wù)加任何鎖。

那難道什么鎖都不加嗎？

當然不是的。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

現(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)

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

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

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

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

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

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

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

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

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

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

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

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

什么是意向鎖？

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

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

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

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

具體是哪 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 鎖的范圍，具體怎么確定呢？

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

因此，此時事務(wù) 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, +∞]

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

現(xiàn)在有個事務(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í)行了好長時間，至少有幾十分鐘）。

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

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

總結(jié)

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

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

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

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

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

分享文章：如果查詢條件沒有索引字段的話，是加「行鎖」還是加「表鎖」？
當前URL：http://fisionsoft.com.cn/article/dpgpdgs.html

新聞中心

做好準備

一條 select 語句會加什么鎖？

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

什么是意向鎖？

具體是哪 4 個行級鎖？

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

總結(jié)

其他資訊

一條 select 語句會加什么鎖？

什么是意向鎖？

具體是哪 4 個行級鎖？

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