如何理解mysql的鎖機制

本篇文章為大家展示了如何理解mysql的鎖機制，內容簡明扼要并且容易理解，絕對能使你眼前一亮，通過這篇文章的詳細介紹希望你能有所收獲。

 鎖

0.1 鎖機制

當前MySQL已經支持 ISAM, MyISAM, MEMORY (HEAP) 類型表的表級鎖了，BDB 表支持頁級鎖，InnoDB 表支持行級鎖。
很多時候，可以通過經驗來猜測什么樣的鎖對應用程序更合適，不過通常很難說一個鎖比別的更好，這全都要依據應用程序來決定，不同的地方可能需要不同的鎖。
想要決定是否需要采用一個支持行級鎖的存儲引擎，就要看看應用程序都要做什么，其中的查詢、更新語句是怎么用的。例如，很多的web應用程序大量的做查詢，很少刪除，主要是基于索引的更新，只往特定的表中插入記錄。采用基本的MySQL MyISAM 表就很合適了。
MySQL中對表級鎖的存儲引擎來說是釋放死鎖的。避免死鎖可以這樣做到：在任何查詢之前先請求鎖，并且按照請求的順序鎖表。
MySQL中用于 WRITE（寫） 的表鎖的實現機制如下：

• 如果表沒有加鎖，那么就加一個寫鎖。

• 否則的話，將請求放到寫鎖隊列中。

MySQL中用于 READ（讀） 的表鎖的實現機制如下：

• 如果表沒有加寫鎖，那么就加一個讀鎖。

• 否則的話，將請求放到讀鎖隊列中。

當鎖釋放后，寫鎖隊列中的線程可以用這個鎖資源，然后才輪到讀鎖隊列中的線程。
這就是說，如果表里有很多更新操作的話，那么 Select 必須等到所有的更新都完成了之后才能開始。
從 MySQL 3.23.33 開始，可以通過狀態變量 Table_locks_waited 和 Table_locks_immediate 來分析系統中的鎖表爭奪情況：

mysql> SHOW STATUS LIKE 'Table%';+-----------------------+---------+| Variable_name         | Value   |+-----------------------+---------+| Table_locks_immediate | 1151552 || Table_locks_waited    | 15324   |+-----------------------+---------+

在 MySQL 3.23.7（在Windows上是3.23.25）以后，在 MyISAM 表中只要沒有沖突的 Insert 操作，就可以無需使用鎖表自由地并行執行 Insert 和 Select 語句。也就是說，可以在其它客戶端正在讀取 MyISAM 表記錄的同時時插入新記錄。如果數據文件的中間沒有空余的磁盤塊的話，就不會發生沖突了，因為這種情況下所有的新記錄都會寫在數據文件的末尾（當在表的中間做刪除或者更新操作時，就可能導致空洞）。當空洞被新數據填充后，并行插入特性就會自動重新被啟用了。
如果想要在一個表上做大量的 Insert 和 Select 操作，但是并行的插入卻不可能時，可以將記錄插入到臨時表中，然后定期將臨時表中的數據更新到實際的表里?？梢杂靡韵旅顚崿F：

mysql> LOCK TABLES real_table WRITE, insert_table WRITE;mysql> Insert INTO real_table Select * FROM insert_table;mysql> TRUNCATE TABLE insert_table;mysql> UNLOCK TABLES;

InnoDB 使用行級鎖，BDB 使用頁級鎖。對于 InnoDB 和 BDB 存儲引擎來說，是可能產生死鎖的。這是因為 InnoDB 會自動捕獲行鎖，BDB 會在執行 SQL 語句時捕獲頁鎖的，而不是在事務的開始就這么做。
行級鎖的優點有：

• 在很多線程請求不同記錄時減少沖突鎖。

• 事務回滾時減少改變數據。

• 使長時間對單獨的一行記錄加鎖成為可能。

行級鎖的缺點有：

• 比頁級鎖和表級鎖消耗更多的內存。

• 當在大量表中使用時，比頁級鎖和表級鎖更慢，因為他需要請求更多的所資源。

• 當需要頻繁對大部分數據做 GROUP BY 操作或者需要頻繁掃描整個表時，就明顯的比其它鎖更糟糕。

• 使用更高層的鎖的話，就能更方便的支持各種不同的類型應用程序，因為這種鎖的開銷比行級鎖小多了。

表級鎖在下列幾種情況下比頁級鎖和行級鎖更優越：

• 很多操作都是讀表。

• 在嚴格條件的索引上讀取和更新，當更新或者刪除可以用單獨的索引來讀取得到時：
  

  Updatetbl_nameSETcolumn=valueWhereunique_key_col=key_value;    Delete FROMtbl_nameWhereunique_key_col=key_value;

• Select 和 Insert 語句并發的執行，但是只有很少的 Update 和 Delete 語句。

• 很多的掃描表和對全表的 GROUP BY 操作，但是沒有任何寫表。

