mysql5.7官網直譯鎖操作優化--内部鎖的方法介紹

8.11 Optimizing Locking Operations 鎖操作的優化

8.11.1 Internal Locking Methods 内部鎖定的方法

8.11.2 Table Locking Issues 表鎖的問題

8.11.3 Concurrent Inserts 并發插入

8.11.4 Metadata Locking 中繼資料鎖

8.11.5 External Locking 外部鎖

mysql對于表中内容的競争通過使用鎖來管理:

>内部鎖在mysql伺服器内部執行用來管理多線程對表内容的競争。這類型的鎖之是以被稱為内部是因為它完全在伺服器内部，不涉及任何其他程式。請看8.11.1的内部鎖方法。

>外部鎖會發生，當伺服器和其他程式鎖MyISAM表檔案進而合作達到程式可以通路表。具體請看8.11.5的外部鎖。

------------------------------------------

8.11.1 Internal Locking Methods 内部鎖方法

這部分讨論内部鎖；也就是說，mysql伺服器自己通過鎖來管理多個session對表内容的競争管理。

這類型的鎖完全有伺服器自己來完成，不需要任何外部程式是以叫内部鎖。關于其他程式在mysql檔案中執行的鎖，請看8.11.5的外部鎖。

>Row-Level Locking 行級鎖

>Table-Level Locking 表級鎖

>Choosing the Type of Locking 選擇表類型

行級鎖

mysql使用行級鎖在InnoDB表上支援多個session對表的同時寫，使得它們适用于多使用者，高并發和OLTP應用。

為了當多個寫操作并發完成在一個單一InnoDB表上且不産生死鎖，要求鎖必須在事物開始之前加上如查詢SELECT .... FOR UPDATE 語句對每一個行組可能被修改的，即使修改資料語句在事物之後。如果事物修改或者鎖不隻一張表，每一個事物中都以相同的順序來釋出合适的語句。死鎖會影響性能，但不會有嚴重錯誤，因為InnoDB自動發現死鎖條件并且會復原收影響的事物。

在高并發系統，死鎖檢測能夠引起性能下降當多個線程等待同一個鎖。這時，也許更有效的是關閉死鎖檢測并依靠innodb_lock_wait_timeout設定來處理當死鎖發生時事物的復原。死鎖的檢測可以通過使用innodb_deadlock_detect配置選項來關閉。

行級鎖的優勢

1)減少鎖沖突，當不同的session通路不同的行。

2)對于復原更少的改變。

3)可能鎖住單行很長時間

表級鎖

mysql對MyISAM,MEMORY,和MERGE表中使用表級鎖，在同一個時間隻允許一個session來更新表。這種級别鎖會使的存儲引擎更适用于隻讀，幾乎全是讀，或者是單使用者的應用程式。

這類型的存儲引擎通過對所有請求排列并按同樣順序鎖表。相對的是減少了并發量；其他session要想改變表必須等待直到目前資料修改完成後才能操作。

表級别鎖的優勢：

1）需要更少的相關記憶體(行鎖要求對每一行記憶體或者是分組行鎖)

2）更快速當使用一個鎖來涉及到一個大部分表。

3）更快速你需要GROUP BY操作在表中大部分資料或者必須掃描掃描完整的表順序。

MySQL授權表寫鎖如下：

1，如果沒有鎖在表中，給表中加一個寫鎖。

2，否則，放鎖要求到寫鎖隊列。

MySQl授權表讀鎖如下：

1）如果沒有寫鎖在表中，則可以給表加行鎖。

2）否則，将鎖請求放入讀鎖隊列。

表更新比表檢索的優先級更好。是以，當一個鎖被釋放，鎖首先會到寫鎖隊列并且之後請求這個讀鎖隊列。這樣能保證更新表操作不會有太久的延遲即使是有很多查詢在的情況下。然而，如果有許多更新在表中，查詢語句需要等待直到不再有更新存在。

對于修改讀寫操作的優先級資訊，請看8.11.2的表鎖的問題。

你能夠分析表鎖競争在你的系統通過檢查table_locks_immediate和table_locks_waited狀态變量值，這說明了請求授予擷取表鎖的數量和等待的數量：

mysql> SHOW STATUS LIKE 'Table%';

+-----------------------+---------+

| Variable_name         | Value   |

+-----------------------+---------+

| Table_locks_immediate | 1151552 |

| Table_locks_waited    | 15324   |

+-----------------------+---------+

鎖執行方案表也提供了鎖資訊，請看25.11.12的鎖方案表的執行。

MyISAM存儲引擎支援并發插入來減少讀和寫的競争對于給出的表：如果MyISAM表沒有自由的資料塊在表檔案中間，行總是插入到資料檔案的最後。在這種情況下，你能自由的混合并發插入和查詢語句對MyISAM表而不需要上鎖。也就是你能夠插入行到MyISAM表的同時，其他用戶端可以讀取它。漏洞可能會因為删除或者更新表中間的資料而産生。如果有漏洞，并發插入不可用但是能夠自動可用當所有的漏洞都填充了新資料以後。為了控制這種行為，使用concurrent_insert系統變量，請看8.11.3的并發插入、

如果你明确需要一個表鎖通過LOCK TABLES,你能夠請求一個READ LOCAL 鎖而不是一個READ鎖，這樣其他session能夠在你鎖表的情況下完成并發插入。

為了執行多個插入和查詢操作在一個表t1當并發插入不可用時，你能夠插入行到臨時表temp_t1 并且o從臨時表中取資料更新真實表:

mysql> LOCK TABLES t1 WRITE, temp_t1 WRITE;

mysql> INSERT INTO t1 SELECT * FROM temp_t1;

mysql> DELETE FROM temp_t1;

mysql> UNLOCK TABLES;

選擇鎖的類型

通常，在如下情況下表鎖優先于行鎖:

>大多數語句都是對表的讀取、

>對表的操作混合着讀和寫，其中更新和删除操作的單行可以通過一個關鍵字查詢：

UPDATE tbl_name SET column=value WHERE unique_key_col=key_value;

DELETE FROM tbl_name WHERE unique_key_col=key_value;

>查詢和并發插入一起執行，非常少的更新或者删除操作。

>許多掃描或者分組操作在完整的表中沒有任何寫。

使用進階鎖，你能夠更容易調整應用通過支援的不同鎖類型，因為重量級鎖要少于行鎖。

除行級鎖以外的選項：

>版本協調(例如使用mysql并發插入)可以邊寫邊讀。這意味着資料庫或者表支援不同的試圖對資料依賴于何時通路開始。其他常用的條件是"空間穿越","寫時複制"，"按需複制"。

>按需複制優先級高于行級鎖，在多種情況下。然而，在最差的情況下，它能夠使用更多記憶體比使用正常鎖。

>代表使用行級鎖，你能夠使用應用級鎖，例如通過使用GET LOCK()和RELEASE_LOCK()在MySQL中提供。這些是對鎖的指導，是以他們會和合作的應用一起工作。具體請看12.20的多方面函數。