【MySQL】MySQL中的鎖機制

週二鴨發表於2020-11-15
MySql

MySQL鎖機制起步

鎖是計算機用以協調多個程式間併發訪問同一共享資源的一種機制。MySQL中為了保證資料訪問的一致性與有效性等功能，實現了鎖機制，MySQL中的鎖是在伺服器層或者儲存引擎層實現的。

行鎖與表鎖

首先我們來了解行鎖與表鎖的基本概念，從名字中我們就可以瞭解：表鎖就是對整張表進行加鎖，而行鎖則是鎖定某行、某幾行資料或者行之間的間隙。

各引擎對鎖的支援情況如下：

行鎖 表鎖 頁鎖
MyISAM √
BDB √ √
InnoDB √ √

1. 行鎖

A record lock is a lock on an index record. Record locks always lock index records, even if a table is defined with no indexes. For such cases, InnoDB creates a hidden clustered index and uses this index for record locking.

上文出自MySQL的官方文件，從這裡我們可以看出行鎖是作用在索引上的，哪怕你在建表的時候沒有定義一個索引，InnoDB也會建立一個聚簇索引並將其作為鎖作用的索引。

這裡還是講一下InnoDB中的聚簇索引。每一個InnoDB表都需要一個聚簇索引，有且只有一個。如果你為該表表定義一個主鍵，那麼MySQL將使用主鍵作為聚簇索引；如果你不為定義一個主鍵，那麼MySQL將會把第一個唯一索引（而且要求NOT NULL）作為聚簇索引；如果上訴兩種情況都GG，那麼MySQL將自動建立一個名字為GEN_CLUST_INDEX的隱藏聚簇索引。

因為是聚簇索引，所以B+樹上的葉子節點都儲存了資料行，那麼如果現在是二級索引呢？InnoDB中的二級索引的葉節點儲存的是主鍵值（或者說聚簇索引的值），所以通過二級索引查詢資料時，還需要將對應的主鍵去聚簇索引中再次進行查詢。

關於索引的問題就到這，我們用一張直觀的圖來表示行鎖：

接下來以兩條SQL的執行為例，講解一下InnoDB對於單行資料的加鎖原理：

update user set age = 10 where id = 49;
update user set age = 10 where name = 'Tom';

第一條SQL使用主鍵查詢，只需要在 id = 49 這個主鍵索引上加上鎖。第二條 SQL 使用二級索引來查詢，那麼首先在 name = Tom 這個索引上加寫鎖，然後由於使用 InnoDB 二級索引還需再次根據主鍵索引查詢，所以還需要在 id = 49 這個主鍵索引上加鎖。

也就是說使用主鍵索引需要加一把鎖，使用二級索引需要在二級索引和主鍵索引上各加一把鎖。

根據索引對單行資料進行更新的加鎖原理了解了，那如果更新操作涉及多個行呢，比如下面 SQL 的執行場景。

update user set age = 10 where id > 49;

上述 SQL 的執行過程如下圖所示。MySQL Server 會根據 WHERE 條件讀取第一條滿足條件的記錄，然後 InnoDB 引擎會將第一條記錄返回並加鎖，接著 MySQL Server 發起更新改行記錄的 UPDATE 請求，更新這條記錄。一條記錄操作完成，再讀取下一條記錄，直至沒有匹配的記錄為止。

2. 表鎖

上面我們講解行鎖的時候，操作語句中的條件判斷列都是有建立索引的，那麼如果現在的判斷列不存在索引呢？InnoDB既支援行鎖，也支援表鎖，當沒有查詢列沒有索引時，InnoDB就不會去搞什麼行鎖了，畢竟行鎖一定要有索引，所以它現在搞表鎖，把整張表給鎖住了。那麼具體啥是表鎖？還有其他什麼情況下也會進行鎖表呢？

表鎖使用的是一次性鎖技術，也就是說，在會話開始的地方使用 lock 命令將後續需要用到的表都加上鎖，在表釋放前，只能訪問這些加鎖的表，不能訪問其他表，直到最後通過 unlock tables 釋放所有表鎖。

除了使用 unlock tables 顯示釋放鎖之外，會話持有其他表鎖時執行lock table 語句會釋放會話之前持有的鎖；會話持有其他表鎖時執行 start transaction 或者 begin 開啟事務時，也會釋放之前持有的鎖。

表鎖由 MySQL Server 實現，行鎖則是儲存引擎實現，不同的引擎實現的不同。在 MySQL 的常用引擎中 InnoDB 支援行鎖，而 MyISAM 則只能使用 MySQL Server 提供的表鎖。

