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鎖。

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

按粒度

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的標記。

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)。

表鎖的兼容性矩陣

以上就是MySQL 鎖的相關知識總結的詳細內容，更多關於MySQL 鎖的資料請關註WalkonNet其它相關文章！

推薦閱讀：

• 詳解MySQL中事務隔離級別的實現原理
• MySQL鎖等待與死鎖問題分析
• MySQL事務的隔離性是如何實現的
• mysql中整數數據類型tinyint詳解
• MySQL InnoDB鎖類型及鎖原理實例解析