MySQL 什麼時候鎖表？如何防止鎖表？

Mysql 調優的重點目標： 避免鎖表

鎖表會帶來一系列問題，影響數據庫的性能和系統的穩定性。

主要是下面的四個問題： 性能問題、死鎖問題、可用性問題、一致性問題

1. 鎖錶帶來的性能問題

鎖表會阻止其他事務對該表的併發訪問，包括讀操作和寫操作。

鎖表會導致嚴重的性能問題：

2. 鎖表引發死鎖

在高併發環境下，鎖表更容易導致死鎖的發生。

鎖表會導致嚴重的事務問題：

3. 鎖表降低系統可用性

長時間的表鎖定會影響數據庫的可用性，使得應用程序無法及時處理用戶請求。

鎖錶帶來的系統可用性問題：

4. 鎖表引發數據一致性問題

在高併發寫操作場景下，鎖表會導致數據一致性問題。

鎖錶帶來的數據一致性問題：

那麼，什麼情況下導致鎖表？ 如何解決鎖表的問題呢？

回顧一下 mysql 鎖的分類

從操作的粒度可分爲表級鎖、行級鎖和頁級鎖。

1. 表級鎖：

每次操作鎖住整張表。鎖定粒度大，發生鎖衝突的概率最高，併發度最低。

注意： 表級鎖應用在 MyISAM、InnoDB、BDB 等存儲引擎中。

表鎖的特點：

2. 行級鎖：

每次操作鎖住一行數據。鎖定粒度最小，發生鎖衝突的概率最低，併發度最高。

注意：行級鎖應用在 InnoDB 存儲引擎中。MyISAM 沒有行級鎖

行鎖的特點：

3. 頁級鎖：

每次鎖定相鄰的一組記錄，鎖定粒度界於表鎖和行鎖之間，加鎖開銷和加鎖時間界於表鎖和行鎖之間，併發度一般。

注意：頁級鎖 應用在 BDB 等存儲引擎中。

頁鎖的特點：

MySQL 使用頁級鎖的情況相對較少，因爲 MySQL 默認使用的是行級鎖。

但在特定的情況下，MySQL 可能會使用頁級鎖，主要包括以下幾種情況：

  1. 表級鎖定轉換爲頁級鎖定： 當表級鎖無法滿足需求時，MySQL 可能會將表級鎖轉換爲頁級鎖。這通常發生在使用 BDB 存儲引擎時，因爲 BDB 存儲引擎默認使用表級鎖。在某些情況下，MySQL 可能會將表級鎖轉換爲頁級鎖，以提高併發性能。

  2. 隱式鎖定大量行數據： 在某些情況下，如果一次性鎖定了大量行數據，則 MySQL 可能會使用頁級鎖來減少鎖的數量，從而減輕鎖管理的負擔。例如，在使用 BDB 存儲引擎時，如果一次性鎖定了大量行數據，則 MySQL 可能會將表級鎖轉換爲頁級鎖。

  3. DDL 操作： 在執行對錶結構進行修改的 DDL（數據定義語言）操作時，MySQL 可能會使用頁級鎖來鎖定整個表。例如，當執行 ALTER TABLE 操作時，MySQL 可能會鎖定整個表的頁，以防止其他對錶結構的修改和查詢操作。

總的來說，MySQL 使用頁級鎖的情況相對較少，因爲頁級鎖通常會導致鎖的粒度過大，影響併發性能。在設計數據庫和應用程序時，通常會盡量避免使用頁級鎖，而是優先使用行級鎖或其他更細粒度的鎖。

InnoDB 存儲引擎中的 表鎖和行鎖

對於 MySQL 來說，每種存儲引擎都可以實現自己的鎖策略和鎖粒度，

比如 InnoDB 引擎支持行級鎖和表級鎖，不支持 頁級鎖

下面主要聚焦介紹 InnoDB 存儲引擎中的兩大鎖：

InnoDB 的表級鎖

表鎖，顧名思義就是對某個表加鎖。

表級鎖可以分爲：表鎖、元數據鎖、意向鎖三種。

表級鎖之一：表鎖

那什麼時候會使用表鎖呢？