3. 兩種鎖的比較

表鎖：加鎖過程的開銷小，加鎖的速度快；不會出現死鎖的情況；鎖定的粒度大，發生鎖衝突的機率大，併發度低；

  • 一般在執行DDL語句時會對整個表進行加鎖，比如說 ALTER TABLE 等操作；

  • 如果對InnoDB的表使用行鎖，被鎖定欄位不是主鍵，也沒有針對它建立索引的話，那麼將會鎖整張表；

  • 表級鎖更適合於以查詢為主，併發使用者少，只有少量按索引條件更新資料的應用，如Web 應用。

行鎖：加鎖過程的開銷大，加鎖的速度慢；會出現死鎖；鎖定粒度最小，發生鎖衝突的概率最低，併發度也最高；

  • 最大程度的支援併發，同時也帶來了最大的鎖開銷。
  • 在 InnoDB 中，除單個 SQL 組成的事務外，鎖是逐步獲得的，這就決定了在 InnoDB 中發生死鎖是可能的。
  • 行級鎖只在儲存引擎層實現，而 MySQL 伺服器層沒有實現。 行級鎖更適合於有大量按索引條件併發更新少量不同資料，同時又有併發查詢的應用，如一些線上事務處理（OLTP）系統。

MyISAM表鎖

1. MyISAM表級鎖模式

  • 表共享讀鎖（Table Read Lock）：不會阻塞其他執行緒對同一個表的讀操作請求，但會阻塞其他執行緒的寫操作請求；

  • 表獨佔寫鎖（Table Write Lock）：一旦表被加上獨佔寫鎖，那麼無論其他執行緒是讀操作還是寫操作，都會被阻塞；

預設情況下，寫鎖比讀鎖具有更高的優先順序；當一個鎖釋放後，那麼它會優先相應寫鎖等待佇列中的鎖請求，然後再是讀鎖中等待的獲取鎖的請求。

This ensures that updates to a table are not “starved” even when there is heavy SELECT activity for the table. However, if there are many updates for a table, SELECT statements wait until there are no more updates.

這種設定也是MyISAM表不適合於有大量更新操作和查詢操作的原因。大量更新操作可能會造成查詢操作很難以獲取讀鎖，從而過長的阻塞。同時一些需要長時間執行的查詢操作，也會使得執行緒“餓死”，應用中應儘量避免出現長時間執行的查詢操作（在可能的情況下可以通過使用中間表等措施對SQL語句做一定的“分解”，使每一步查詢都能在較短的時間內完成，從而減少鎖衝突。如果複雜查詢不可避免，應儘量安排在資料庫空閒時段執行，比如一些定期統計可以安排在夜間執行。）

我們可以通過一些設定來調節MyISAM的排程行為：

  • 通過指定啟動引數low-priority-updates，使MyISAM引擎預設給予讀請求以優先的權利；
  • 通過執行命令SET LOW_PRIORITY_UPDATES=1，使該連線發出的更新請求優先順序降低；
  • 通過指定INSERT、UPDATE、DELETE語句的LOW_PRIORITY屬性，降低該語句的優先順序；
  • 給系統引數max_write_lock_count設定一個合適的值，當一個表的讀鎖達到這個值後，MySQL就暫時將寫請求的優先順序降低，給讀程式一定獲得鎖的機會。

2. MyISAM對錶加鎖分析

MyISAM在執行查詢語句（SELECT）前，會自動給涉及的所有表加讀鎖，在執行更新操作（UPDATE、DELETE、INSERT等）前，會自動給涉及的表加寫鎖，這個過程並不需要使用者干預，因此使用者一般不需要直接用 LOCK TABLE 命令給 MyISAM 表顯式加鎖。在自動加鎖的情況下，MyISAM 總是一次獲得 SQL 語句所需要的全部鎖，這也正是 MyISAM 表不會出現死鎖（Deadlock Free）的原因。

MyISAM儲存引擎支援併發插入，以減少給定表的讀操作和寫操作之間的爭用：

如果MyISAM表在資料檔案中沒有空閒塊（由於刪除或更新導致的空行），則行始終插入資料檔案的末尾。在這種情況下，你可以自由混合併發使用MyISAM表的 INSERT 和 SELECT 語句而不需要加鎖（你可以在其他執行緒進行讀操作的情況下，同時將行插入到MyISAM表中）。如果檔案中有空閒塊，則併發插入會被禁止，但當所有的空閒塊重新填充有新資料時，它又會自動啟用。 要控制此行為，可以使用MySQL的concurrent_insert系統變數。

  • 當concurrent_insert=0時，不允許併發插入功能。

  • 當concurrent_insert=1時，允許對沒有空閒塊的表使用併發插入，新資料位於資料檔案結尾（預設）。

  • 當concurrent_insert=2時，不管表有沒有空想快，都允許在資料檔案結尾併發插入。

