7.3 鎖

7.3.1 鎖機制

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

• 如果表沒有加鎖，那么就加一個寫鎖。
• 否則的話，將請求放到寫鎖隊列中。

MySQL中用于 READ（讀） 的表鎖的實現(xiàn)機制如下：

• 如果表沒有加寫鎖，那么就加一個讀鎖。
• 否則的話，將請求放到讀鎖隊列中。

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

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 操作，就可以無需使用鎖表自由地并行執(zhí)行 INSERT 和 SELECT 語句。也就是說，可以在其它客戶端正在讀取 MyISAM 表記錄的同時時插入新記錄。如果數(shù)據(jù)文件的中間沒有空余的磁盤塊的話，就不會發(fā)生沖突了，因為這種情況下所有的新記錄都會寫在數(shù)據(jù)文件的末尾（當在表的中間做刪除或者更新操作時，就可能導致空洞）。當空洞被新數(shù)據(jù)填充后，并行插入特性就會自動重新被啟用了。
如果想要在一個表上做大量的 INSERT 和 SELECT 操作，但是并行的插入?yún)s不可能時，可以將記錄插入到臨時表中，然后定期將臨時表中的數(shù)據(jù)更新到實際的表里?？梢杂靡韵旅顚崿F(xiàn)：

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 存儲引擎來說，是可能產(chǎn)生死鎖的。這是因為 InnoDB 會自動捕獲行鎖，BDB 會在執(zhí)行 SQL 語句時捕獲頁鎖的，而不是在事務的開始就這么做。
行級鎖的優(yōu)點有：

• 在很多線程請求不同記錄時減少沖突鎖。
• 事務回滾時減少改變數(shù)據(jù)。
• 使長時間對單獨的一行記錄加鎖成為可能。

行級鎖的缺點有：

• 比頁級鎖和表級鎖消耗更多的內(nèi)存。
• 當在大量表中使用時，比頁級鎖和表級鎖更慢，因為他需要請求更多的所資源。
• 當需要頻繁對大部分數(shù)據(jù)做 GROUP BY 操作或者需要頻繁掃描整個表時，就明顯的比其它鎖更糟糕。
• 使用更高層的鎖的話，就能更方便的支持各種不同的類型應用程序，因為這種鎖的開銷比行級鎖小多了。

表級鎖在下列幾種情況下比頁級鎖和行級鎖更優(yōu)越：

• 很多操作都是讀表。
• 在嚴格條件的索引上讀取和更新，當更新或者刪除可以用單獨的索引來讀取得到時：
  

  UPDATE tbl_name SET column=value WHERE unique_key_col=key_value;    DELETE FROM tbl_name WHERE unique_key_col=key_value;

• SELECT 和 INSERT 語句并發(fā)的執(zhí)行，但是只有很少的 UPDATE 和 DELETE 語句。
• 很多的掃描表和對全表的 GROUP BY 操作，但是沒有任何寫表。

表級鎖和行級鎖或頁級鎖之間的不同之處還在于：
將同時有一個寫和多個讀的地方做版本（例如在MySQL中的并發(fā)插入）。也就是說，數(shù)據(jù)庫/表支持根據(jù)開始訪問數(shù)據(jù)時間點的不同支持各種不同的試圖。其它名有：時間行程，寫復制，或者是按需復制。
原文： 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.
按需復制在很多情況下比頁級鎖或行級鎖好多了。盡管如此，最壞情況時還是比其它正常鎖使用了更多的內(nèi)存。
可以用應用程序級鎖來代替行級鎖，例如MySQL中的 GET_LOCK() 和 RELEASE_LOCK()。但它們是勸告鎖（原文：These are advisory locks），因此只能用于安全可信的應用程序中。

7.3.2 鎖表

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

• 一個客戶端提交了一個需要長時間運行的 SELECT 操作。
• 其他客戶端對同一個表提交了 UPDATE 操作，這個客戶端就要等到 SELECT 完成了才能開始執(zhí)行。
• 其他客戶端也對同一個表提交了 SELECT 請求。由于 UPDATE 的優(yōu)先級高于 SELECT，所以 SELECT 就會先等到 UPDATE 完成了之后才開始執(zhí)行，它也在等待第一個 SELECT 操作。

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

• 讓 SELECT 速度盡量快，這可能需要創(chuàng)建一些摘要表。
• 啟動 mysqld 時使用參數(shù) --low-priority-updates。這就會讓更新操作的優(yōu)先級低于 SELECT。這種情況下，在上面的假設中，第二個 SELECT 就會在 INSERT 之前執(zhí)行了，而且也無需等待第一個SELECT 了。
• 可以執(zhí)行 SET LOW_PRIORITY_UPDATES=1 命令，指定所有的更新操作都放到一個指定的鏈接中去完成。詳情請看“14.5.3.1 SET Syntax”。
• 用 LOW_PRIORITY 屬性來降低 INSERT，UPDATE，DELETE 的優(yōu)先級。
• 用 HIGH_PRIORITY 來提高 SELECT 語句的優(yōu)先級。詳情請看“14.1.7 SELECT Syntax”。
• 從MySQL 3.23.7 開始，可以在啟動 mysqld 時指定系統(tǒng)變量 max_write_lock_count 為一個比較低的值，它能強制臨時地提高表的插入數(shù)達到一個特定值后的所有 SELECT 操作的優(yōu)先級。它允許在 WRITE 鎖達到一定數(shù)量后有 READ 鎖。
• 當 INSERT 和 SELECT 一起使用出現(xiàn)問題時，可以轉(zhuǎn)而采用 MyISAM 表，它支持并發(fā)的SELECT 和 INSERT 操作。
• 當在同一個表上同時有插入和刪除操作時，INSERT DELAYED 可能會很有用。詳情請看“14.1.4.2 INSERT DELAYED Syntax”。
• 當 SELECT 和 DELETE 一起使用出現(xiàn)問題時，DELETE 的 LIMIT 參數(shù)可能會很有用。詳情請看“14.1.1 DELETE Syntax”
• 執(zhí)行 SELECT 時使用 SQL_BUFFER_RESULT 有助于減短鎖表的持續(xù)時間.詳情請看“14.1.7 SELECT Syntax”。
• 可以修改源代碼 `mysys/thr_lock.c'，只用一個所隊列。這種情況下，寫鎖和讀鎖的優(yōu)先級就一樣了，這對一些應用可能有幫助。

以下是MySQL鎖的一些建議：

• 只要對同一個表沒有大量的更新和查詢操作混在一起，目前的用戶并不是問題。
• 執(zhí)行 LOCK TABLES 來提高速度（很多更新操作放在一個鎖之中比沒有鎖的很多更新快多了）。將數(shù)據(jù)拆分開到多個表中可能也有幫助。
• 當MySQL碰到由于鎖表引起的速度問題時，將表類型轉(zhuǎn)換成 InnoDB 或 BDB 可能有助于提高性能。詳情請看“16 The InnoDB Storage Engine”和“15.4 The BDB (BerkeleyDB) Storage Engine”。