8.11 優化器鎖操作

04-21

參考官方文檔：

https://dev.mysql.com/doc/refman/5.7/en/locking-issues.html

MySQL使用鎖管理對錶內容的爭用：

內部鎖定在MySQL伺服器內部執行，以管理多個線程對錶內容的爭用。這種類型的鎖定是內部的，因為它完全由伺服器執行，並且不涉及其他程序。
當伺服器和其他程序鎖定MyISAM表文件以在它們自己之間協調哪個程序可以在哪個時間訪問表時發生外部鎖定。

8.11.1 內部鎖定方法

本節討論內部鎖定; 即在MySQL伺服器本身內執行鎖定以管理多個會話對錶內容的爭用。這種類型的鎖定是內部的，因為它完全由伺服器執行，並且不涉及其他程序

行級別鎖定

MySQL對InnoDB表使用行級鎖定來支持多個會話的同時寫入訪問，使其適用於多用戶，高度並發和OLTP應用程序。

為避免在單個InnoDB表上執行多個並發寫入操作時發生死鎖，通過針對每組預計要修改的行發出SELECT ... FOR UPDATE語句，在事務啟動時獲取必要的鎖定，即使數據更改語句稍後在事務中。如果事務修改或鎖定多個表，請在每個事務中以相同的順序發布適當的語句。死鎖會影響性能而不是表示嚴重的錯誤，因為InnoDB會自動檢測死鎖條件並回滾其中一個受影響的事務。

在高並發系統上，當大量線程等待相同的鎖時，死鎖檢測會導致速度下降。有時，在死鎖發生時，禁用死鎖檢測並依賴innodb_lock_wait_timeout設置進行事務回滾可能更有效。使用innodb_deadlock_detect配置選項可以禁用死鎖檢測。

行級鎖定的優點：

當不同的會話訪問不同的行時，更少的鎖衝突。
回滾更少。
可能長時間鎖定一行。

表級別鎖定

MySQL對MyISAM，MEMORY和MERGE表使用表級鎖定，一次只允許一個會話更新這些表。這種鎖定級別使得這些存儲引擎更適合於只讀，主要讀取或單用戶應用程序。

這些存儲引擎通過始終在查詢開始時一次請求所有需要的鎖，並始終以相同的順序鎖定表來避免死鎖。權衡是這種策略降低了並發性; 其他要修改表的會話必須等到當前數據更改語句結束。

表級別鎖定的優勢：

所需的內存相對較少（行鎖定需要鎖定每行或一組行的內存）
在大部分表上使用時都很快，因為只涉及一個鎖。
如果您經常對大部分數據執行GROUP BY操作，或者必須頻繁掃描整個表，則速度很快。

MySQL授予表寫鎖定如下：

如果表上沒有鎖，在其上加寫鎖。
否則，將鎖定請求放入寫鎖定隊列中。

MySQL授予表讀鎖定如下：

如果表上沒有寫鎖，那麼在上面加入讀鎖。
否則，將鎖定請求放入讀鎖定隊列中。

表更新優先於表格檢索。因此，當釋放一個鎖時，該鎖可用於寫鎖定隊列中的請求，然後可用於讀鎖定隊列中的請求。這可確保即使表中的SELECT活動很繁重，表的更新也不會「餓死」。但是，如果表有很多更新，則SELECT語句會等待，直到沒有更新。

您可以通過檢查Table_locks_immediate和Table_locks_waited狀態變數來分析系統上的表鎖爭用，這些變數分別指示可以立即授予表鎖請求的次數和需要等待的數量：

SHOW STATUS LIKE Table%;

MyISAM存儲引擎支持 concurrent插入以減少給定表的讀，寫之間的爭用：如果MyISAM表在數據文件中間沒有空閑塊，則總是在數據文件的末尾插入行。在這種情況下，您可以自由地將MyISAM表的並發INSERT和SELECT語句混合使用，而無需鎖定。也就是說，您可以在其他客戶端正在讀取的同時將行插入到MyISAM表中。在表中間刪除或更新的行可能會產生空洞。如果存在空洞，則 concurrent插入將被禁用，但當所有孔已填充新數據時將自動再次啟用。要控制這種行為，請使用concurrent_insert系統變數。

