你應該瞭解的MySQL鎖分類

X先生發表於2020-09-02
MySql

MySQL中的鎖

鎖是為了解決併發環境下資源競爭的手段，其中樂觀併發控制，悲觀併發控制和多版本併發控制是資料庫併發控制主要採用的技術手段（具體可見我之前的文章），而MySQL中的鎖就是其中的悲觀併發控制。

MySQL中的鎖有很多種類，我們可以按照下面方式來進行分類。

按讀寫

從資料庫的讀寫的角度來分，資料庫的鎖可以分為分為以下幾種：

  • 獨佔鎖：又稱排它鎖、X鎖、寫鎖。X鎖不能和其他鎖相容，只要有事務對資料上加了任何鎖，其他事務就不能對這些資料再放置X了，同時某個事務放置了X鎖之後，其他事務就不能再加其他任何鎖了，只有獲取排他鎖的事務是可以對資料進行讀取和修改。
  • 共享鎖：又稱讀鎖、S鎖。S鎖與S鎖相容，可以同時放置。
  • 更新鎖：又稱U鎖。它允許再加S鎖，但不允許其他事務再施加U鎖或X鎖，當被讀取的資料要被更新時，則升級S鎖為X鎖。U鎖的優點是允許事務A讀取資料的同時不阻塞其它事務，並同時確保事務A自從上次讀取資料後資料沒有被更改，因此可以減少X鎖和S鎖的衝突，同時避免使用S鎖後再升級為X鎖造成的死鎖現象。注意，MySQL並不支援U鎖，SQLServer才支援U鎖。

相容性矩陣如下(+ 代表相容， -代表不相容)

右側是已加的鎖XSU
X---
S-++
U-+-

按粒度

MySQL支援不同級別的鎖，其鎖定的資料的範圍也不同，也即我們常說的鎖的粒度。MySQL有三種鎖級別：行級鎖、頁級鎖、表級鎖。不同的儲存引擎支援不同的鎖粒度，例如MyISAM和MEMORY儲存引擎採用的是表級鎖，頁級鎖僅被BDB儲存引擎支援，InnoDB儲存引擎支援行級鎖和表級鎖，預設情況下是採用行級鎖。

特點

表級鎖：開銷小，加鎖快；不會出現死鎖；鎖定粒度大，發生鎖衝突的概率最高，併發度最低。資料庫引擎總是一次性同時獲取所有需要的鎖以及總是按相同的順序獲取表鎖從而避免死鎖。
行級鎖：開銷大，加鎖慢；會出現死鎖；鎖定粒度最小，發生鎖衝突的概率最低，併發度也最高。行鎖總是逐步獲得的，因此會出現死鎖。
頁面鎖：開銷和加鎖時間界於表鎖和行鎖之間；會出現死鎖；鎖定粒度界於表鎖和行鎖之間，併發度一般。

下面詳細介紹行鎖和表鎖，頁鎖由於使用得較少就不介紹了。

行鎖

按行對資料進行加鎖。InnoDB行鎖是通過給索引上的索引項加鎖來實現的，Innodb一定存在聚簇索引，行鎖最終都會落到聚簇索引上，通過非聚簇索引查詢的時候，先鎖非聚簇索引，然後再鎖聚簇索引。如果一個where語句裡面既有聚簇索引，又有二級索引，則會先鎖聚簇索引，再鎖二級索引。由於是分步加鎖的，因此可能會有死鎖發生。

MySQL的行鎖對S、X鎖上做了一些更精確的細分，使得行鎖的粒度更細小，可以減少衝突，這就是被稱為“precise mode”的相容矩陣。（該矩陣沒有出現在官方文件上，是有人通過Mysql lock0lock.c:lock_rec_has_to_wait原始碼推測出來的。）

行鎖相容矩陣

  • 間隙鎖(Gap Lock)：只鎖間隙，前開後開區間(a,b)，對索引的間隙加鎖，防止其他事務插入資料。
  • 記錄鎖(Record Lock)：只鎖記錄，特定幾行記錄。
  • 臨鍵鎖(Next-Key Lock)：同時鎖住記錄和間隙，前開後閉區間(a,b]。
  • 插入意圖鎖(Insert Intention Lock)：插入時使用的鎖。在程式碼中，插入意圖鎖，實際上是GAP鎖上加了一個LOCK_INSERT_INTENTION的標記。
右側是已加的鎖(+ 代表相容， -代表不相容)GRNI
G++++
R+––+
N+––+
I–+–+

S鎖和S鎖是完全相容的，因此在判別相容性時不需要對比精確模式。精確模式的檢測，用在S、X和X、X之間。從這個矩陣可以看到幾個特點：

  • INSERT操作之間不會有衝突：你插入你的，我插入我的。
  • GAP，Next-Key會阻止Insert：插入的資料正好在區間內，不允許插入。
  • GAP和Record，Next-Key不會衝突
  • Record和Record、Next-Key之間相互衝突。
  • 已有的Insert鎖不阻止任何準備加的鎖。
  • 間隙鎖（無論是S還是X）只會阻塞insert操作。

注意點

  • 對於記錄鎖，列必須是唯一索引列或者主鍵列，查詢語句必須為精確匹配，如“=”，否則記錄鎖會退化為臨鍵鎖。
  • 間隙鎖和臨鍵鎖基於非唯一索引，在唯一索引列上不存在間隙鎖和臨鍵鎖。

表鎖與鎖表的誤區