表級鎖和行級鎖或頁級鎖之間的不同之處還在于：
將同時有一個寫和多個讀的地方做版本（例如在MySQL中的并發插入）。也就是說，數據庫/表支持根據開始訪問數據時間點的不同支持各種不同的試圖。其它名有：時間行程，寫復制，或者是按需復制。
原文： Versioning (such as we use in MySQL for concurrent inserts) where you can have one writer at the same time as many readers. This means that the database/table supports different views for the data depending on when you started to access it. Other names for this are time travel, copy on write, or copy on demand.
按需復制在很多情況下比頁級鎖或行級鎖好多了。盡管如此，最壞情況時還是比其它正常鎖使用了更多的內存。
可以用應用程序級鎖來代替行級鎖，例如MySQL中的 GET_LOCK() 和 RELEASE_LOCK()。但它們是勸告鎖（原文：These are advisory locks），因此只能用于安全可信的應用程序中。

7.3.2 鎖表

為了能有快速的鎖，MySQL除了 InnoDB 和 BDB 這兩種存儲引擎外，所有的都是用表級鎖（而非頁、行、列級鎖）。
對于 InnoDB 和 BDB 表，MySQL只有在指定用 LOCK TABLES 鎖表時才使用表級鎖。在這兩種表中，建議最好不要使用 LOCK TABLES，因為 InnoDB 自動采用行級鎖，BDB 用頁級鎖來保證事務的隔離。
如果數據表很大，那么在大多數應用中表級鎖會比行級鎖好多了，不過這有一些陷阱。
表級鎖讓很多線程可以同時從數據表中讀取數據，但是如果另一個線程想要寫數據的話，就必須要先取得排他訪問。正在更新數據時，必須要等到更新完成了，其他線程才能訪問這個表。
更新操作通常認為比讀取更重要，因此它的優先級更高。不過最好要先確認，數據表是否有很高的 Select 操作，而更新操作并非很‘急需’。
表鎖鎖在一個線程在等待，因為磁盤空間滿了，但是卻需要有空余的磁盤空間，這個線程才能繼續處理時就有問題了。這種情況下，所有要訪問這個出問題的表的線程都會被置為等待狀態，直到有剩余磁盤空間了。
表鎖在以下設想情況中就不利了：

• 一個客戶端提交了一個需要長時間運行的 Select 操作。

• 其他客戶端對同一個表提交了 Update 操作，這個客戶端就要等到 Select 完成了才能開始執行。

• 其他客戶端也對同一個表提交了 Select 請求。由于 Update 的優先級高于 Select，所以 Select 就會先等到 Update 完成了之后才開始執行，它也在等待第一個 Select 操作。

下列所述可以減少表鎖帶來的資源爭奪：

• 讓 Select 速度盡量快，這可能需要創建一些摘要表。

• 啟動 mysqld 時使用參數 --low-priority-updates。這就會讓更新操作的優先級低于 Select。這種情況下，在上面的假設中，第二個 Select 就會在 Insert 之前執行了，而且也無需等待第一個Select 了。

• 可以執行 SET LOW_PRIORITY_UpdateS=1 命令，指定所有的更新操作都放到一個指定的鏈接中去完成。詳情請看“14.5.3.1 SET Syntax”。

• 用 LOW_PRIORITY 屬性來降低 Insert，Update，Delete 的優先級。

• 用 HIGH_PRIORITY 來提高 Select 語句的優先級。詳情請看“14.1.7 Select Syntax”。

• 從MySQL 3.23.7 開始，可以在啟動 mysqld 時指定系統變量 max_write_lock_count 為一個比較低的值，它能強制臨時地提高表的插入數達到一個特定值后的所有 Select 操作的優先級。它允許在 WRITE 鎖達到一定數量后有 READ 鎖。

• 當 Insert 和 Select 一起使用出現問題時，可以轉而采用 MyISAM 表，它支持并發的Select 和 Insert 操作。

• 當在同一個表上同時有插入和刪除操作時，Insert DELAYED 可能會很有用。詳情請看“14.1.4.2 Insert DELAYED Syntax”。

• 當 Select 和 Delete 一起使用出現問題時，Delete 的 LIMIT 參數可能會很有用。詳情請看“14.1.1 Delete Syntax”

• 執行 Select 時使用 SQL_BUFFER_RESULT 有助于減短鎖表的持續時間.詳情請看“14.1.7 Select Syntax”。

• 可以修改源代碼 `mysys/thr_lock.c'，只用一個所隊列。這種情況下，寫鎖和讀鎖的優先級就一樣了，這對一些應用可能有幫助。

以下是MySQL鎖的一些建議：

• 只要對同一個表沒有大量的更新和查詢操作混在一起，目前的用戶并不是問題。

• 執行 LOCK TABLES 來提高速度（很多更新操作放在一個鎖之中比沒有鎖的很多更新快多了）。將數據拆分開到多個表中可能也有幫助。

• 當MySQL碰到由于鎖表引起的速度問題時，將表類型轉換成 InnoDB 或 BDB 可能有助于提高性能。詳情請看“16 The InnoDB Storage Engine”和“15.4 The BDB (BerkeleyDB) Storage Engine”。

上述內容就是如何理解mysql的鎖機制，你們學到知識或技能了嗎？如果還想學到更多技能或者豐富自己的知識儲備，歡迎關注億速云行業資訊頻道。