如果使用LOCK TABLES顯式獲取表鎖，則可以請求READ LOCAL鎖而不是READ鎖，以便在表鎖定時允許其他會話執行 concurrent 插入。

要在concurrent 插入不可能的情況下對錶t1執行多個INSERT和SELECT操作，可以將行插入臨時表temp_t1中，並使用臨時表中的行更新實際表：

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;

SELECT結合了並發INSERT語句，以及很少的UPDATE或DELETE語句。
許多掃描或GROUP BY操作在整個表上沒有任何作家。

對於更高級別的鎖定，您可以通過支持不同類型的鎖定來更輕鬆地調整應用程序，因為鎖定開銷比行級別的鎖定要少。

行級鎖定以外的選項：

版本控制（例如在MySQL中用於並發插入的版本），可以在多個讀的同一時間擁有一個寫。這意味著根據訪問何時開始，資料庫或表支持不同數據視圖。其他常見術語是「 time travel」，「 copy on write」或「 copy on demand」。
在許多情況下，copy on demand優於行級鎖定。但是，在最壞的情況下，它可以使用比使用普通鎖更多的內存。
您可以使用應用程序級鎖，例如MySQL中的GET_LOCK（）和RELEASE_LOCK（）提供的鎖，而不是使用行級鎖。這些是諮詢鎖，因此它們只能與相互合作的應用程序一起工作。

8.11.2 表鎖定發布

InnoDB表使用行級鎖定，以便多個會話和應用程序可以同時讀取和寫入同一個表，而不會讓對方等待或產生不一致的結果。對於這個存儲引擎，避免使用LOCK TABLES語句，因為它沒有提供任何額外的保護，而是降低了並發性。自動的行級鎖定使得這些表適用於最繁忙的資料庫和最重要的數據，同時還可以簡化應用程序邏輯，因為您無需鎖定和解鎖表。因此，InnoDB存儲引擎是MySQL中的默認設置。

除InnoDB外，MySQL對所有存儲引擎都使用表鎖（而不是頁鎖，行鎖或列鎖）。鎖定操作本身沒有太多的開銷。但是因為只有一個會話可以同時寫入一個表，為了獲得這些其他存儲引擎的最佳性能，請將它們主要用於經常查詢且很少插入或更新的表。

（OLAP，分析庫環境）

InnoDB 性能考慮因素

在選擇是使用InnoDB還是其他存儲引擎創建表時，請記住表鎖定的以下缺點：

表鎖定使許多會話可以同時從表中讀取數據，但是如果會話要寫入表中，它必須首先獲得獨佔訪問權，這意味著它可能必須先等待其他會話完成表。在更新期間，想要訪問此特定表的所有其他會話必須等到更新完成。
由於磁碟已滿並且在會話可繼續之前需要有空閑空間，此時會話會等待，表鎖定會導致問題。在這種情況下，所有希望訪問問題表的會話也會處於等待狀態，直到有更多磁碟空間可用。
需要很長時間運行的SELECT語句會阻止其他會話同時更新表，從而使其他會話顯得緩慢或無響應。雖然會話正在等待獨佔訪問表以進行更新，但發出SELECT語句的其他會話將在其後面排隊，即使對於只讀會話也會降低並發性。

繞過鎖性能問題

以下各項介紹了一些避免或減少表鎖定引起的爭用的方法：

考慮將表切換到InnoDB存儲引擎，在安裝過程中使用CREATE TABLE ... ENGINE=INNODB，或對現有表使用ALTER TABLE ... ENGINE=INNODB。
優化SELECT語句以加快運行速度，以便鎖定表的時間更短。您可能需要創建一些匯總表來執行此操作。
用--low-priority-updates啟動mysqld。對於僅使用表級鎖定的存儲引擎（例如MyISAM，MEMORY和MERGE），這會為所有更新（修改）表的語句賦予低於SELECT語句的優先順序。在這種情況下，前面的場景中的第二個SELECT語句將在UPDATE語句之前執行，並且不會等待第一個SELECT完成。（更新的優先順序會降低，排在最後）
要指定在特定連接中發出的所有更新應該以低優先順序完成，請將low_priority_updates伺服器系統變數設置為1。
要為特定的INSERT，UPDATE或DELETE語句賦予較低的優先順序，請使用LOW_PRIORITY屬性。
要為特定的SELECT語句賦予更高的優先順序，請使用HIGH_PRIORITY屬性。
使用max_write_lock_count系統變數的低值啟動mysqld，以強制MySQL臨時提升在發生特定數量的表插入後正在等待表的所有SELECT語句的優先順序。這允許一定數量的WRITE鎖之後的READ鎖。
如果您在INSERT與SELECT結合時遇到問題，請考慮切換到支持並發SELECT和INSERT語句的MyISAM表。
如果混合SELECT和DELETE語句有問題，DELETE的LIMIT選項可能會有所幫助。
對SELECT語句使用SQL_BUFFER_RESULT可以幫助縮短表鎖的持續時間。
通過允許查詢針對一個表中的列運行，而將更新限制在不同表中的列中，將表內容拆分為單獨的表格可能會有所幫助。（把容易更新的列單獨拆分出去）
您可以將mysys/thr_lock.c中的鎖定代碼更改為使用單個隊列。在這種情況下，寫入鎖和讀取鎖具有相同的優先順序，這可能有助於某些應用程序。