只有正確通過索引條件檢索資料（沒有索引失效的情況），InnoDB才會使用行級鎖，否則InnoDB對錶中的所有記錄加鎖，也就是將鎖住整個表。注意，這裡說的是鎖住整個表，但是Innodb並不是使用表鎖來鎖住表的，而是使用了下面介紹的Next-Key Lock來鎖住整個表。網上很多的說法都是說用表鎖，然而實際上並不是，我們可以通過下面的例子來看看。

假設我們有以下的資料(MySQL8)：

mysql> select * from users;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | a    | 1   |
|  2 | a    | 1   |
|  3 | a    | 1   |
|  4 | a    | 1   |
|  5 | a    | 1   |
+----+------+-----+

方法一：

我們使用表鎖鎖表，並檢視引擎的狀態

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> lock tables users write;
Query OK, 0 rows affected (0.00 sec)

mysql>  show engine innodb status\G
...
------------
TRANSACTIONS
------------
Trx id counter 4863
Purge done for trx's n:o < 4862 undo n:o < 0 state: running but idle
History list length 911
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 281479760456232, not started
mysql tables in use 1, locked 1   ###############注意這裡
0 lock struct(s), heap size 1136, 0 row lock(s)
...

然後我們再通過非索引的欄位查詢來加鎖，並檢視引擎的狀態

## 先解鎖上次的表鎖
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from users where name = 'a' for update;

mysql>  show engine innodb status\G
...
------------
TRANSACTIONS
------------
Trx id counter 4864
Purge done for trx's n:o < 4862 undo n:o < 0 state: running but idle
History list length 911
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 4863, ACTIVE 37 sec
2 lock struct(s), heap size 1136, 6 row lock(s)    ###############注意這裡
...

然後我們再刪除id為2，3，4的資料，然後在通過非索引的欄位查詢來加鎖，並檢視引擎的狀態

mysql> delete from users where id in (2,3,4);
Query OK, 3 rows affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from users where name = 'a' for update;

mysql>  show engine innodb status\G
...
------------
TRANSACTIONS
------------
Trx id counter 4870
Purge done for trx's n:o < 4869 undo n:o < 0 state: running but idle
History list length 914
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 4869, ACTIVE 9 sec
2 lock struct(s), heap size 1136, 3 row lock(s)   ###############注意這裡
...

可以看到這裡使用了表鎖和因為沒法用索引鎖定特定行而轉而鎖住整個表是不一樣的。從第二次和第三次的操作來看，lock住的row也是不同的，這是因為兩者間隙的個數不同，所以可以看到使用的並不是表鎖，而是Next-Key Lock。第一次鎖住了(-∞,1],(1,2],(2,3],(3,4],(4,5],(5,∞]，第二次鎖住了(-∞,1],(1,5],(5,∞]。

方法二：

也可以通過以下語句來檢視鎖的資訊，也可以知道用的是行鎖，且是鎖住了區間（插入不了資料）和記錄，所以是Next-Key Lock。

mysql> select ENGINE_TRANSACTION_ID,LOCK_TYPE,LOCK_MODE from performance_schema.data_locks where ENGINE_TRANSACTION_ID in (你的事務id);
+-----------------------+-----------+-----------+
| ENGINE_TRANSACTION_ID | LOCK_TYPE | LOCK_MODE |
+-----------------------+-----------+-----------+
|                  4889 | TABLE     | IX        |
|                  4889 | RECORD    | X         |
|                  4889 | RECORD    | X         |
|                  4889 | RECORD    | X         |
+-----------------------+-----------+-----------+
10 rows in set (0.00 sec)

LOCK_TYPE：對於InnoDB，可選值為 RECORD(行鎖)， TABLE(表鎖)

LOCK_MODE：對於InnoDB，可選值為S[,GAP], X[,GAP], IS[,GAP],IX[,GAP], AUTO_INC和UNKNOWN。除了AUTO_INC和UNKNOWN，其他鎖定模式都包含了GAP鎖（如果存在）。

具體可見 MySQL文件：https://dev.mysql.com/doc/ref...

表級鎖

直接對整個表加鎖，影響表中所有記錄，表讀鎖和表寫鎖的相容性見上面的分析。

MySQL中除了表讀鎖和表寫鎖之外，還存在一種特殊的表鎖：意向鎖，這是為了解決不同粒度的鎖的相容性判斷而存在的。

意向鎖

因為鎖的粒度不同，表鎖的範圍覆蓋了行鎖的範圍，所以表鎖和行鎖會產生衝突，例如事務A對錶中某一行資料加了行鎖，然後事務B想加表鎖，正常來說是應該要衝突的。如果只有行鎖的話，要判斷是否衝突就得遍歷每一行資料了，這樣的效率實在不高，因此我們就有了意向表鎖。

意向鎖的主要目的是為了使得 行鎖 和 表鎖 共存，事務在申請行鎖前，必須先申請表的意向鎖，成功後再申請行鎖。注意：申請意向鎖的動作是資料庫完成的，不需要開發者來申請。

意向鎖是表級鎖，但是卻表示事務正在讀或寫某一行記錄，而不是整個表， 所以意向鎖之間不會產生衝突，真正的衝突在加行鎖時檢查。

意向鎖分為意向讀鎖(IS)和意向寫鎖(IX)。

表鎖的相容性矩陣

右側是已加的鎖(+ 代表相容， -代表不相容)ISIXSX
IS+++–
IX++––
S+–+–
X––––

參考資料

https://www.cnblogs.com/rjzhe...
https://dev.mysql.com/doc/ref...

版權宣告

轉載請註明作者和文章出處
作者: X先生
https://segmentfault.com/a/1190000023869573

覺得不錯的話請幫忙收藏點贊~

相關文章