MySQL 鎖的使用

7.3 鎖

7.3.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 操作，但是并行的插入卻不可能時，可以将記錄插入到臨時表中，然後定期将臨時表中的資料更新到實際的表裡。可以用以下指令實作：

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 操作或者需要頻繁掃描整個表時，就明顯的比其它鎖更糟糕。

使用更高層的鎖的話，就能更友善的支援各種不同的類型應用程式，因為這種鎖的開銷比行級鎖小多了。

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

很多操作都是讀表。

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

Update tbl_name SET column=value Where unique_key_col=key_value;    Delete FROM tbl_name Where unique_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”。