8.11.3 Concurrent inserts

MyISAM存儲引擎支持並發插入以減少給定表的讀寫器之間的爭用：如果MyISAM表在數據文件中沒有空洞（中間刪除行），則可以執行INSERT語句以將行添加到末尾同時SELECT語句正在讀取表中的行。如果存在多個INSERT語句，則它們將與SELECT語句同時排隊並執行。並發INSERT的結果可能不會立即可見。

concurrent_insert系統變數可以設置為修改並發插入處理。默認情況下，該變數設置為AUTO（或1），並按前面所述處理並發插入。如果concurrent_insert設置為NEVER（或0），則禁用並發插入。如果變數設置為ALWAYS（或2），即使對於已刪除行的表，也允許在表的末尾進行並發插入。另請參閱concurrent_insert系統變數的說明。

如果您正在使用二進位日誌，並發插入將轉換為CREATE ... SELECT或INSERT ... SELECT語句的正常插入。這樣做是為了確保您可以通過在備份操作期間應用日誌來重新創建表的精確副本。另外，對於這些語句，在選定表上放置一個讀鎖，以便阻止插入到該表中。結果是該表的並發插入必須等待。

使用LOAD DATA INFILE，如果使用滿足並發插入條件的MyISAM表（即中間不包含空閑塊）指定CONCURRENT，則其他會話可在LOAD DATA執行時從表中檢索數據。即使沒有其他會話同時使用該表，CONCURRENT選項的使用也會影響LOAD DATA的性能。

如果您指定HIGH_PRIORITY，則它會覆蓋--low-priority-updates選項的效果，如果伺服器是使用該選項啟動的。這也會導致並發插入不被使用。

對於LOCK TABLE，READ LOCAL和READ之間的區別在於READ LOCAL允許在鎖定期間執行不衝突的INSERT語句（並發插入）。但是，如果要在持有鎖的同時使用伺服器外部的進程來操作資料庫，則無法使用此功能。

8.11.4 元數據的鎖定

MySQL使用元數據鎖定來管理對資料庫對象的並發訪問並確保數據一致性。元數據鎖定不僅適用於表，還適用於方案，存儲的程序（過程，函數，觸發器和調度事件）以及表空間（MySQL 5.7.6）。

元數據鎖定確實涉及一些開銷，隨著查詢量的增加而增加。元數據爭用增加了多個查詢嘗試訪問相同對象的次數越多。

元數據鎖定不是表定義緩存的替代，其 mutexes 和 locks 與LOCK_open mutex不同。以下討論提供了有關元數據鎖定工作原理的一些信息。

為確保事務可序列化，伺服器不得允許一個會話在另一個會話的未完成時，顯式或隱式在啟動事務中使用的表上執行DDL語句。伺服器通過獲取事務內使用的表上的元數據鎖，並推遲釋放這些鎖，直到事務結束為止。表上的元數據鎖可以防止對錶的結構進行更改。這種鎖定方式意味著一個會話中的事務正在使用的表不能在其他會話中用於DDL語句，直到事務結束。

這個原則不僅適用於事務表，也適用於非事務表。假設會話開始一個使用事務表t和非事務表nt的事務，如下所示：

START TRANSACTION;

SELECT * FROM t;

SELECT * FROM nt;