一般情況是對應的存儲引擎沒有行級鎖（例如：MyIASM），或者是對應的 SQL 語句沒有匹配到索引。

對於第一種情況而言，因爲對應存儲引擎不支持行鎖，所以只能是使用更粗粒度的鎖來實現，這也比較好理解。

對於第二種情況而言，如果存儲引擎支持行鎖，但對應的 SQL 就沒有使用索引，那麼此時也是會全表掃描，那此時也是會使用表鎖。

例如下面的語句沒有指定查詢列，或者指定了查詢列但是並沒有用到索引，那麼也是會直接鎖定整個表。

情況 1：沒有指定查詢列

select * from user;

情況 2：指定查詢列，但是沒有用到索引

select * from user where name = 'zhangsan';

上面說的索引，其實是判斷是否會用行級鎖的關鍵。

表級鎖之二：元數據鎖

元數據，指的是我們的表結構這些元數據。

元數據鎖（Metadata Lock）自然是執行 DDL 表結構變更語句時，我們對錶加上的一個鎖了。

那什麼時候會使用元數據鎖這個表級鎖呢？

當我們對一個表做增刪改查操作的時候，會加上 MDL 讀鎖；當我們要對錶結構做變更時，就會加 MDL 寫鎖。

在 MySQL 中，當對錶進行寫操作（如 INSERT、UPDATE、DELETE）時，需要對相關的數據行加鎖以確保數據的一致性和完整性。在某些情況下，MySQL 需要鎖定整個表而不是部分行，這種情況下會鎖定整個表，導致其他會話不能訪問表。

  1. 使用 ALTER TABLE、TRUNCATE TABLE 等語句對錶進行結構性修改時，MySQL 需要鎖定整個表以防止其他會話對錶進行操作。

  2. 使用 LOCK TABLES 語句手動鎖定表時，MySQL 將鎖定整個表以確保其他會話不能訪問它。

  3. 在使用 MyISAM 存儲引擎時，當執行寫操作時，MySQL 會對整個表進行加鎖。這是因爲 MyISAM 使用表級鎖定而不是行級鎖定。

項目中最常見的鎖表問題，都是由於 UPDATE 語句或者 DELETE 語句的 where 條件沒有走索引導致的。因此我們需要在條件字段上加索引，從而將表鎖變爲行鎖。

表級鎖之三：意向鎖

意向鎖，本質上就是空間換時間的產物，是爲了提高行鎖效率的一個東西。

在 InnoDB 中，我們對某條記錄進行鎖定時，爲了提高併發度，通常都只是鎖定這一行記錄，而不是鎖定整個表。

而當我們需要爲整個表加 X 鎖的時候，我們就需要遍歷整個表的記錄，如果每條記錄都沒有被加鎖，纔可以給整個表加 X 鎖。

而這個遍歷過程就很費時間，這時候就有了意向鎖。

意向鎖，其實就是標記這個表有沒有被鎖，如果有某條記錄被鎖住了，那麼就必須獲取該表的意向鎖。

所以當我們需要判斷這個表的記錄有沒有被加鎖時，直接判斷意向鎖就可以了，減少了遍歷的時間，提高了效率，是典型的用空間換時間的做法。

那麼什麼時候會用到意向鎖呢？

很簡單，就是在對錶中的行記錄加鎖的時候，就會用到意向鎖。

InnoDB 存儲引擎支持 多粒度（granular）鎖定，就是說允許事務在行級上的鎖和表級上的鎖同時存在。

那麼爲了實現行鎖和表鎖並存，InnoDB 存儲引擎就設計出了 意向鎖（Intention Lock） 這個東西：

Intention locks are table-level locks that indicate which type of lock (shared or exclusive) a transaction requires later for a row in a table.

很好理解：意向鎖是一個表級鎖，其作用就是指明接下來的事務將會用到哪種鎖。

有兩種意向鎖：

各位其實可以直接把 ”意向 “翻譯成” 想要“，想要共享鎖、想要排他鎖，你就會發現原來就這東西啊（滑稽）。

意向鎖之間是相互兼容的：

6gHXJ5

但是與表級讀寫鎖之間大部分都是不兼容的：

JttsYh