3. 顯式加表鎖的應用

上面已經提及了表鎖的加鎖方式，一般表鎖都是隱式加鎖的，不需要我們去主動宣告，但是也有需要顯式加鎖的情況，這裡簡單做下介紹：

給MyISAM表顯式加鎖，一般是為了一定程度模擬事務操作，實現對某一時間點多個表的一致性讀取。例如，有一個訂單表orders，其中記錄有訂單的總金額total，同時還有一個訂單明細表 order_detail，其中記錄有訂單每一產品的金額小計 subtotal，假設我們需要檢查這兩個表的金額合計是否相等，可能就需要執行如下兩條SQL：

SELECT SUM(total) FROM orders;
SELECT SUM(subtotal) FROM order_detail;

這時，如果不先給這兩個表加鎖，就可能產生錯誤的結果，因為第一條語句執行過程中，order_detail表可能已經發生了改變。因此，正確的方法應該是：

LOCK tables orders read local,order_detail read local;
SELECT SUM(total) FROM orders;
SELECT SUM(subtotal) FROM order_detail;
Unlock tables;

4. 檢視錶鎖爭用情況：

可以通過檢查 table_locks_waited 和 table_locks_immediate 狀態變數來分析系統上的表鎖的爭奪，如果 Table_locks_waited 的值比較高，則說明存在著較嚴重的表級鎖爭用情況：

mysql> SHOW STATUS LIKE 'Table%';
+-----------------------+---------+
| Variable_name | Value |
+-----------------------+---------+
| Table_locks_immediate | 1151552 |
| Table_locks_waited | 15324 |
+-----------------------+---------+

InnoDB行鎖與表鎖

1. InnoDB鎖模式

1）InnoDB中的行鎖

InnoDB實現了以下兩種型別的行鎖：

  • 共享鎖（S）：加了鎖的記錄，所有事務都能去讀取但不能修改，同時阻止其他事務獲得相同資料集的排他鎖；
  • 排他鎖（X）：允許已經獲得排他鎖的事務去更新資料，阻止其他事務取得相同資料集的共享讀鎖和排他寫鎖；

2）InnoDB表鎖——意向鎖

由於表鎖和行鎖雖然鎖定範圍不同，但是會相互衝突。當你要加表鎖時，勢必要先遍歷該表的所有記錄，判斷是否有排他鎖。這種遍歷檢查的方式顯然是一種低效的方式，MySQL引入了意向鎖，來檢測表鎖和行鎖的衝突。

Intention locks are table-level locks that indicate which type of lock (shared or exclusive) a transaction requires later for a row in a table。

The intention locking protocol is as follows:

  • Before a transaction can acquire a shared lock on a row in a table, it must first acquire an IS lock or stronger on the table.

  • Before a transaction can acquire an exclusive lock on a row in a table, it must first acquire an IX lock on the table.

意向鎖也是表級鎖，分為讀意向鎖（IS鎖）和寫意向鎖（IX鎖）。當事務要在記錄上加上行鎖時，要首先在表上加上意向鎖。這樣判斷表中是否有記錄正在加鎖就很簡單了，只要看下錶上是否有意向鎖就行了，從而就能提高效率。

意向鎖之間是不會產生衝突的，它只會阻塞表級讀鎖或寫鎖。意向鎖不於行級鎖發生衝突。

2. 鎖模式的相容矩陣

下面表顯示了了各種鎖之間的相容情況：

X IX S IS
X
IX 相容 相容
S 相容 相容
IS 相容 相容 相容

（注意上面的X與S是說表級的X鎖和S鎖，意向鎖不和行級鎖發生衝突）

如果一個事務請求的鎖模式與當前的鎖相容，InnoDB就將請求的鎖授予該事務；如果兩者不相容，那麼該事務就需要等待鎖的釋放。