伺服器在t和nt上保存元數據鎖，直到事務結束。如果另一個會話嘗試對任一表執行DDL或寫入鎖定操作，則會阻塞，直到事務結束時釋放元數據鎖。例如，如果第二個會話嘗試執行以下任何操作，則會阻止：

DROP TABLE t;

ALTER TABLE t ...;

DROP TABLE nt;

ALTER TABLE nt ...;

LOCK TABLE t ... WRITE;

同樣的行為適用於 LOCK TABLES ... READ。也就是說，更新任何錶（事務或非事務）的顯式或隱式啟動事務將被該表的LOCK TABLES ... READ阻塞和並阻塞。

如果伺服器為語句有效但在執行期間失敗的語句獲取元數據鎖，則它不會提前釋放鎖。鎖釋放仍然延遲到事務結束，因為失敗的語句被寫入二進位日誌，並且鎖保護日誌一致性。

在自動提交模式下，每條語句實際上是一個完整的事務，因此為語句獲取的元數據鎖只保留在語句的末尾。

即使preparation發生在在多語句事務中，在準備語句之後，PREPARE語句期間獲取的元數據鎖也會被釋放。

8.11.5 外部鎖定

外部鎖定是使用文件系統鎖定來管理多個進程對MyISAM資料庫表的爭用。外部鎖定用於單個進程（如MySQL伺服器）不能被認為是唯一需要訪問表的進程的情況。這裡有些例子：

如果運行多個使用相同資料庫目錄的伺服器（不推薦），則每台伺服器都必須啟用外部鎖定。
如果使用myisamchk在MyISAM表上執行表維護操作，則必須確保伺服器未運行，或者伺服器啟用了外部鎖定，以便根據需要鎖定表文件以與myisamchk協調以訪問表。使用myisampack打包MyISAM表也是如此。

如果伺服器在啟用外部鎖定的情況下運行，則可以隨時使用myisamchk進行讀取操作，例如檢查表。在這種情況下，如果伺服器嘗試更新myisamchk正在使用的表，伺服器將在繼續之前等待myisamchk完成。

如果使用myisamchk進行寫入操作（例如修復或優化表），或者如果使用myisampack打包表，則必須始終確保mysqld伺服器未使用表。如果您不停止mysqld，至少在運行myisamchk之前執行mysqladmin flush-tables。如果伺服器和myisamchk同時訪問表，您的表可能會損壞。

在外部鎖定生效的情況下，每個需要訪問表的進程在繼續訪問表之前都會獲取表文件的文件系統鎖。如果無法獲取所有必需的鎖，則會阻止該進程訪問表，直到獲得鎖（在當前持有鎖的進程釋放它們之後）。

外部鎖定會影響伺服器性能，因為伺服器在訪問表之前必須等待其他進程。

如果您運行單個伺服器來訪問給定的數據目錄（通常情況下），並且在伺服器運行時沒有其他程序（如myisamchk）需要修改表，則不需要外部鎖定。如果只使用其他程序讀取表，則不需要外部鎖定，儘管如果伺服器在myisamchk正在讀取表格時更改表，myisamchk可能會報告警告。

在禁用外部鎖定的情況下，要使用myisamchk，必須在執行myisamchk時停止伺服器，否則在運行myisamchk之前鎖定並刷新表。（請參見第8.12.1節「系統因素」。）為避免此要求，請使用CHECK TABLE和REPAIR TABLE語句來檢查和修復MyISAM表。

對於mysqld，外部鎖定由skip_external_locking系統變數的值控制。當這個變數被啟用時，外部鎖定被禁用，反之亦然。外部鎖定默認是禁用的。

使用外部鎖定可以在伺服器啟動時通過使用 - 外部鎖定或--skip-external-locking選項來控制。

如果確實使用外部鎖定選項來啟用來自多個MySQL進程的MyISAM表的更新，則必須確保滿足以下條件：

不要將查詢緩存用於使用另一個進程更新的表的查詢。
不要使用--delay-key-write = ALL選項啟動伺服器，或者對任何共享表使用DELAY_KEY_WRITE = 1表選項。否則，可能會發生索引損壞。

滿足這些條件的最簡單方法是始終使用--external-locking和--delay-key-write = OFF和--query-cache-size = 0。（這不是默認完成的，因為在許多設置中，混合使用上述選項很有用。）