MySQL表級鎖使用說明

2022-10-22 14:01:10

表級鎖

該鎖會鎖定整張表，它是MySQL中最基本的鎖策略，並不依賴於儲存引擎(不管你是MySQL的什麼儲存引擎，對於表鎖的策略都是一樣的)，並且表鎖是開銷最小的策略（因為粒度比較大)。由於表級鎖一次會將整個表鎖定，所以可以很好的避免死鎖問題。當然，鎖的粒度大所帶來最大的負面影響就是出現鎖資源爭用的概率也會最高，導致並行率大打折扣。

1、表級別的S鎖，X鎖

InnoDB儲存引擎

在對某個表執行SELECT、INSERT、DELETE、UPDATE 語句時，InnoDB儲存引擎是不會為這個表新增表級別的S鎖或者X鎖的。

一般情況下，不會使用InnoDB儲存引擎提供的表級別的S鎖和X鎖。只會在一些特殊情況下，比方說崩潰恢復過程中用到。

InnoDB儲存引擎下，手動新增表t的S鎖或X鎖：

lock tables t read  -- S鎖
lock tables t write  -- X鎖

不過儘量避免在使用InnoDB儲存引擎的表上使用LOCK TABLES這樣的手動鎖表語句，它們並不會提供什麼額外的保護，只是會降低並行能力而已。

MyISAM儲存引擎

MyISAM 的表級鎖有2種模式，分別為：表共用讀鎖（S鎖）和表獨佔寫鎖（X鎖）。

表共用讀鎖（S鎖）：當開啟事務A 獲取表共用讀鎖，則其他新開啟事務只能讀取資料，不能對操作的同張表進行更新或者插入操作，刪除操作，

表獨佔寫鎖（X鎖）：當開啟事務A 獲取獨佔寫鎖，則其他新開啟的事物讀取，新增，修改，刪除等操作會處於阻塞狀態，只到事務A 主動釋放鎖。

MyISAM儲存引擎下，手動新增表t的S鎖或X鎖：

lock tables t read  -- S鎖
lock tables t write  -- X鎖

可通過 show status like 'tables%'; 命令來檢視 mysql 內部表級鎖定的情況：

2、意向鎖

意向鎖概述

InnoDB支援多粒度鎖(multiple granularity locking)，它允許行級鎖與表級鎖共存，而意向鎖就是其中的一種表鎖。

==意向鎖的存在是為了協調行鎖和表鎖的關係，支援多粒度（表鎖與行鎖）的鎖並存。==

意向鎖是一種不與行級鎖衝突的表級鎖，這一點非常重要。

意向鎖分為兩種：

意向共用鎖（intention shared lock, IS）：事務有意向對錶中的某些行加共用鎖（S鎖）

select column from table ... lock in share mode; --

意向排他鎖（intention exclusive lock, IX）：事務有意向對錶中的某些行加排他鎖（X鎖）

select column from table ... for mode; --

申請意向鎖的動作是資料庫完成的，就是說，事務A申請一行的行鎖的時候，資料庫會自動先開始申請表的意向鎖，不需要我們程式設計師使用程式碼來申請。

意向鎖解決的問題

事務A鎖住了表中的一行，讓這一行只能讀，不能寫。之後，事務B申請整個表的寫鎖。

如果事務B申請成功，那麼理論上它就能修改表中的任意一行，這與A持有的行鎖是衝突的。

資料庫需要避免這種衝突，就是說要讓B的申請被阻塞，直到A釋放了行鎖。於是就有了意向鎖。

事務B只需檢查表上的意向鎖，發現表上有意向共用鎖IS，說明表中有些行被共用行鎖鎖住了，因此，事務B申請表的寫鎖會被阻塞。

在資料表的場景中，如果我們給某一行資料加上了排它鎖，資料庫會自動給更大一級的空間，比如資料頁或資料表加上意向鎖，告訴其他人這個資料頁或資料表已經有人上過排它鎖了，這樣當其他人想要獲取資料表排它鎖的時候，只需要瞭解是否有人已經獲取了這個資料表的意向排他鎖即可。