3. InnoDB的加鎖方法

  • 意向鎖是 InnoDB 自動加的，不需要使用者干預；
  • 對於UPDATE、DELETE和INSERT語句，InnoDB會自動給涉及的資料集加上排他鎖；
  • 對於普通的SELECT語句，InnoDB不會加任何鎖；事務可以通過以下語句顯示給記錄集新增共享鎖或排他鎖：
    • 共享鎖（S）：select * from table_name where ... lock in share mode。此時其他 session 仍然可以查詢記錄，並也可以對該記錄加 share mode 的共享鎖。但是如果當前事務需要對該記錄進行更新操作，則很有可能造成死鎖。
    • 排他鎖（X）：select * from table_name where ... for update。其他session可以查詢記錄，但是不能對該記錄加共享鎖或排他鎖，只能等待鎖釋放後在加鎖。

3.1 select for update

在執行這個 select 查詢語句的時候，會將對應的索引訪問條目加上排他鎖（X鎖），也就是說這個語句對應的鎖就相當於update帶來的效果；

使用場景：為了讓確保自己查詢到的資料一定是最新資料，並且查詢到後的資料值允許自己來修改，此時就需要用到select for update語句；

效能分析：select for update語句相當於一個update語句。在業務繁忙的情況下，如果事務沒有及時地commit或者rollback可能會造成事務長時間的等待，從而影響資料庫的併發使用效率。

3.2 select lock in share mode

in share mode 子句的作用就是將查詢的資料加上一個share鎖，這個就是表示其他的事務只能對這些資料進行簡單的 select 操作，而不能進行 DML 操作。

使用場景：為了確保自己查詢的資料不會被其他事務正在修改，也就是確保自己查詢到的資料是最新的資料，並且不允許其他事務來修改資料。與select for update不同的是，本事務在查詢完之後不一定能去更新資料，因為有可能其他事務也對同資料集使用了 in share mode 的方式加上了S鎖；

效能分析：select lock in share mode 語句是一個給查詢的資料上一個共享鎖（S 鎖）的功能，它允許其他的事務也對該資料上S鎖，但是不能夠允許對該資料進行修改。如果不及時的commit 或者rollback 也可能會造成大量的事務等待。

4 . InnoDB的鎖爭用情況

可以通過檢查 InnoDB_row_lock 狀態變數來分析系統上的行鎖的爭奪情況：

mysql> show status like 'innodb_row_lock%'; 
+-------------------------------+-------+ 
| Variable_name | Value | 
+-------------------------------+-------+ 
| InnoDB_row_lock_current_waits | 0 | 
| InnoDB_row_lock_time | 0 | 
| InnoDB_row_lock_time_avg | 0 | 
| InnoDB_row_lock_time_max | 0 | 
| InnoDB_row_lock_waits | 0 | 
+-------------------------------+-------+ 
5 rows in set (0.01 sec)

行鎖的型別

上面我們根據了鎖的粒度將鎖分為了行鎖與表鎖，接下來根據使用場景的不同，又可以將行鎖進行進一步的劃分：Next-Key Lock、Gap Lock、Record Lock以及插入意向GAP鎖。

不同的鎖鎖定的位置是不同的，比如說記錄鎖只鎖定對應的記錄，而間隙鎖鎖住記錄和記錄之間的間隙，Next-key Lock則鎖住所屬記錄之間的間隙。不同的鎖型別鎖定的範圍大致如圖所示：

1. 記錄鎖（Record Lock）

記錄鎖最簡單的一種行鎖形式，上面我們以及稍微提及過了。這裡補充下的點就是：行鎖是加在索引上的，如果當你的查詢語句不走索引的話，那麼它就會升級到表鎖，最終造成效率低下，所以在寫SQL語句時需要特別注意。

2. 間隙鎖（Gap Lock）

A gap lock is a lock on a gap between index records, or a lock on the gap before the first or after the last index record。

當我們使用範圍條件而不是相等條件去檢索，並請求鎖時，InnoDB就會給符合條件的記錄的索引項加上鎖；而對於鍵值在條件範圍內但並不存在（參考上面所說的空閒塊）的記錄，就叫做間隙，InnoDB在此時也會對間隙加鎖，這種記錄鎖+間隙鎖的機制叫Next-Key Lock。額，扯的有點快。

從上面這句話可以表明間隙鎖是所在兩個存在的索引之間，是一個開區間，像最開始的那張索引圖，15和18之間，是有（16，17）這個間隙存在的。

Gap locks in InnoDB are “purely inhibitive”, which means that their only purpose is to prevent other transactions from inserting to the gap. Gap locks can co-exist. A gap lock taken by one transaction does not prevent another transaction from taking a gap lock on the same gap. There is no difference between shared and exclusive gap locks. They do not conflict with each other, and they perform the same function.