注意，這裏強調一點：上表中的讀寫鎖指的是表級鎖，意向鎖不會與行級的讀寫鎖互斥！！！

來理解一下爲什麼說意向鎖不會與行級的讀寫鎖互斥。舉個例子，事務 T1、事務 T2、事務 T3 分別想對某張表中的記錄行 r1、r2、r3 進行修改，很普通的併發場景對吧，這三個事務之間並不會發生干擾，所以是可以正常執行的。

這三個事務都會先對這張表加意向寫鎖，因爲意向鎖之間是兼容的嘛，所以這一步沒有任何問題。

那如果意向鎖和行級讀寫鎖互斥的話，豈不是這三個事務都沒法再執行下去了，對吧。

OK，看到這裏，我們來思考兩個問題：

1）爲什麼沒有意向鎖的話，表鎖和行鎖不能共存？

2）意向鎖是如何讓表鎖和行鎖共存的？

首先來看第一個問題，假設行鎖和表鎖能共存，舉個例子：事務 T1 鎖住表中的某一行（行級寫鎖），事務 T2 鎖住整個表（表級寫鎖）。

問題很明顯，既然事務 T1 鎖住了某一行，那麼其他事務就不可能修改這一行。這與 ” 事務 T2 鎖住整個表就能修改表中的任意一行 “ 形成了衝突。所以，沒有意向鎖的時候，行鎖與表鎖是無法共存的。

再來看第二個問題，有了意向鎖之後，事務 T1 在申請行級寫鎖之前，MySQL 會先自動給事務 T1 申請這張表的意向排他鎖，當表上有意向排他鎖時其他事務申請表級寫鎖會被阻塞，也即事務 T2 申請這張表的寫鎖就會失敗。

InnoDB 的行級鎖

行級鎖是存儲引擎級別的鎖，需要存儲引擎支持纔有效。

目前 MyISAM 存儲引擎不支持行級鎖，而 Innodb 存儲引擎則支持行級鎖。

而表級鎖，則是 MySQL 層面就支持的鎖。

那麼什麼時候會使用行級鎖呢？

當增刪改查匹配到索引時，Innodb 會使用行級鎖。

如果沒有匹配不到索引，那麼就會直接使用表級鎖。

InnoDB 存儲引擎三種行級鎖

InnoDB 引擎行鎖是通過對索引數據頁上的記錄加鎖實現的，主要實現算法有 3 種：Record Lock、Gap Lock 和 Next-key Lock, 也就是 InnoDB 的三種行鎖模式。

行級鎖之一：記錄鎖 (Record Locks)

（1）記錄鎖, 僅僅鎖住索引記錄的一行，在單條索引記錄上加鎖。 （2）record lock 鎖住的永遠是索引，而非記錄本身，即使該表上沒有任何索引，那麼 innodb 會在後臺創建一個隱藏的聚集主鍵索引，那麼鎖住的就是這個隱藏的聚集主鍵索引。

所以說當一條 sql 沒有走任何索引時，那麼將會在每一條聚合索引後面加 X 鎖，這個類似於表鎖，但原理上和表鎖應該是完全不同的。

行級鎖之二：間隙鎖 (Gap Locks)

（1）區間鎖, 僅僅鎖住一個索引區間（開區間，不包括雙端端點）。 （2）在索引記錄之間的間隙中加鎖，或者是在某一條索引記錄之前或者之後加鎖，並不包括該索引記錄本身。

（3）間隙鎖可用於防止幻讀，保證索引間的不會被插入數據

比如在 100、10000 中，間隙鎖的可能值有 (∞, 100)，(100, 10000)，(10000, ∞)，

行級鎖之三：臨鍵鎖 (Next-Key Locks)

（1）record lock + gap lock, 左開右閉區間。

（2）默認情況下，innodb 使用 next-key locks 來鎖定記錄。select … for update （3）但當查詢的索引含有唯一屬性的時候，Next-Key Lock 會進行優化，將其降級爲 Record Lock，即僅鎖住索引本身，不是範圍。 （4）Next-Key Lock 在不同的場景中會退化:

比如在 100、10000 中，臨鍵鎖 (Next-Key Locks) 的可能有 (∞, 100]，(100, 10000] , 40 歲老架構師尼恩提示，這裏的關鍵是左開右閉

在 MySQL 中，當對錶進行寫操作（如 INSERT、UPDATE、DELETE）時，需要對相關的數據行加鎖以確保數據的一致性和完整性。在某些情況下，MySQL 需要鎖定整個表而不是部分行，這種情況下會鎖定整個表，導致其他會話不能訪問表。

  1. 使用 ALTER TABLE、TRUNCATE TABLE 等語句對錶進行結構性修改時，MySQL 需要鎖定整個表以防止其他會話對錶進行操作。

  2. 使用 LOCK TABLES 語句手動鎖定表時，MySQL 將鎖定整個表以確保其他會話不能訪問它。

  3. 在使用 MyISAM 存儲引擎時，當執行寫操作時，MySQL 會對整個表進行加鎖。這是因爲 MyISAM 使用表級鎖定而不是行級鎖定。

InnoDB 如何加鎖？

InnoDB 採用的是兩階段鎖定協議（two-phase locking protocol），意思是：對於每一個事務 Transaction，分爲兩個階段

在這個階段，事務可以獲得鎖定，但不能釋放鎖定。事務可以繼續請求並獲得鎖定，直到它達到了其鎖定點（Lock Point），也就是事務獲得最後一個鎖定的時間點。一旦事務進入第二階段，它就不能再獲得新的鎖定。

在這個階段，事務可以釋放已經持有的鎖定，但不能再獲取新的鎖定。這個階段的目的是確保事務不會在已經進入第二階段後再次請求鎖定

兩階段鎖定協議（two-phase locking protocol），有下面的兩個特點：

InnoDB 如何加鎖？從下面三個維度來分開介紹：

第 1 個維度：InnoDB 如何加意向鎖?

它比較特殊，是由 InnoDB 存儲引擎自己維護的，用戶無法手動操作意向鎖，

在爲數據行加讀寫鎖之前，InnoDB 會先獲取該數據行所在在數據表的對應意向鎖。

第 2 個維度：InnoDB 如何加表級鎖?：

1）隱式鎖定：對於常見的 DDL 語句（如 ALTER、CREATE等），InnoDB 會自動給相應的表加表級鎖

2）顯示鎖定：在執行 SQL 語句時，也可以明確顯示指定對某個表進行加鎖（lock table user read(write)）

lock table user read; # 加表級讀鎖
unlock tables; # 釋放表級鎖

第 3 個維度：InnoDB 如何加行級鎖?：

1）對於常見的 DML 語句（如 UPDATE、DELETE 和 INSERT），InnoDB 會自動給相應的記錄行加寫鎖

2）默認情況下對於普通 SELECT 語句，InnoDB 不會加任何鎖，但是在 Serializable 隔離級別下會加行級讀鎖

上面兩種是隱式鎖定，InnoDB 也支持通過特定的語句進行顯式鎖定，不過這些語句並不屬於 SQL 規範：

3）SELECT * FROM table_name WHERE ... FOR UPDATE，加行級寫鎖

4）SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE，加行級讀鎖

另外，需要注意的是，InnoDB 存儲引擎的行級鎖是基於索引的，也就是說當索引失效或者說根本沒有用索引的時候，行鎖就會升級成表鎖。

舉個例子（這裏就以比較典型的索引失效情況 “使用 or" 來舉例），有數據庫如下，id 是主鍵索引：

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
12345

新建兩個事務，先執行事務 T1 的前兩行，也就是不要執行 rollback 也不要 commit：

這個時候事務 T1 沒有釋放鎖，並且由於索引失效事務 T1 其實是鎖住了整張表，

此時再來執行事務 2，你會發現事務 T2 會卡住，最後超時關閉事務：

回到面試題：什麼情況下，MySQL 會鎖定整個表？

回到字節的面試題的核心：MySQL 什麼時候 鎖表？

MySQL 會在以下情況下鎖定整個表:

1. 對錶進行結構性修改:

當使用 ALTER TABLE 或 TRUNCATE TABLE 等語句對錶進行結構性修改時，MySQL 需要鎖定整個表以防止其他會話對錶進行操作。