如果事務想要獲得資料表中某些記錄的共用鎖，就需要在資料表上新增意向共用鎖。
如果事務想要獲得資料表中某些記錄的排他鎖，就需要在資料表上新增意向排他鎖。

意向鎖的並行性

開啟一個事務，並給查詢記錄加上X鎖：此時針對查詢的記錄還加上了一個表級別的共用排它鎖(IX)

再開啟一個事務，查詢不同記錄，並給查詢記錄加上X鎖：表級別的 IX共用排它鎖加鎖成功，因為兩次事務加的IX是針對不同的記錄的

結論:

InnoDB支援多粒度鎖，特定場景下，行級鎖可以與表級鎖共存。
意向鎖之間互不排斥，但除了IS與S相容外，意向鎖會與共用鎖/排他鎖互斥。
lX，IS是表級鎖，不會和行級的X，S鎖發生衝突。只會和表級的X，S發生衝突。
意向鎖在保證並行性的前提下，實現了行鎖和表鎖共存且滿足事務隔離性的要求。

3、自增鎖（AUTO-INC鎖）

自增鎖是MySQL一種特殊的鎖，如果表中存在自增欄位，當向表中插入資料時，MySQL便會自動維護一個表級的自增鎖。

在執行插入語句時就在表級別加一個AUTO-INC鎖，然後為每條待插入記錄的AUTO_INCREMENT修飾的列分配遞增的值，在該語句執行結束後，再把AUTO-INC鎖釋放掉。

一個事務在持有AUTO-INC鎖的過程中，其他事務的插入語句都要被阻塞，可以保證一個語句中分配的遞增值是連續的。也正因為此，其並行性顯然並不高，當我們向一個有AUTO_INCREMENT關鍵字的主鍵插入值的時候，每條語句都要對這個表鎖進行競爭，這樣的並行潛力其實是很低下的。

所以 innodb 引擎通過設定 innodb_autoinc_lock_mode 的值來提供不同的鎖定機制，來顯著提高sQL語句的可伸縮性和效能。

innodb_autoinc_lock_mode有三個取值：0,1,2

tradition（innodb_autoinc_lock_mode = 0) 模式：==傳統==鎖定模式

它提供了一個向後相容的能力
在這一模式下，所有型別的insert語句都會在語句開始的時候得到一個表級的auto_inc鎖，用於插入具有auto_inc列的表，在語句結束的時候才釋放這把鎖，注意，這裡說的是語句級而不是事務級的，一個事務可能包涵有一個或多個語句。
它能保證值分配的可預見性，與連續性，可重複性，這個也就保證了insert語句在複製到slave的時候還能生成和master那邊一樣的值(它保證了基於語句複製的安全)。
由於在這種模式下auto_inc鎖一直要保持到語句的結束，所以這個就影響到了並行的插入。因為是表級鎖，當在同一時間多個事務中執行 insert 的時候，對於auto_inc鎖的爭奪會限制並行能力。

consecutive（innodb_autoinc_lock_mode = 1) 模式：==連續==鎖定模式

在MySQL8.0之前，==連續==鎖定模式是預設的新增模式
這一模式在simple insert （要插入的行數已知）做了優化，由於simple insert一次性插入值的個數可以立馬得到確定，所以mysql可以一次生成幾個連續的值，用於這個insert語句；總的來說這個對複製也是安全的 (它保證了基於語句複製的安全)
這一模式也是mysql的預設模式，這個模式的好處是auto_inc鎖不要一直保持到語句的結束，只要語句得到了相應的值後就可以提前釋放鎖

interleaved（innodb_autoinc_lock_mode = 2) 模式：==交錯==鎖定模式

在MySQL8.0，==交錯==鎖定模式是預設的新增模式
由於這個模式下所有insert語句都不回使用表級auto_inc鎖，並且可以同時執行多個語句，這是最快和最可延伸的鎖定模式，所以這個模式下的效能是最好的；但是它也有一個問題，由於多個語句可以同時生成數位，為任何給定語句插入的行生成的值可能是不連續的。