上面這段話表明間隙鎖是可以共存的，共享間隙鎖與獨佔間隙鎖之間是沒有區別的，兩者之間並不衝突。其存在的目的都是防止其他事務往間隙中插入新的紀錄，故而一個事務所採取的間隙鎖是不會去阻止另外一個事務在同一個間隙中加鎖的。

當然也不是在什麼時候都會去加間隙鎖的：

Gap locking can be disabled explicitly. This occurs if you change the transaction isolation level to READ COMMITTED. Under these circumstances, gap locking is disabled for searches and index scans and is used only for foreign-key constraint checking and duplicate-key checking.

這段話表明，在 RU 和 RC 兩種隔離級別下，即使你使用 select in share mode 或 select for update，也無法防止幻讀（讀後寫的場景）。因為這兩種隔離級別下只會有行鎖，而不會有間隙鎖。而如果是 RR 隔離級別的話，就會在間隙上加上間隙鎖。

3. 臨鍵鎖（Next-key Lock）

A next-key lock is a combination of a record lock on the index record and a gap lock on the gap before the index record.

臨鍵鎖是記錄鎖與與間隙鎖的結合，所以臨鍵鎖與間隙鎖是一個同時存在的概念，並且臨鍵鎖是個左開有閉的卻比如(16, 18]。

關於臨鍵鎖與幻讀，官方文件有這麼一條說明：

By default, InnoDB operates in REPEATABLE READ transaction isolation level. In this case, InnoDB uses next-key locks for searches and index scans, which prevents phantom rows.

就是說 MySQL 預設隔離級別是RR，在這種級別下，如果你使用 select in share mode 或者 select for update 語句，那麼InnoDB會使用臨鍵鎖（記錄鎖 + 間隙鎖），因而可以防止幻讀；

但是我也在網上看到相關描述：即使你的隔離級別是 RR，如果你這是使用普通的select語句，那麼此時 InnoDB 引擎將是使用快照讀，而不會使用任何鎖，因而還是無法防止幻讀。

4. 插入意向鎖（Insert Intention Lock）

An insert intention lock is a type of gap lock set by INSERT operations prior to row insertion. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap. Suppose that there are index records with values of 4 and 7. Separate transactions that attempt to insert values of 5 and 6, respectively, each lock the gap between 4 and 7 with insert intention locks prior to obtaining the exclusive lock on the inserted row, but do not block each other because the rows are nonconflicting.

官方文件已經解釋得很清楚了，這裡我做個翻譯機：

插入意圖鎖是一種間隙鎖，在行執行 INSERT 之前的插入操作設定。如果多個事務 INSERT 到同一個索引間隙之間，但沒有在同一位置上插入，則不會產生任何的衝突。假設有值為4和7的索引記錄，現在有兩事務分別嘗試插入值為 5 和 6 的記錄，在獲得插入行的排他鎖之前，都使用插入意向鎖鎖住 4 和 7 之間的間隙，但兩者之間並不會相互阻塞，因為這兩行並不衝突。

插入意向鎖只會和 間隙或者 Next-key 鎖衝突，正如上面所說，間隙鎖作用就是防止其他事務插入記錄造成幻讀，正是由於在執行 INSERT 語句時需要加插入意向鎖，而插入意向鎖和間隙鎖衝突，從而阻止了插入操作的執行。

5. 不同型別鎖之間的相容

不同型別的鎖之間的相容如下表所示：

RECORED GAP NEXT-KEY II GAP（插入意向鎖）
RECORED 相容 相容
GAP 相容 相容 相容 相容
NEXT-KEY 相容 相容
II GAP 相容 相容

（其中行表示已有的鎖，列表示意圖加上的鎖）

其中，第一行表示已有的鎖，第一列表示要加的鎖。插入意向鎖較為特殊，所以我們先對插入意向鎖做個總結，如下：

  • 插入意向鎖不影響其他事務加其他任何鎖。也就是說，一個事務已經獲取了插入意向鎖，對其他事務是沒有任何影響的；
  • 插入意向鎖與間隙鎖和 Next-key 鎖衝突。也就是說，一個事務想要獲取插入意向鎖，如果有其他事務已經加了間隙鎖或 Next-key 鎖，則會阻塞。

其他型別的鎖的規則較為簡單：

  • 間隙鎖不和其他鎖（不包括插入意向鎖）衝突；
  • 記錄鎖和記錄鎖衝突，Next-key 鎖和 Next-key 鎖衝突，記錄鎖和 Next-key 鎖衝突；

文章寫到這裡吧，再寫下去有點長了。上面文章中很多資訊都來源網路，我只是個搬運工，假若哪裡有表述錯誤，請評論區留言。

參考資料：

相關文章