2. 手動鎖定表:

使用 LOCK TABLES 語句手動鎖定表時，MySQL 將鎖定整個表以確保其他會話不能訪問它。

3.MyISAM 寫操作:

在使用 MyISAM 存儲引擎時，執行寫操作會導致 MySQL 對整個表加鎖，

這是因爲 MyISAM 使用表級鎖定而不是行級鎖定。

4. 兩個或多個事務在同時修改一個表時:

事務中包含多條對同一個表進行修改的 SQL 語句時，該表會被鎖定。

這是因爲 MVSQL 採用行鎖定機制，但當兩個或多個事務在同時修改一個表時，未使用的修改請求會被放入等待隊列。

5. 索引操作:

對一個大表進行索引操作 (如新建或刪除索引) 時，該表會被鎖定。

索引操作鎖定表的時間與表的大小和結構有關。

6. 併發操作:

在一張表中插入大量數據的同時，嘗試在同一時刻進行查詢操作，會導致表被鎖定。

這是因爲查詢和插入操作都需要獲取相應的鎖

7 索引不可用時加鎖操作:

使用 SELECT..FOR UPDATE 語句進行行級鎖定操作時，如果索引不可用，MySQL 可能會升級爲表鎖。

8. 索引選擇不恰當:

在某些情況下，如果查詢的索引選擇不恰當，MVSQL 可能會升級爲表鎖，尤其是當鎖住的數據量較大時

9. 更新和刪除場景，where 沒命中索引（最常見場景）:

項目中最常見的鎖表問題，都是由於 UPDATE/DELETE 時, where 條件沒有走索引導致的。

當執行 UPDATE 或 DELETE 語句且 where 條件未使用索引時，可能會導致全表掃描並鎖定整個表。

因此我們需要在條件字段上加索引，從而將表鎖變爲行鎖。

10. 查詢場景，索引失效的情況下，行鎖升表鎖

在索引失效的情況下，MySQL 會把所有聚集索引記錄和間隙都鎖上，稱之爲鎖表，或叫行鎖升表鎖.

在 MySQL 中，索引對於查詢性能至關重要，但是有些情況下索引可能會失效，從而導致查詢性能下降

以下是一些常見的索引失效原因及其解決方法：

10.1. 使用函數或操作符

10.2. 模糊查詢

10.3. 不符合最左前綴原則

10.4. 數據類型不一致

10.5. 使用 OR 條件

10.6. 隱式類型轉換

10.7. 範圍條件

10.8. NULL 判斷

10.9. 更新頻繁的列

10.10. 查詢優化器選擇

通過識別和解決這些索引失效的原因，可以顯著提高 MySQL 查詢的性能。

MySQL 會鎖表的場景總結

總之，MVSQL 在執行結構性修改、手動鎖定、寫操作、事務處理、索引操作、併發操作、加鎖操作、以及在特定查詢條件下索引失效，都可能需要對錶進行鎖定。

瞭解這些情況，有助於更好地進行 MySQL 性能優化

回到面試題第二問：如何減少或避免鎖表？

鎖表會對系統性能、數據一致性和用戶體驗產生負面影響，甚至可能引發嚴重的業務中斷和系統崩潰。

因此，避免鎖表是數據庫優化和系統設計中的重要任務。

可以通過優化索引、分解大事務、合理設計表結構、使用適當的事務隔離級別、讀寫分離等方法來減少鎖表問題，提升系統的併發性能和穩定性。

在 MySQL 中避免鎖表問題對於提高數據庫性能和併發性至關重要。

以下是一些調優策略和最佳實踐，以減少或避免鎖表問題：

1. 使用合適的存儲引擎

2. 優化查詢和索引

3. 分解大事務

4. 鎖策略和隔離級別

5. 分區和分表

6. 避免長時間的鎖定操作

7. 監控和分析

8. 讀寫分離

9. 使用合適的鎖

10. 業務層優化

通過以上策略和最佳實踐，可以有效減少或避免 MySQL 中的鎖表問題，提高數據庫的併發性和性能。

本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/972ifwCLVycmcosQJ3oRjA

猜你喜歡